請問資料統計的查詢問題

[temple_new]

我想依下表統計出 pcategory 有哪些 cno 買？各個 pcategory 購買的 quantity 有多少？

 

cno	quantity	pcategory
0260000266565	2	1
0260000266565	1	3
0260000660967	1	1
0260000663036	1	3
0260000664378	10	5
0260000664378	1	4
0260000664590	1	1
0260000664590	2	4
0260000664767	1	1
0260000664767	4	6

 

[temple_new]

@猫狸嘎 1、例如cno=0260000266565的pcategory有1和3，期望结果里在ltem_1和ltem_3下显示1，而cno=0260000664378的pcategory有5和4，但在期望结果里的ltem_5与ltem_4上显示为5和4，我不明白ltem_n的赋值规则。 ==>item_1至 item_6 是第一個表格中的pcategory, 它是商品類別cno=0260000266565的pcategory有1和3: 表示他買了 item_1 和 item_3 cno=0260000664378的pcategory有5和4:表示他買了 item_5和 item_4 ==>原資料（第一個表格）用PIVOT 得到第二個表格, 但或許用 T 表示有買, F 表示沒買, 會方便很多, 但因為我是sql 新手, 請您指導. 或者你有更好的處理方式, 也請您指導. 2、再例如cno=0260000664590的pcategory有1与4，期望结果里却仅在ltem_1与ltem_3上有显示值。这里疑惑的是按照其它cno的其它列分析，pcategory为几，则会在对应的ltem_n上显示，这里应该在ltem_1与ltem_4上显示值，结果却是ltem_1与ltem_3上有显示值。 ==> 對於您對 cno=0260000664590 的疑惑是正確的, 是我在複製表格時因為資料沒有對齊, 所以做了調整, 沒有留意到這一點 3、另外期望结果里的合计值是不是有问题，并且quantity为啥没有合计值，这个字段涵义不是数量吗？ 抛开以上的疑惑不谈，如果实际应用环境里cno不仅6个，切ltem_n也不仅会有6个，那么要实现这个我能想到的方式需要动态拼接sql，不是简单的事情。 ==> 關於這一點, 我也有一些游移. 但是一個人同一類別買了兩種, 數量記錄為2, 他買了兩次. ==>若我只是在每個 item_x 增加合計總個數, 但受限於每個item_x 都用 x 表示有購買的記錄, 合計總個數該怎麼處理？

[猫狸嘎]

@temple_new 没看懂规则。 1、例如cno=0260000266565的pcategory有1和3，期望结果里在ltem_1和ltem_3下显示1，而cno=0260000664378的pcategory有5和4，但在期望结果里的ltem_5与ltem_4上显示为5和4，我不明白ltem_n的赋值规则。 2、再例如cno=0260000664590的pcategory有1与4，期望结果里却仅在ltem_1与ltem_3上有显示值。这里疑惑的是按照其它cno的其它列分析，pcategory为几，则会在对应的ltem_n上显示，这里应该在ltem_1与ltem_4上显示值，结果却是ltem_1与ltem_3上有显示值。 3、另外期望结果里的合计值是不是有问题，并且quantity为啥没有合计值，这个字段涵义不是数量吗？ 抛开以上的疑惑不谈，如果实际应用环境里cno不仅6个，切ltem_n也不仅会有6个，那么要实现这个我能想到的方式需要动态拼接sql，不是简单的事情。

[temple_new]

@猫狸嘎 下表是我希望能得到的結果, 為了統計每個商品類別購買的次數 cno quantity Item_1 Item_2 Item_3 Item_4 Item_5 Item_6 0260000266565 2 1 NULL NULL NULL NULL NULL 0260000266565 1 NULL NULL 1 NULL NULL NULL 0260000660967 1 1 NULL NULL NULL NULL NULL 0260000663036 1 NULL NULL 3 NULL NULL NULL 0260000664378 10 NULL NULL NULL NULL 5 NULL 0260000664378 1 NULL NULL NULL 4 NULL NULL 0260000664590 1 1 NULL NULL NULL NULL NULL 0260000664590 2 NULL NULL 4 NULL NULL NULL 0260000664767 1 1 NULL NULL NULL NULL NULL 0260000664767 4 NULL NULL NULL NULL NULL 6 合計(數量) 5 0 2 3 10 4

