group by 字段名 with ROLLUP

 

SELECT 网站名称, 姓名 ,SUM(产品数)as 产品数
from(
SELECT f.Url as 网站名称, e.Name AS 姓名,a.OrderNumber ,SUM(b.Quality)as 产品数
FROM 
tb_order(nolock) as a
left join tb_OrderList as b on b.OrderID=a.ID
left join tb_WebSitePromotion as c on c.WebSiteInfoID=a.WebSiteInfoID and c.SpreadTypeID=2 and c.StartDate<= a.AuditingTime and a.AuditingTime<=ISNULL(c.EndDate,GETDATE())
left join tb_Employee as e on e.ID=c.EmployeeID
left join tb_WebSiteInfo as f on f.ID=a.WebSiteInfoID
WHERE a.Flag in(1,3,5) AND a.Auditing =2and a.MoneyRate is not null and a.MoneyRate>0 
and len(isnull(e.Name,'') )>0
and AuditingTime>='2013-04-01'
GROUP BY e.Name ,a.OrderNumber,f.Url

) as h
group by 姓名,网站名称 WITH ROLLUP
order by 姓名 desc

　　

 

 分组统计姓名相同，加一行合并总数， 最后在来一个总合计

转载于:https://www.cnblogs.com/zhang9418hn/archive/2013/04/18/3029113.html