Sql Server 查询语句

经典的sql语句保存

 

select case 4-2 when 1 then 'one' when 2 then 'two' else 'more' end;select T.MediaCode,T.MediaName,sum(1) as total,sum(case T.state when 1 then 0 else 1 end) as '未处理', --如果T.state ==1 就统计为0 个数否则统计为1的个数sum(case T.state when 1 then 1 else 0 end) as '已提交'from (select A.State,C.MediaCode,C.MediaName from NM_CustCateDate Ajoin NM_NetDatas B on B.MediumTypeId=A.MediumTypeId and B.DataId=A.DataIdleft join NM_Medias C on C.MediumType=B.MediumTypeId and C.MediaCode=B.MediaCodewhere A.MediumTypeId=5 and B.PublicDate>='2000-01-01' and B.PublicDate<='2011-12-31') Tgroup by T.MediaCode,T.MediaName-- cust.state=0 未处理-- cust.state=1 已提交

 

select comp.Name_c,users.UserName,sum(1) as total,sum(case cust.state when 1 then 0 else 1 end) as '未处理', --如果T.state ==1 就统计为0 个数否则统计为1的个数sum(case cust.state when 1 then 1 else 0 end) as '已提交'from NM_CustCateDate custleft join NM_Users users on cust.UserId=users.UserIdleft join NM_Company comp on cust.CustId=comp.CompanyIDwhere comp.Date between '2010-12-10' and '2016-12-31'group by comp.name_c,users.UserName WITH ROLLUP-- cust.state=0 未处理-- cust.state=1 已提交

select case 4-2 when 1  then 'one'  when 2 then 'two' else 'more' end;