22,209
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([Customer] nvarchar(21),[SALETYPE] nvarchar(22),[TYPECOUNT] int)
Insert #1
select N'A',N'水果',20 union all
select N'A',N'水果',10 union all
select N'A',N'肉肉',50 union all
select N'B',N'肉肉',100
Go
SELECT
[Customer],[SALETYPE],STR([TYPECOUNT]*100.00/t2.[TYPECOUNT2],6,2)+'%' AS [TYPECOUNT]
FROM
(
SELECT
[Customer],[SALETYPE],SUM([TYPECOUNT]) AS [TYPECOUNT]
FROM #1
GROUP BY [Customer],[SALETYPE]
) AS t
CROSS APPLY(SELECT SUM([TYPECOUNT]) FROM #1 WHERE [Customer]=t.[Customer]) AS t2([TYPECOUNT2])
WHERE [TYPECOUNT]/t2.[TYPECOUNT2]>0.7
ORDER BY 2 desc,1
/*
Customer SALETYPE TYPECOUNT
B 肉肉 100.00%*/
SELECT *
FROM (
-- 原#1语句不包括 ORDER BY
) AS t2
WHERE TYPECOUNT > ' 70.00%'
ORDER BY 2 DESC, 1
SELECT [Customer] ,
[SALETYPE] ,
STR([TYPECOUNT], 6, 2) + '%' AS [TYPECOUNT]
FROM ( SELECT [Customer] ,
[SALETYPE] ,
[TYPECOUNT] * 100.00
/ SUM([TYPECOUNT]) OVER ( PARTITION BY [Customer] ) AS [TYPECOUNT]
FROM ( SELECT [Customer] ,
[SALETYPE] ,
SUM([TYPECOUNT]) AS [TYPECOUNT]
FROM #1
GROUP BY [Customer] ,
[SALETYPE]
) AS t
) AS tt
WHERE [TYPECOUNT] > 70
ORDER BY 2 DESC ,
1
--测试数据
;with taba (Customer,SALETYPE,TYPECOUNT)as
(
select 'A','水果',20.00 union all
select 'A','水果',10.00 union ALL
select 'A','肉肉',50.00 union ALL
select 'B','水果',100.00
)
--测试数据结束
SELECT a.Customer ,
a.SALETYPE ,
ROUND(( CAST(( SUM(a.TYPECOUNT) / ( t.count + 0.0 ) * 100 ) AS DECIMAL(9,
2)) ), 2) AS [count]
FROM taba a
INNER JOIN ( SELECT b.Customer ,
SUM(b.TYPECOUNT) AS [count]
FROM taba b
GROUP BY b.Customer
) t ON a.Customer = t.Customer
GROUP BY a.Customer ,
a.SALETYPE ,
[count]
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([Customer] nvarchar(21),[SALETYPE] nvarchar(22),[TYPECOUNT] int)
Insert #1
select N'A',N'水果',20 union all
select N'A',N'水果',10 union all
select N'A',N'肉肉',50 union all
select N'B',N'肉肉',100
Go
SELECT
[Customer],[SALETYPE],STR([TYPECOUNT]*100.00/SUM([TYPECOUNT])OVER(PARTITION BY [Customer]),6,2)+'%' AS [TYPECOUNT]
FROM
(
SELECT
[Customer],[SALETYPE],SUM([TYPECOUNT]) AS [TYPECOUNT]
FROM #1
GROUP BY [Customer],[SALETYPE]
) AS t
ORDER BY 2 desc,1
/*
Customer SALETYPE TYPECOUNT
A 水果 37.50%
A 肉肉 62.50%
B 肉肉 100.00%
*/