Android环境下修改SQL查询：按两列分组统计Qty并保留其他列

原查询 #

select * from Assignment_Data_RAD where Rfid IN ( '34E59808D8400410C2048F48','3460881C004610224927D013','3C60881C00461020420FD000','3C60881C00461020420C107D','3DD00000000000103D004188','3460881C00461020420FD000' )

需求说明 #

获取指定RFID列表的数据，同时得到按Category和GS_1_BARCODE分组的Qty统计结果，以下分两种场景给出修改方案：

场景1：保留所有原列，同时显示对应分组的Qty

使用窗口函数COUNT() OVER()实现，它会为每行添加对应分组的统计数量，不影响原列的展示：

select 
    *,
    COUNT(*) OVER (PARTITION BY Category, GS_1_BARCODE) AS Qty
from Assignment_Data_RAD 
where Rfid IN ( '34E59808D8400410C2048F48','3460881C004610224927D013','3C60881C00461020420FD000','3C60881C00461020420C107D','3DD00000000000103D004188','3460881C00461020420FD000' )

场景2：仅获取分组统计的汇总结果（与示例格式一致）

使用GROUP BY分组聚合，只返回分组列和统计数量：

select 
    Category,
    GS_1_BARCODE AS Gs1,
    COUNT(*) AS Qty
from Assignment_Data_RAD 
where Rfid IN ( '34E59808D8400410C2048F48','3460881C004610224927D013','3C60881C00461020420FD000','3C60881C00461020420C107D','3DD00000000000103D004188','3460881C00461020420FD000' )
group by Category, GS_1_BARCODE
order by Category, Gs1

内容的提问来源于stack exchange，提问作者Pemba Tamang