我有一个简单的表,用于存储一堆股票的市值。我想看看我是否可以创建一个查询,返回如下内容:
cap_type type_count
micro 4
small 6
large 1
mega 2
我不知道如何调整这个查询来计算不同select case组中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);
您尝试过以下操作吗?还是我理解错了问题?
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;
UPD:你的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;
您可以使用子查询:
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 ║
╚═══════════╩════════════╝
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