常用SQL语句

[b]1、常用的Update语句：[/b]
update a set tier_id = l.tier_id
from temp_id a,lp_account l
where a.aid=l.aid
或者
update a set tier_id = l.tier_id
from a inner join l on a.id = l.tid
[b]2、常用的Delete语句：[/b]delete from t1 from t1 inner join t2 on t1.id = t2.tid
[b]3、常用的case,count查询语句：[/b]
select fr.departure_airport,fr.arrive_airport,
count(case when fr.cumulate_cabin in ('F','A') then 1 else NULL end) as FACount,
count(case when fr.cumulate_cabin in ('C','D') then 1 else NULL end) CDCount,
count(case when fr.cumulate_cabin='W' then 1 else NULL end) WCount
from fr_ffp_travel_detail fr
where fr.flight_date>='2010-01-01'
and fr.flight_date<='2010-12-01'
and ((fr.departure_airport='CAN' and fr.arrive_airport='SYD')
or (fr.departure_airport='CAN' and fr.arrive_airport='MEL')
or (fr.departure_airport='CAN' and fr.arrive_airport='BNE')
or (fr.departure_airport='CAN' and fr.arrive_airport='PEK')
or (fr.departure_airport='PEK' and fr.arrive_airport='AMS')
or (fr.departure_airport='CAN' and fr.arrive_airport='CDG')
or (fr.departure_airport='CAN' and fr.arrive_airport='DXB')
or (fr.departure_airport='CAN' and fr.arrive_airport='LAX')
or (fr.departure_airport='DXB' and fr.arrive_airport='JED'))
and fr.cumulate_cabin in
('F','A','C','D','W')
and fr.status='IN'
and fr.airline_company='CZ'
group by fr.departure_airport,fr.arrive_airport
[b]4、经典的左链接查询：[/b]
查出a中b不存在bid的记录：
select * from a left join b on a.aid = b.bid
where b.name is null
在连接的时候加上条件：
select branch_code, isnull(r.result,'NO'),count(c.sid) as allCount
from cb_service_order c
inner join fr_soc_flight f on c.departure_airport=f.departure_airport
and c.flight_date=f.flight_date
and c.flight_no=f.flight_no
inner join cb_service_response r on c.sid=r.sid
and r.result_type='001'
where c.sid is not null
and c.flight_date>=:beginDate
and c.flight_date<=:endDate
and c.departure_airport in ('SHE','PEK','KWL','NNG','DLC','CAN','KWE','HAK','SYX','CGO','WUH','HRB','CSX','CGQ','SWA','PVG','SHA','URC','SZX','ZUH','CKG')
and c.type=:type
and c.cabin in ('F','A','P','J','C','D','I','W','Z','Y','T','K','H','M','B','E','G','L','N','O','Q','R','S','U','V','X')
group by branch_code, isnull(r.result,'NO')
order by branch_code
[b]5、去除重复记录的SQL[/b]
select * from gczbxx_zhao where viewid in ( select max(viewid) from gczbxx_zhao group by
gcmc ) order by gkrq desc ---还是这个可行。
或者：
SELECT *
FROM TB A INNER JOIN
(
SELECT MIN(ID),NAME FROM TB WHERE TYPE = 1 AND CHECK = 0 GROUP BY NAME
) B ON A.ID = B.ID AND A.NAME = B.NAME