1、sqlserver 中建立临时表存储数据,然后查询
select 种类,编号,时间,内容,创建时间,
into TableBak
from dbo.SLottery
where 种类='3'
order by ctime
select *
from TableBak
drop table TableBak ; //这是一条SQL语句。
2、将行变成列。(去看看以前在银行做报表时候海波龙的列子)
|
|
3、插入的方法
INSERT INTO COUNTER (NAME,CURRENTID) SELECT 'com.endplay.feeds.model.Schedule', NVL(MAX(SCHEDULE_ID),0) FROM SCHEDULE;
4、难点 :需要update多个数据,而这些符合条件的数据需要like出来。
方法 :先查出需要update的记录,拼装成为带%的字段,然后做为一个表。
update QRTZ_SIMPLE_TRIGGERS QST set REPEAT_COUNT = 0 where EXISTS (
select * from (select 'TRIGGER_' || groupId || '%' as triggers_name from group_ where liveGroupid = 0) GP where QST.TRIGGER_NAME like GP.triggers_name
);
5、删除重复
ORA-02437错误:创建表时没有添加主键,当表中已经存在很多重复数据时,再添加主键就会报这个错误。所以,设计表的时候一定要注意!
下面是这个错误的解决办法,先找出表中重复的数据,然后保留相同数据rowid最小的那一列
- select * from material_table
- where unid in (select unid from material_table
- group by unid having count(unid) > 1)
这里的unid 是主键列,即not null 那些你认为是主键的列,通过主键进行分组查找出记录大于1条的数据。
找到重复数据后进行删除
rowid 是 oracle自己的,是每一列的主键
- delete from material_table where unid in (select unid from material_table
- group by unid having count(unid) > 1) and rowid not in (select min(rowid)
- from material_table group by unid having count(unid)>1)
6、复制表结构
1. 复制表结构及其数据: create table table_name_new as select * from table_name_old
2. 只复制表结构: create table table_name_new as select * from table_name_old where 1=2;
或者: create table table_name_new like table_name_old
3. 只复制表数据:如果两个表结构一样:insert into table_name_new select * from table_name_old
如果两个表结构不一样:insert into table_name_new(column1,column2...) select column1,column2... from table_name_old
7、从一个表复制数据插入另外一个表
目的:从portal_user_XXX复制数据到portal_XXX中
难点:portal_XXX的主键ID 要递减
INSERT INTO `portal_XXX`
(`id`, `account_id`, `account_name`, `solution_id`, `capital_account_id`, `capital_account_name`, `extra_info`, `is_deleted`, `gmt_created`, `gmt_modified`)
select @i:=@i-1 as id, t.* from
(select account_id,login_name as account_name,'0',account_id as capital_account_id,login_name as capital_account_name,null,'N',now() as gmt_created,now() as gmt_modified from portal_user_XXX where user_type = '1' and is_deleted='N') t ,
(SELECT @i :=(select min(a.id) from portal_XXX a)) r;