首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >T-SQL结果列表加上一行不太重要的值的摘要

问T-SQL结果列表加上一行不太重要的值的摘要
EN

Stack Overflow用户
提问于 2013-04-29 20:27:14
回答 4查看 271关注 0票数 1

表按如下方式填充

代码语言:javascript
复制
CustomerName                  SalesValue
CusA                          100
CusB                          250
CusC                          900
CusD                          1200
CusE                          2500

我想要一个查询,它将列出我的所有客户的销售价值,如果价值(每个客户)超过1000。如果小于这个值,我只想看到一个汇总行,该行表示所有客户<= 1000的总价值。结果如下所示:

代码语言:javascript
复制
CusE                          2500
CusD                          1200

(Small Value Customers)       1250

Total                         4950

提前谢谢。

EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2013-04-29 20:40:44

也许:

代码语言:javascript
复制
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
票数 3
EN

Stack Overflow用户

发布于 2013-04-29 20:32:00

你可以这样做:

代码语言:javascript
复制
  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
票数 1
EN

Stack Overflow用户

发布于 2013-04-29 20:39:16

下面是查询

代码语言:javascript
复制
 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_sales

和sqlfiddle

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/16278617

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档