今天需要这么一条sql语句，需求是B2B_Agent(供应商表)、forderform_tmpUse(订单状态临时表)，找出每个供应商出票状态是(2, 7, 8, 15, 16) 这几种状态的出票总数。forderform_tmpUse中的每一条记录代表一个订单
首先用到的是行转列，将这两个表进行关联之后，对ftu.status 的每个状态求和。然后就把刚刚的这个查询当做一个新表，在按照供应商简称分组排序。
注：就是不明白为什么要除以一个数，没搞懂
SELECT CompanyNameShort,
sum(二) as 二,sum(九) as 九,sum(十五) as 十五,sum(新的) as 新的
FROM
(SELECT ba.CompanyNameShort,
sum(CASE ftu.status WHEN 2 THEN 1 ELSE 0 END ) AS 二,
sum(CASE ftu.status WHEN 7 THEN 1 ELSE 0 END ) AS 七,
sum(CASE ftu.status WHEN 8 THEN 1 ELSE 0 END ) AS 九,
sum(CASE ftu.status WHEN 15 THEN 1 ELSE 0 END ) AS 十五,
sum(CASE ftu.status WHEN 16 THEN 1 ELSE 0 END ) AS 十六,
(sum(CASE ftu.status WHEN 7 THEN 1 ELSE 0 END ) + sum(CASE ftu.status WHEN 16 THEN 1 ELSE 0 END ) )as 新的
FROM B2B_Agent ba WITH(NOLOCK)
LEFT JOIN forderform_tmpUse ftu WITH(NOLOCK) ON ba.Code = ftu.category
WHERE ba.isprovider = 1 AND ba.Isuseful = 1 AND ba.[State] = 1 AND ba.Code<>'' AND ba.Code IS NOT NULL
AND ba.FatherAgentId IS NULL AND ftu.[status] IN (2, 7, 8, 15, 16)
GROUP BY ba.CompanyNameShort,ftu.status ) a
GROUP BY CompanyNameShort ORDER BY a.CompanyNameShort