发现好记性不如烂笔头这句话是真理已经很久了，呵呵，得真理用于实践了。
1、常用的Update语句：
update temp_id set temp_id.tier_id =lp_account.tier_id from temp_id, lp_account where temp_id.aid=lp_account.aid 或者 update a set tier_id = l.tier_id from a inner join l on a.id = l.tid
2、常用的Delete语句：
delete from t1 from t1 inner join t2 on t1.id = t2.tid
3、常用的case,count查询语句：
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 4、经典的左链接查询： 查出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 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