選擇案例有好的解決方案嗎？我的選擇查詢看起來很愚蠢。:))

select 
    code,
    max(age) age_level,
    max(age_interval) age_interval,
    sum(total) total
from 
    (select 
        (case  
            when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then '?св?р ?е' 
            when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then 'Залуу ?е'
            when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then 'Идэр ?е'
            when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then 'Хижээл ?е'
            when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then '?т?л ?е'            
            when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then '?нд?р ?е'
            else 'other'
        end) age,
        (case  
            when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then 'kid'
            when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then 'bigger_kid'
            when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then 'adult'
            when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then 'bigger_adult'
            when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then 'big_adult'            
            when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then 'caption'
            else 'other'
        end) code,
        (case  
            when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then '13-18'
            when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then '19-25'
            when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then '26-35'
            when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then '36-45'
            when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then '46-60'            
            when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then '60-100'
            else 'other'
        end) age_interval,
        count(u.id) total
    from sec_survey_users su 
        left join sec_users u on su.user_id = u.id 
    where su.survey_id = 'D3A21B1C2D8334C9E055824F7FFC5DF4' group by u.birth_date) mtable group by mtable.code;

uj5u.com熱心網友回復：

像這樣的事情可以減少重復（或在永久表中定義年齡范圍并跳過with子句）：

with age_classes (min_age, max_age, age_interval, code, age) as
     ( select 13, 18,  '13-18',  'teen',                   'Teenager'       from dual union all
       select 19, 25,  '19-25',  'young',                  'Young adult'    from dual union all
       select 26, 35,  '26-35',  'still_young',            'It won''t last' from dual union all
       select 36, 45,  '36-45',  'slightly_less_young',    'Middle aged'    from dual union all
       select 46, 50,  '46-60',  'still_got_it',           'Still got it'   from dual union all    
       select 60, 100, '60-100', 'exceeding_expectations', 'Free bus pass'  from dual
     )
select nvl(ac.code,'other') as code
     , nvl(ac.age,'other')  as age
     , nvl(ac.age_interval,'other') as age_interval
     , count(*) total
from   sec_survey_users su 
       left join sec_users u on su.user_id = u.id 
       left join age_classes ac on floor(months_between(sysdate, u.birth_date)/12) between ac.min_age and ac.max_age
group by
       ac.code
     , ac.age
     , ac.age_interval;

對于永久表，您可以將age_interval標簽定義為連接最小/最大年齡列的虛擬列，以避免出現一些冗余。該表還可以方便地用于報告應用程式等中的下拉串列。

uj5u.com熱心網友回復：

您可以使用CTE重寫查詢，以防止重復寫入 將在GROUP BY串列中使用的它的FLOOR(MONTHS_BETWEEN(sysdate, u.birth_date) / 12)哪個和年齡別名形式，例如

WITH u0 AS
 (SELECT FLOOR(MONTHS_BETWEEN(sysdate, birth_date) / 12) AS age, COUNT(id) AS total
    FROM sec_users
   GROUP BY FLOOR(MONTHS_BETWEEN(sysdate, birth_date) / 12)),
u2 AS
 (SELECT (CASE
           WHEN age BETWEEN 13 AND 18 THEN
            '?св?р ?е'
           WHEN age BETWEEN 19 AND 25 THEN
            'Залуу ?е'
           WHEN age BETWEEN 26 AND 35 THEN
            'Идэр ?е'
           WHEN age BETWEEN 36 AND 45 THEN
            'Хижээл ?е'
           WHEN age BETWEEN 46 AND 60 THEN
            '?т?л ?е'
           WHEN age BETWEEN 61 AND 100 THEN
            '?нд?р ?е'
           ELSE
            'other'
         END) age,
         (CASE
           WHEN age BETWEEN 13 AND 18 THEN
            'kid'
           WHEN age BETWEEN 19 AND 25 THEN
            'bigger_kid'
           WHEN age BETWEEN 26 AND 35 THEN
            'adult'
           WHEN age BETWEEN 36 AND 45 THEN
            'bigger_adult'
           WHEN age BETWEEN 46 AND 60 THEN
            'big_adult'
           WHEN age BETWEEN 61 AND 100 THEN
            'caption'
           ELSE
            'other'
         END) code,
         (CASE
           WHEN age BETWEEN 13 AND 18 THEN
            '13-18'
           WHEN age BETWEEN 19 AND 25 THEN
            '19-25'
           WHEN age BETWEEN 26 AND 35 THEN
            '26-35'
           WHEN age BETWEEN 36 AND 45 THEN
            '36-45'
           WHEN age BETWEEN 46 AND 60 THEN
            '46-60'
           WHEN age BETWEEN 61 AND 100 THEN
            '61-100'
           ELSE
            'other'
         END) age_interval,
         SUM(total) AS total
    FROM u
   GROUP BY u.age)
SELECT code,
       MAX(age) AS age_level,
       MAX(age_interval) AS age_interval,
       SUM(total) AS total
  FROM sec_survey_users su
  LEFT JOIN u
    ON su.user_id = u.id
 WHERE su.survey_id = 'D3A21B1C2D8334C9E055824F7FFC5DF4'
 GROUP BY code

標籤：sql 甲骨文 plsql

上一篇：為什么這個feDisplacementMap過濾器不起作用？

下一篇：選擇每個月插入日期的最大日期