mysql - 在搜索范围的 MySQL WHEN 语句中捕获 NULL 或零结果

Question

我正在尝试按价格范围从我的数据库中提取产品销售额。

我有一个相当复杂的 SQL 查询，它有效:

SELECT 
    SUM((t.price-t.discount)*t.quantity) as totalValue, 
    MAX(t.price) as maxP, 
    t.range AS score_range,  
    COUNT(*) AS count  
        FROM 
            (SELECT products.price, 
            salesRecords.discount,
            salesRecords.quantity,  
                CASE WHEN products.price >=0 AND products.price <50 THEN  '0-49' 
                WHEN products.price >=50 AND products.price <100 THEN  '50-99' 
                WHEN products.price >=100 AND products.price <200 THEN  '100-199' 
                WHEN products.price >=200 AND products.price <350 THEN  '200-349' 
                WHEN products.price >=200 AND products.price <350 THEN  '200-349'  
                WHEN products.price >=350 AND products.price <500 THEN  '350-499' 
                WHEN products.price >=500 AND products.price <800 THEN  '500-799' 
                WHEN products.price >=800 AND products.price <1200 THEN  '800-1199' 
                WHEN products.price >=1200 AND products.price <1800 THEN  '1200-1799' 
                WHEN products.price >=1800 AND products.price <2500 THEN  '1800-2499' 
                ELSE '2500 +' END 
            AS  range  FROM salesRecords 
                LEFT JOIN products ON products.id=salesRecords.itemNo 
                ORDER BY products.price DESC)t 
    GROUP BY t.range 
    ORDER BY maxP DESC

希望您能看到这里发生了什么。我们在价格范围内对产品进行分组，对销售值(value)求和，因此得到如下输出:

totalValue  maxP         score_range    count   
8381    251.17       200-349        35
32522   199.00       100-199        198
22614   99.95        50-99      271
41825   49.99        0-49       2765

但是，如您所见，对于这个特定的数据集，存在许多差距。

我只有 score_range200-349, 100-199,50-99, 0-49 的值，缺少 35-499, 500-799 的任何数据等等。这是因为没有这些值的数据……这很好……除了……我将此数据作为 JSON 对象注入(inject)到 AmCharts 雷达图中，以便为了让数据对我的应用程序真正有意义，我需要拥有所有范围，并简单地用 zeros 填充它们，所以我想要得到的是:

totalValue  maxP     score_range    count   
0       3500         2500 +             0
0       2499         1800-2499          0
0       1799         1200-1799          0
0       1199         800-1199           0
0       799          500-799            0
0       499          350-499            0
8381    251.17       200-349            35
32522   199.00       100-199            198
22614   99.95        50-99              271
41825   49.99        0-49               2765

Answer 1

您需要一张驾驶台，以便您拥有所有范围。这是一个例子:

SELECT tsum.totalValue, 
       maxP, 
       range.range AS score_range,  
       coalesce(count, 0) count  
FROM (select '0-49' as range union all
      select '50-99' union all
      . . .
     ) as range left outer join
     (SELECT SUM((t.price-t.discount)*t.quantity) as totalValue, 
             MAX(t.price) as maxP, 
             t.range AS score_range,  
             COUNT(*) AS count
      from (SELECT products.price, 
                   salesRecords.discount,
                   salesRecords.quantity,  
                   (CASE WHEN products.price >=0 AND products.price <50 THEN  '0-49' 
                         WHEN products.price >=50 AND products.price <100 THEN  '50-99' 
                         WHEN products.price >=100 AND products.price <200 THEN  '100-199' 
                         WHEN products.price >=200 AND products.price <350 THEN  '200-349' 
                         WHEN products.price >=200 AND products.price <350 THEN  '200-349'  
                         WHEN products.price >=350 AND products.price <500 THEN  '350-499' 
                         WHEN products.price >=500 AND products.price <800 THEN  '500-799' 
                         WHEN products.price >=800 AND products.price <1200 THEN  '800-1199' 
                         WHEN products.price >=1200 AND products.price <1800 THEN  '1200-1799' 
                         WHEN products.price >=1800 AND products.price <2500 THEN  '1800-2499' 
                         ELSE '2500 +'
                    END) AS  range
            FROM salesRecords LEFT JOIN
                 products
                 ON products.id=salesRecords.itemNo 
           )t
     ) tsum
     on ranges.range = tsum.score_range
GROUP BY ranges.range 
ORDER BY maxP DESC

但是，这个例子表明您真的希望有一个范围表，其中定义了范围的限制。然后您可以将整个 case 语句作为一个连接来执行。