27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT '['+Convert(varchar(11),n.g*10)+'-'+Convert(varchar(11),n.g*10+10)+']' AS 价位,
s.总售出数量
FROM (
SELECT number AS g
FROM master..spt_values
WHERE type = 'p'
AND number < 10
) n
LEFT JOIN (
SELECT 商品价格/10 AS g,
SUM(售出数量) AS 总售出数量
FROM table1
GROUP BY 商品价格/10
) s
ON s.g = n.g
WITH table1(商品价格,售出数量) AS (
SELECT 8,2 UNION ALL
SELECT 2,6 UNION ALL
SELECT 89,1 UNION ALL
SELECT 25,4 UNION ALL
SELECT 8,2
)
,s AS (
SELECT 商品价格/10 AS g,
SUM(售出数量) AS 总售出数量
FROM table1
GROUP BY 商品价格/10
)
,n AS (
SELECT number AS g
FROM master..spt_values
WHERE type = 'p'
AND number < 10
)
SELECT '['+Convert(varchar(11),n.g*10)+'-'+Convert(varchar(11),n.g*10+10)+']' AS 价位,
s.总售出数量
FROM n
LEFT JOIN s
ON s.g = n.g
价位 总售出数量
------------------------- -----------
[0-10] 10
[10-20] NULL
[20-30] 4
[30-40] NULL
[40-50] NULL
[50-60] NULL
[60-70] NULL
[70-80] NULL
[80-90] 1
[90-100] NULL
SELECT 商品编码,商品名
,SUM(CASE WHEN 商品价格<=10 THEN 售出数量 ELSE 0 END)[0-10]
,SUM(CASE WHEN 商品价格<=20 AND 商品价格>10 THEN 售出数量 ELSE 0 END)[10-20]
,SUM(CASE WHEN 商品价格<=30 AND 商品价格>20 THEN 售出数量 ELSE 0 END)[20-30]
,SUM(CASE WHEN 商品价格<=40 AND 商品价格>30 THEN 售出数量 ELSE 0 END)[30-40]
,SUM(CASE WHEN 商品价格<=50 AND 商品价格>40 THEN 售出数量 ELSE 0 END)[40-50]
,SUM(CASE WHEN 商品价格<=60 AND 商品价格>50 THEN 售出数量 ELSE 0 END)[50-60]
,SUM(CASE WHEN 商品价格<=70 AND 商品价格>60 THEN 售出数量 ELSE 0 END)[60-70]
,SUM(CASE WHEN 商品价格<=80 AND 商品价格>70 THEN 售出数量 ELSE 0 END)[70-80]
,SUM(CASE WHEN 商品价格<=90 AND 商品价格>80 THEN 售出数量 ELSE 0 END)[80-90]
,SUM(CASE WHEN 商品价格<=100 AND 商品价格>90 THEN 售出数量 ELSE 0 END)[90-100]
FROM
TB
GROUP BY 商品编码,商品名
DECLARE @StartAmt INT,@EndAmt INT
SELECT @StartAmt=0,@EndAmt=10
SELECT CAST(@StartAmt AS VARCHAR)+'-'+CAST(@EndAmt AS VARCHAR) AS [价位]
,SUM(售出数量) [售出数量]
FROM t
WHERE t.商品价格 BETWEEN @StartAmt AND @EndAmt
select
商品名,
sum(case when 商品价格 between 0 and 10 then 售出数量 else 0 end) ,
..........
from
tb
group by
商品名