SQL Group By和COALESCE在sqlite上的问题

vinaym 发表于 Dev

维纳伊姆

我在sqlite数据库中有一个如下表。我想创建一个折线图，按产品组显示使用情况。

Table: ProductUsageData

UserID  ProductName ProductGroup    Qty RecordID
1       A1          A               12  1   
2       A1          A               12  1   
1       A2          A               15  1   
3       A1          A               12  2   
2       B1          B               12  2   
5       B2          B               5   2
1       A1          A               12  3   
1       A2          A               15  3   
4       A1          A               12  3   
3       C1          C               12  3   
2       C2          C               15  3

由于我要为每个产品组使用单独的行，因此我在查询下面使用

SELECT 
      SUM(Qty) as UsedQty, 
      ProductGroup, 
      RecordID 
FROM ProductUsageData 
GROUP BY ProductGroup, RecordID  
ORDER BY RecordID ASC;

虽然我获得了三个记录A（每个RecordID），但对于B和C，每个记录只有1条记录，因为在每个RecordID期间都没有使用它们。问题是当我在图表中为每个ProductGroup放置一条线时，B和C的点按数量显示在第一个中。我的输出是这样的

所以图看起来像这样

代替

为了解决这个问题，如果在每次记录期间都未使用ProductGroup，我使用COALESCE更改了查询以获取0 Qty。

SELECT 
       COALESCE(SUM(Qty), 0) as UsedQty, 
       ProductGroup, 
       RecordID 
FROM ProductUsageData 
GROUP BY ProductGroup, RecordID  
ORDER BY RecordID ASC;

我期待的输出如下

但我得到的输出与第一相同

请让我知道如何更正查询以获得所需的输出

专线小巴

一种典型的解决方案是对前cross join两个查询选择不同的产品组并从表中记录ID。这为您提供productGroup和的所有可能组合recordID。

然后，您可以使用引入原始表left join并进行汇总：

select
    g.productGroup,
    coalesce(sum(p.qty), 0) qty,
    r.recordID
from (select distinct productGroup from productUsageData) g
cross join (select distinct recordID from productUsageData) r
left join productUsageData p
    on  p.productGroup = g.productGroup
    and p.recordID = r.recordID
group by r.recordID, g.productGroup
order by r.recordID, g.productGroup

在现实世界中，您可能具有用于产品组和记录ID的单独的引用表，这将使查询更简单，更高效（因为它将避免需要select distinct子查询）。

DB Fiddle上的演示：

产品组| 数量 recordID 
：----------- | ：-| ：------- 
A | 39 | 1        
B | 0 | 1        
C | 0 | 1        
A | 12 | 2        
B | 17 | 2        
C | 0 | 2        
A | 39 | 3        
B | 0 | 3        
C | 27 | 3