项目中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、一种思路

引用

表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

能查出上面数据的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  条件