MySQL：CASE，GROUP和ORDER BY

Question

我有一个简单的表格，其中存储了一堆股票的市值。我想尝试创建一个查询，返回类似以下的结果：

cap_type  type_count
micro     4
small     6
large     1
mega      2

我不确定如何调整此查询以计算不同选择案例组中cap_types的数量。以下是我目前拥有的：

SELECT CASE
    WHEN market_cap < 10 THEN 'micro' 
    WHEN market_cap >= 10 < 50 THEN 'small' 
    WHEN market_cap >= 50 < 100 THEN 'large' 
    ELSE 'mega' 
END AS cap_type
FROM stocks
GROUP BY CASE
    WHEN market_cap < 10 THEN 'micro' 
    WHEN market_cap >= 10 < 50 THEN 'small' 
    WHEN market_cap >= 50 < 100 THEN 'large' 
    ELSE 'mega' 
    END
ORDER BY cap_type ASC

以下是一些示例数据：

CREATE TABLE `stocks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stock` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
  `market_cap` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
INSERT INTO temp (`stock`,`market_cap`) VALUES ('MSFT', 40);
INSERT INTO temp (`stock`,`market_cap`) VALUES ('MINI', 4);
INSERT INTO temp (`stock`,`market_cap`) VALUES ('GOOG', 50);
INSERT INTO temp (`stock`,`market_cap`) VALUES ('F', 90);

- Gunnar

Answer 1

您可以使用子查询：

SELECT sub.cap_type, COUNT(*) AS type_count
FROM (
    SELECT CASE WHEN market_cap < 10 THEN 'micro' 
                WHEN market_cap < 50 THEN 'small' 
                WHEN market_cap < 100 THEN 'large' 
                ELSE 'mega' 
           END AS cap_type
    FROM temp
) AS sub  
GROUP BY sub.cap_type
ORDER BY 
CASE sub.cap_type
  WHEN 'micro' THEN 1
  WHEN 'small' THEN 2
  WHEN 'large' THEN 3
  ELSE 4
END;

SqlFiddleDemo

或者只需将COUNT(*)添加到您现有的查询中：

SELECT CASE WHEN market_cap < 10 THEN 'micro' 
            WHEN market_cap < 50 THEN 'small' 
            WHEN market_cap < 100 THEN 'large' 
            ELSE 'mega'
       END AS cap_type
       ,COUNT(*) AS type_count
FROM temp
GROUP BY CASE WHEN market_cap < 10 THEN 'micro' 
              WHEN market_cap < 50 THEN 'small' 
              WHEN market_cap < 100 THEN 'large' 
              ELSE 'mega' 
          END
ORDER BY 
CASE cap_type
  WHEN 'micro' THEN 1
  WHEN 'small' THEN 2
  WHEN 'large' THEN 3
  ELSE 4
END

---

如果你需要所有的组，可以使用子查询来列出它们并使用 LEFT JOIN：

SELECT sub.cap_type, COUNT(t.market_cap) AS type_count
FROM (SELECT 'micro' AS cap_type
      UNION ALL SELECT 'small'
      UNION ALL SELECT 'large' 
      UNION ALL SELECT 'mega') AS sub
LEFT JOIN temp t
  ON CASE
    WHEN t.market_cap < 10 THEN 'micro' 
    WHEN t.market_cap < 50 THEN 'small' 
    WHEN t.market_cap < 100 THEN 'large' 
    ELSE 'mega' 
    END = sub.cap_type
GROUP BY sub.cap_type
ORDER BY 
  CASE cap_type
    WHEN 'micro' THEN 1
    WHEN 'small' THEN 2
    WHEN 'large' THEN 3
    ELSE 4
  END

SqlFiddleDemo2

输出：

╔═══════════╦════════════╗
║ cap_type  ║ type_count ║
╠═══════════╬════════════╣
║ micro     ║          1 ║
║ small     ║          1 ║
║ large     ║          2 ║
║ mega      ║          0 ║
╚═══════════╩════════════╝   

- Lukasz Szozda

Answer 2

你尝试过以下方法吗？还是我理解问题有误？

SELECT 
  CASE
    WHEN market_cap < 10 THEN 'micro' 
    WHEN market_cap >= 10 < 50 THEN 'small' 
    WHEN market_cap >= 50 < 100 THEN 'large' 
    ELSE 'mega' 
  END AS cap_type,
  COUNT(*) as count
FROM stocks
GROUP BY cap_type
ORDER BY cap_type ASC;

更新：你的CASE语句包含细微错误（你漏掉了AND，并且条件没有意义，虽然形式上是正确的），这是正确的查询语句（匹配的WHEN条件停止处理）：

SELECT 
  CASE
    WHEN market_cap < 10 THEN 'micro' 
    WHEN market_cap < 50 THEN 'small' 
    WHEN market_cap < 100 THEN 'large' 
    ELSE 'mega' 
  END AS cap_type,
  COUNT(*) as count
FROM stocks
GROUP BY cap_type
ORDER BY cap_type ASC;

- newtover

---

这个方法很管用，谢谢！现在我看了一下SQL语句，可以清楚地看到CASE语句的作用以及如何/在哪里调用其他字段。 - Gunnar

@Gunnar，顺便说一下，我复制粘贴了你的CASE语句，但它包含错误，我更新了答案以得出正确的解决方案。 - newtover

Answer 3

SELECT 
CASE
    WHEN market_cap < 10 THEN 'micro'
    WHEN market_cap >= 10 and market_cap < 50 THEN 'small'
    WHEN market_cap >= 50 and market_cap < 100 THEN 'large'
    ELSE 'mega'
END AS cap_type,
count(*)
FROM stock
GROUP BY CASE
    WHEN market_cap < 10 THEN 'micro'
    WHEN market_cap >= 10 and market_cap < 50 THEN 'small'
    WHEN market_cap >= 50 and market_cap < 100 THEN 'large'
    ELSE 'mega'
END
ORDER BY cap_type ASC;

SQLFiddle.

（注：该文本为含有HTML标签的内容，原文已被保留。）

- Nikhil