Mysql案例问题

Question

我通过从表单域获取某些变量来创建一个sql查询。但有些领域是相互重复的。因此，这打破了MySQL的案例结构。举个例子：

(case 
  when `field` IN (1,2) then 'area 1'
  when `field` = 3 then 'area 2'
  when `field` = 4 then 'area 3'
  when `field` IN (1,2,3,4) then 'area 4'
end) as cases

Group by cases

我知道“区域4”永远不会出现在结果中，但有没有办法显示出来呢？

sqlfiddle

Answer 1

按优先级对它们进行排序。首先是最匹配的查询，然后是较小的查询。

就像这样：

(case 
  when `field` IN (1,2,3,4) then 'area 4'
  when `field` IN (1,2) then 'area 1'
  when `field` = 3 then 'area 2'
  when `field` = 4 then 'area 3'
end) as cases

Answer 2

参考您的SQLfiddle

下面是我能想到的查询

SELECT ROUND(SUM(quantity)/1000) as `quantity`,
ROUND(SUM(value)/1000) as `value`,
(case 
   when istpoz REGEXP '^(7207|7218|7224|7206)' 
   then
    case when istpoz REGEXP '^(7206|7207)' 
         then 'Yarı Mamul (Alaşımsız)'   else 'Yarı Mamul (Tamamı)'  end
    else
    case
      when istpoz REGEXP '^(7224)' then 'Yarı Mamul (Alaşımlı Çelik)' 
      when istpoz REGEXP '^(7218)' then 'Yarı Mamul (Paslanmaz Ç.)' 
    end
end) as istpozz,
`year` FROM 
export as x WHERE 
`year` IN (2016) 
AND `month` IN (1,2,3,4) 
AND `country_id` IN (1) 
AND `istpoz` REGEXP '^(7206|7207|7224|7218)' GROUP BY istpozz,year