时间戳

首页 > 解决方案 > 选择 GROUP 中的所有记录，即使此时没有对应的数据

sql - 选择 GROUP 中的所有记录，即使此时没有对应的数据

问题描述

寻找一种在结果中包含所有“CASE WHEN”选项的方法。所以即使没有落入Range'Between 1 and 14 days' and MyOrder= 1的记录，最终结果也应该包含这样的记录。Suma这些行Docs可以为空或等于 0。

SELECT 
    MIN(b.Range) Nazwa, SUM(b.Left) Suma, COUNT(*) Docs
FROM
    (SELECT
         CASE
             WHEN a.DaysLate BETWEEN 1 AND 14 
                 THEN 'Between 1 and 14 days'
             WHEN a.DaysLate BETWEEN 15 AND 30 
                 THEN 'Between 15 and 30 days'
             ELSE 'Over 30 days'
         END AS Range,
         CASE
             WHEN a.DaysLate BETWEEN 1 AND 14 THEN 1
             WHEN a.DaysLate BETWEEN 15 AND 30 THEN 2
             ELSE 3
         END AS MyOrder,
         Amount, DaysLate
     FROM 
         (#subquery) a) b
GROUP BY 
    b.MyOrder

到目前为止，我发现的所有解决方案都依赖于JOIN包含所有潜在值的最终表格CASE。但这会产生一种冗余。有没有更简单的方法来实现这个？

我正在使用数据库兼容级别设置为 150 的 Azure SQL。

标签： sqlsql-servertsqlcase

解决方案

我倾向于使用通用层表。这样我就可以为多个主人服务，并将逻辑排除在代码之外。

下面是一个过于简化的演示

例子

Declare @Tier table (Tier_Name varchar(50),Tier_R1 int, Tier_R2 int,Tier_Desc varchar(100))
Insert Into @Tier values
 ('DAYSLATE', 1, 14    ,'Between 1 and 15 days')
,('DAYSLATE',15, 30    ,'Between 15 and 30 days')
,('DAYSLATE',31, 999999,'Over 30 days')

Declare @SampleData table (DaysLate int,Amount money)
Insert Into @SampleData values
 (8,25000)
,(10,1000)
,(22,50000)
,(90,60000)


Select Tier_Desc
      ,Amount = sum(Amount)
      ,Cnt    = sum(1)
 From  @Tier A
 Join  @SampleData B
   on  Tier_Name='DAYSLATE' and DaysLate between Tier_R1 and Tier_R2
 Group By Tier_R1,Tier_Desc
 Order By Tier_R1

退货

Tier_Desc               Amount      Cnt
Between 1 and 15 days   26000.00    2
Between 15 and 30 days  50000.00    1
Over 30 days            60000.00    1

推荐阅读