Sql Server 查询语句

转载

mob604757069565 2011-04-02 11:29:00

经典的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 已提交