SQL Case when condition more than

Question

我有product和agegroup桌子。

我在匹配来自agegroup表的id和来自product表的agegroup时连接这两个表。

我使用的查询如下所示；

WITH CTE_AgeGroup AS                                                                            
                (
                SELECT  p.agegroup,
                      CASE
                             WHEN AgeFromMonths >=0 AND AgeToMonths <= 24 
                               THEN
                                    CASE 
                                          WHEN AgeFromMonths >=0 AND AgeToMonths <= 3 THEN '0-3 Months'
                                          WHEN AgeFromMonths >=3 AND AgeToMonths <= 6 THEN '3-6 Months'
                                          WHEN AgeFromMonths >=6 AND AgeToMonths <= 9 THEN '6-9 Months'
                                          WHEN AgeFromMonths >=9 AND AgeToMonths <= 12 THEN '9-12 Months'
                                          WHEN AgeFromMonths >=12 AND AgeToMonths <= 18 THEN '12-18 Months'
                                          WHEN AgeFromMonths >=18 AND AgeToMonths <= 24 THEN '18-24 Months'
                                          END  
                           END as  'age'
                FROM agegroup a inner join product p on
                a.id= p.agegroup

                ),
                  CTE_AgeGroupResultSet AS
                (
                   select
                        Age, Count(*) AS CountAge from CTE_AgeGroup  group by Age   
                      )
                select * from CTE_AgeGroupResultSet order by age;  

然而，我的问题是，如果产品A在“0-3个月”内列出，而产品B在“3-6个月”内列出，这两个产品不会在“0-24个月”内列出。

Answer 1

您可以在group by中使用with rollup来获取总计数：

WITH CTE_AgeGroup AS
(
    SELECT p.agegroup,
           CASE WHEN AgeFromMonths >=0 AND AgeToMonths <= 24 
                THEN CASE WHEN AgeFromMonths >=0 AND AgeToMonths <= 3 
                          THEN '0-3 Months'
                          WHEN AgeFromMonths >=3 AND AgeToMonths <= 6 
                          THEN '3-6 Months'
                          WHEN AgeFromMonths >=6 AND AgeToMonths <= 9 
                          THEN '6-9 Months'
                          WHEN AgeFromMonths >=9 AND AgeToMonths <= 12 
                          THEN '9-12 Months'
                          WHEN AgeFromMonths >=12 AND AgeToMonths <= 18 
                          THEN '12-18 Months'
                          WHEN AgeFromMonths >=18 AND AgeToMonths <= 24 
                          THEN '18-24 Months'
                      END  
            END as 'age'
    FROM agegroup a 
         inner join product p 
            on a.id= p.agegroup
),
CTE_AgeGroupResultSet AS
(
    select case when grouping (Age) = 1 then '0-24' else Age end Age, 
           Count(*) AS CountAge 
      from CTE_AgeGroup
     group by Age WITH ROLLUP
)
select * 
  from CTE_AgeGroupResultSet 
 order by age;

此外，如果将varchar列添加到包含组描述的agegroup中，将完全消除大小写。

Answer 2

我不确定为什么你会选择这种函数。最好使用UNION运算符(这只需要产品表和年龄组表的并集)

完全外连接(连接两个表并从两个表中获取数据，即使两个表中都有空值)。