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 A join NM_NetDatas B on B.MediumTypeId=A.MediumTypeId and B.DataId=A.DataId left join NM_Medias C on C.MediumType=B.MediumTypeId and C.MediaCode=B.MediaCode where A.MediumTypeId=5 and B.PublicDate>='2000-01-01' and B.PublicDate<='2011-12-31') T group 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 cust left join NM_Users users on cust.UserId=users.UserId left join NM_Company comp on cust.CustId=comp.CompanyID where 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;