表按如下方式填充
CustomerName SalesValue
CusA 100
CusB 250
CusC 900
CusD 1200
CusE 2500我想要一个查询,它将列出我的所有客户的销售价值,如果价值(每个客户)超过1000。如果小于这个值,我只想看到一个汇总行,该行表示所有客户<= 1000的总价值。结果如下所示:
CusE 2500
CusD 1200
(Small Value Customers) 1250
Total 4950提前谢谢。
发布于 2013-04-29 20:40:44
也许:
WITH sales AS
(
SELECT [CustomerName], [SalesValue],
CustSales = SUM(SalesValue)OVER(PARTITION BY CustomerName)
FROM dbo.Sales
)
SELECT CustomerName, CustSales FROM(
SELECT 1 AS Source,
CustomerName,
CustSales = SUM(CustSales) FROM sales
GROUP BY CustomerName
HAVING SUM(CustSales) > 1000
UNION ALL
SELECT 2 As Source,
CustomerName = '(Small Value Customers)',
CustSales = SUM(CustSales) FROM sales
WHERE CustSales <= 1000
UNION ALL
SELECT 3 As Source,
CustomerName = 'Total',
CustSales = SUM(CustSales) FROM sales
) AS X
ORDER BY X.Source, X.CustSales DESC发布于 2013-04-29 20:32:00
你可以这样做:
DECLARE @LargeCustomersSum INT
SELECT @LargeCustomersSum = SUM(SalesValue)
FROM YourTable
WHERE SalesValue >= 1000
DECLARE @SmallCustomersSum INT
SELECT @SmallCustomersSum = SUM(SalesValue)
FROM YourTable
WHERE SalesValue <= 1000
SELECT 'Large Value Customers', @LargeCustomersSum
UNION
SELECT CustomerName, SalesValue
FROM YourTable
ORDER BY SalesValue DESC
WHERE SalesValue >= 1000
UNION
SELECT 'Small Value Customers', @SmallCustomersSum
UNION
SELECT CustomerName, SalesValue
FROM YourTable
ORDER BY SalesValue DESC
WHERE SalesValue <= 1000
UNION
SELECT 'Total', @LargeCustomersSum + @SmallCustomersSum发布于 2013-04-29 20:39:16
下面是查询
select SalesValue, CustomerName from cus_sales where SalesValue > 1000
UNION
select sum(SalesValue) as Sum, 'Others' as CustomerName from cus_sales where SalesValue <= 1000
UNION
select sum(SalesValue) as Sum, 'Total' as CustomerName from cus_saleshttps://stackoverflow.com/questions/16278617
复制相似问题