[猫狸嘎]

希望能以表格形式给出期望的查询结果，否则你描述的指向太多可能，不知道你要哪种

[猫狸嘎]

@temple_new 客气了。有个地方有问题，“if object_id('tempdb..##全局临时表_1') is not null drop table ##T_1”要改成“if object_id('tempdb..##全局临时表_1') is not null drop table ##全局临时表_1”，不少地方改汉字方便你理解，这里忘记改了。

[temple_new]

@猫狸嘎 高手！謝謝你！學習了！我先研究研究, 再反饋你！

[猫狸嘎]

@猫狸嘎 才发现你举例的还有不同cno的各个类别的合计，这个恐怕也要写动态sql整，因为列是动态的，合计列自然也要动态获取，太晚不想整了，你可以利用系统视图查询全局临时表_1的元数据，也就是列名，然后拼接sql查出合计union all上去，或者cube、rollup整都行，后者性能开销比union all小些。

[猫狸嘎]

--建立测试表
select * into 测试表 from (
select cno = 0260000266565,quantity=2,pcategory=1
union all
select cno = 0260000266565,quantity=1,pcategory=3
union all
select cno = 0260000660967,quantity=1,pcategory=1
union all
select cno = 0260000663036,quantity=1,pcategory=3
union all
select cno = 0260000664378,quantity=10,pcategory=5
union all
select cno = 0260000664378,quantity=1,pcategory=4
union all
select cno = 0260000664590,quantity=1,pcategory=1
union all
select cno = 0260000664590,quantity=2,pcategory=4
union all
select cno = 0260000664767,quantity=1,pcategory=1
union all
select cno = 0260000664767,quantity=4,pcategory=6
)T

--开始实现统计
--利用动态sql创建临时表
if object_id('tempdb..##全局临时表_1') is not null drop table ##T_1
declare @row int = (select max(pcategory) from 测试表)    --需要循环创建的列数,用pcategory最大值作为最大列数
declare @拼接sql_1 nvarchar(max)
--创建表
set @拼接sql_1 = 'create table ##全局临时表_1 (cno bigint,Total int'
declare @i nvarchar(50) = 1
WHILE @i<=@row  
BEGIN  
    set @拼接sql_1 = @拼接sql_1+',Item_'+@i+' int'
    set @i = @i+1
END 
exec (@拼接sql_1+')')    --创建临时表


--向临时表中写入统计数据
declare 游标_1 cursor Scroll                --声明游标循环  
for  
    select cno,quantity=sum(quantity),T1.Total,pcategory from 测试表 as a1
    cross apply(select Total = sum(quantity) from 测试表 as b1 where b1.cno = a1.cno)T1
    group by cno,pcategory,T1.Total            --循环处理的数据
open 游标_1                                    --打开游标
declare @cno nvarchar(50),@quantity nvarchar(50),@Total nvarchar(50),@pcategory nvarchar(50)
fetch first from 游标_1 into @cno,@quantity,@Total,@pcategory    --第一次循环，同时声明所有需要处理的字段变量
while @@fetch_status=0                                    --游标读取下一条是否成功
begin                                                    --成功则进入循环
    --使用merge更新或写入数据
    declare @拼接sql_2 nvarchar(max) = 'merge into ##全局临时表_1 as T1
    using (select cno = '''+@cno+''',quantity = '''+@quantity+''',Total = '''+@Total+''',pcategory = '''+@pcategory+''') as T2 on T1.cno = T2.cno
    when matched then update set T1.Total = T2.Total,T1.Item_'+@pcategory+' = T2.quantity            --满足T1.cno=T2.cno时,更新T1
    when not matched then insert (cno,Total,Item_'+@pcategory+') values(T2.cno,T2.Total,T2.quantity)            --不满足T1.cno=T2.ID时,则插入T2行
    ;'
    exec (@拼接sql_2)
fetch Next from 游标_1 into @cno,@quantity,@Total,@pcategory    --下一次循环，同时声明所有需要处理的字段变量
end  
close 游标_1                                --关闭循环
deallocate 游标_1                            --关闭游标


--查询统计数据
select * from ##全局临时表_1

--查完删除全局临时表
drop table ##全局临时表_1

ltem_n的值代表各个类别的购买数量，Total的值代表各个类别购买数量的总合。
我觉得这样是你期望的结果吧？