项目中SQL TIP 积累

scaoping

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  条件