SQL Server输出:我应该如何继续在表中获取此输出？
SQL Server输出:我应该如何继续在表中获取此输出？
提问于 2016-02-10 13:00:41
这是我要针对其编写查询的示例数据
Shopkeeper | Date | Product
----------------------------------------
SA10000601 | 2011-12-01 | 8183
SA10000601 | 2011-02-01 | 190
SA10000601 | 2011-04-01 | 3192
SA10000601 | 2011-03-01 | 4195
SA10000601 | 2011-04-01 | 9195
SA10000605 | 2011-12-01 | 8183
SA10000601 | 2011-02-01 | 190
JA10000607 | 2011-04-01 | 3192
SA10000604 | 2011-03-01 | 195
JA10000603 | 2011-04-01 | 9195
SA10000601 | 2011-12-01 | 8183
SA10000601 | 2011-02-01 | 190
SA10000601 | 2011-04-01 | 3192
SA10000601 | 2011-09-01 | 4195
SA10000601 | 2011-08-01 | 9195
SA10000904 | 2011-08-01 | 9195
SA10000601 | 2011-05-01 | 8180
SA10000606 | 2011-03-01 | 190
SA10000601 | 2011-08-01 | 3192
SA10000609 | 2011-11-01 | 4195我想展示所有的产品，这些产品只卖了1个月，一年卖了2-3个月，一年卖了4-5个月，一年卖了6-7个月。
在同一张表中，我想显示只销售了一个月的店主，销售了2-3个月的店主，销售了4-5个月的店主。
应该看起来像这样(示例输出)：
Months | Shopkeepers | Product sold
-----------------------------------------------
1 month | 5 | 10
2-3 months | 3 | 2
4-5 months | 8 | 4SQL Server中的查询应该如何进行？请寻求更多的解释，因为我今天必须解决这个问题。提前感谢:)
附言:对于那些表格，很抱歉，在这里如何在表格中放置数据？
回答 3
Stack Overflow用户
发布于 2016-02-10 13:15:11
SELECT t2.MonthCount AS Months, COUNT(t2.Shopkeeper) AS Shopkeepers,
SUM(t1.ProductCount) AS `Product Sold`
FROM
(
SELECT Shopkeeper, COUNT(DISTINCT Product) AS ProductCount
FROM products
GROUP BY Shopkeeper
) t1
INNER JOIN
(
SELECT Shopkeeper, COUNT(DISTINCT MONTH(Date)) AS MonthCount
FROM products
GROUP BY Shopkeeper
) t2
ON t1.Shopkeeper = t2.Shopkeeper
GROUP BY t2.MonthCount以下是上述示例数据的输出：
╔════════════╦═════════════╦══════════════╗
║ MonthCount ║ Shopkeepers ║ Product Sold ║
╠════════════╬═════════════╬══════════════╣
║ 1 ║ 7 ║ 7 ║
║ 7 ║ 1 ║ 6 ║
╚════════════╩═════════════╩══════════════╝请注意，在上面的示例数据中，只有两种类型的店主:仅在一个月内售出的店主(其中7个)，以及在7个月内售出的店主。我选择省略按月分组，但您自己添加此功能并不太难。
点击下面的链接查看一个正在运行的演示：
Stack Overflow用户
发布于 2016-02-10 13:52:30
declare @table table
(shop_keeper nvarchar(50),[date] date,product_sold int)
insert into @table
select 'SA10000601', '2011-12-01', 8183
union all select 'SA10000601', '2011-01-01', 10
union all select 'SA10000601', '2011-02-01', 190
union all select 'SA10000601', '2011-04-01', 3192
union all select 'SA10000601', '2011-03-01', 4195
union all select 'SA10000601', '2011-04-01', 9195
union all select 'SA10000605', '2011-12-01', 8183
union all select 'SA10000601', '2011-02-01', 190
union all select 'JA10000607', '2011-04-01', 3192
union all select 'SA10000604', '2011-03-01', 195
union all select 'JA10000603', '2011-04-01', 9195
union all select 'SA10000601', '2011-12-01', 8183
union all select 'SA10000601', '2011-02-01', 190
union all select 'SA10000601', '2011-04-01', 3192
union all select 'SA10000601', '2011-09-01', 4195
union all select 'SA10000601', '2011-08-01', 9195
union all select 'SA10000904', '2011-08-01', 9195
union all select 'SA10000601', '2011-05-01', 8180
union all select 'SA10000606', '2011-03-01', 190
union all select 'SA10000601', '2011-08-01', 3192
union all select 'SA10000609', '2011-11-01', 4195
WITH cte
AS (
SELECT month([date]) AS [month]
,sum([product_sold]) AS [product_sold]
,count(shop_keeper) AS shop_keeper
,CASE
WHEN month([date]) % 2 = 0
THEN month([date])
ELSE month([date]) - 1
END AS RN
FROM @table
GROUP BY month([date])
)
SELECT CASE
WHEN RN <> 0
AND RN <> 12
THEN cast(RN AS VARCHAR) + ' - ' + cast(RN + 1 AS VARCHAR) + ' Months'
WHEN Rn = 0
THEN cast(RN + 1 AS VARCHAR) + ' Month'
ELSE cast(RN AS VARCHAR) + ' Months'
END AS [months]
,sum(product_sold) product_sold
,sum(shop_keeper) shop_keeper
FROM cte
GROUP BY RN输出
+----------------+--------------+-------------+
| months | product_sold | shop_keeper |
+----------------+--------------+-------------+
| 1 Month | 10 | 1 |
| 2 - 3 Months | 5150 | 6 |
| 4 - 5 Months | 36146 | 6 |
| 8 - 9 Months | 25777 | 4 |
| 10 - 11 Months | 4195 | 1 |
| 12 Months | 24549 | 3 |
+----------------+--------------+-------------+Stack Overflow用户
发布于 2016-02-10 14:31:05
尝试使用此查询：
SELECT month(date) AS month, count(shopkeepers) AS shopkeepers, sum(productsold) AS productsold
FROM products
GROUP BY month(date)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35307158
复制相关文章
相似问题
腾讯云开发者
