项目中SQL TIP 积累

1、一张表t_payfee,有一个字段invoicenum,字段里内容是：
id invoicenum
1001 12.00
1002 89.00
1003 0.00
要求：把invoicenum 的内容后取整数，即去掉小数点和小数点后面的00;

 update t_payfee
   set invoicenum=(select substring(invoicenum,0,charindex('.',invoicenum)) );

2、oracle 查询时间最近的记录

select * from t_payfee where payfeedate=(select max(payfeedate) from t_payfee where cardnum='00000036')

3、将表a中的某个字段值设置成表b的某字段值

update t_consumer
	set basenumber=jixiebiaodi
	from t_recordinput
	where t_consumer.usercode=t_recordinput.usercode and t_consumer.chaobiaodate=t_recordinput.chaobiaodate;

4、将某列左或者右 边补零
-->不够8位左边补零

update 表A
set 列1=right('00000000'+right(列1,len(列1)),8)
where ......;

-->右边补零

update 表A
set 列1=left(列1+'00000000',8)
where ......;

5、交费记录表 payfee 有两个字段 A（姓名）、B（金额）.数据结构如下：
A B
张三 10
张三 20
李四 30
李四 40
王五 50
王五 20
现要求：以A作为分组条件，取出B字段值最大的记录，得到结果集如下：
A B
张三 20
李四 40
王五 50

select A,MAX(B) as b from payfee group by A

6、一种思路
[quote]表A用户表 {sid,sname}
sid sname
1 张三
2 李四
3 王五
表B交易表 {gid,sid,交易金额，交易量，交易时间}
gid sid 交易金额 交易量 交易时间
1 1 10 4 20080808
2 1 34 6 20090409
3 2 45 8 20200908
4 1 5 22 20120907
5 2 55 2 20110101
6 3 44 8 20030908

求一条：查出所有用户 某段时间内的 交易金额总计 与交易量总计？
如：20080101 到20121221 之间的数据
sid sname 总金额 总量
1 张三 49 32
2 李四 55 2
3 王五 null null[/quote]

能查出上面数据的sql..

select a.sid,a.sname,w.交易金额,w.交易量 from  A a left join  (select sid, SUM(交易金额) 交易金额, SUM(交易量)from  B where 交易时间 between atime and btime group by sid ) w
 on a.sid=w.sid

思路：先把B表根据外键（也就是A表的主键）进行分组，作为一张新表(w)与A 表进行join 左连接 ，这样时间条件就可以在分组里限制了
7、取一个表中的值更新另一个表；

update consumer 
set lastgas=t.lastgas,gasamount=t.totalgas
from consumer c,t_temp t 
where c.id=t.id ;

update consumer
set consumer.consumertype=temp.consumertype
from 库名称.dbo.t_consumer consumer join 另外的库名称.dbo.t_temp temp 
on consumer.id=temp.id;

8、sql server 2008 删除表中完全重复的多条数据，保留一条。用临时表的办法解决！

select distinct * into #temp from table where condition   //将查询符合条件的记录并合并相同记录,添加到临时表中
delete from table where condition    //从表中删除符合条件的记录,有重复的全部删除.                    
insert into table select * from #temp          //将临时表中的记录重新添加到表中

9、sql server 2008 中让其显示与oracle 中的rownum 一样的效果

select t.*, row_number() over(order by f_districtname) as a
  from (select f_districtname from t_userfiles group by f_districtname) t

10、查询出交易表中，所有最近一次的交易记录

select s.id,s.code,s.name,s.number,s.biaodi,
	s.type,s.unitprice ,s.date
from S s 
where s.date= (select MAX(date) from S w  
		where s.code=w.code
		group by code) 
		and s.date between  20130503 and 20130503

11、条件修改库

update tb_xxx 
set f_x=case 
            when 判断表达式   then  成立时的值  else 不成立时的值 
        end
where  条件