請問資料統計的查詢問題

temple_new 2023-01-20 23:27:40

我想依下表統計出 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

 

...全文
39 9 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
猫狸嘎 4天前
  • 打赏
  • 举报
回复 1

--建立测试表
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

img


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

猫狸嘎 4天前
  • 举报
回复 1
@猫狸嘎 才发现你举例的还有不同cno的各个类别的合计,这个恐怕也要写动态sql整,因为列是动态的,合计列自然也要动态获取,太晚不想整了,你可以利用系统视图查询全局临时表_1的元数据,也就是列名,然后拼接sql查出合计union all上去,或者cube、rollup整都行,后者性能开销比union all小些。
temple_new 3天前
  • 举报
回复
@猫狸嘎 高手!謝謝你!學習了!我先研究研究, 再反饋你!
猫狸嘎 3天前
  • 举报
回复 1
@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”,不少地方改汉字方便你理解,这里忘记改了。
1条回复
猫狸嘎 6天前
  • 打赏
  • 举报
回复 1

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

temple_new 6天前
  • 举报
回复
@猫狸嘎 下表是我希望能得到的結果, 為了統計每個商品類別購買的次數 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
猫狸嘎 6天前
  • 举报
回复 1
@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 5天前
  • 举报
回复
@猫狸嘎 1、例如cno=0260000266565的pcategory有1和3,期望结果里在ltem_1和ltem_3下显示1,而cno=0260000664378的pcategory有5和4,但在期望结果里的ltem_5与ltem_4上显示为5和4,我不明白ltem_n的赋值规则。 ==&gt;item_1至 item_6 是第一個表格中的pcategory, 它是商品類別cno=0260000266565的pcategory有1和3: 表示他買了 item_1 和 item_3 cno=0260000664378的pcategory有5和4:表示他買了 item_5和 item_4 ==&gt;原資料(第一個表格)用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上有显示值。 ==&gt; 對於您對 cno=0260000664590 的疑惑是正確的, 是我在複製表格時因為資料沒有對齊, 所以做了調整, 沒有留意到這一點 3、另外期望结果里的合计值是不是有问题,并且quantity为啥没有合计值,这个字段涵义不是数量吗? 抛开以上的疑惑不谈,如果实际应用环境里cno不仅6个,切ltem_n也不仅会有6个,那么要实现这个我能想到的方式需要动态拼接sql,不是简单的事情。 ==&gt; 關於這一點, 我也有一些游移. 但是一個人同一類別買了兩種, 數量記錄為2, 他買了兩次. ==&gt;若我只是在每個 item_x 增加合計總個數, 但受限於每個item_x 都用 x 表示有購買的記錄, 合計總個數該怎麼處理?
相关推荐
发帖
MS-SQL Server

3.4w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2023-01-20 23:27
社区公告
暂无公告