需要使用subselect获取一行结果
需要使用subselect获取一行结果
提问于 2017-01-04 15:27:16
sql查询如下：
SELECT
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 1") AS test_1 ,
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 2") AS test_2 ,
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 3") AS test_3
FROM `table_name` GROUP BY `content`并得到如下结果：
test_1, test_2, test_3
2, 7, 0
2, 7, 0
2, 7, 0
2, 7, 0
2, 7, 0
2, 7, 0
2, 7, 0
2, 7, 0
2, 7, 0
2, 7, 0只需要获得一行结果，如下所示：
test_1, test_2, test_3
2, 7, 0怎么做？我的查询有什么问题？
回答 4
Stack Overflow用户
回答已采纳
发布于 2017-01-04 15:30:15
看看这个。
SELECT distinct
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 1") AS test_1 ,
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 2") AS test_2 ,
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 3") AS test_3
FROM `table_name` GROUP BY `content`Stack Overflow用户
发布于 2017-01-04 16:02:23
您的查询应该像这样重写：
SELECT
SUM(CASE WHEN `content` = "test 1" THEN 1 ELSE 0 END) AS test_1,
SUM(CASE WHEN `content` = "test 2" THEN 1 ELSE 0 END) AS test_2,
SUM(CASE WHEN `content` = "test 3" THEN 1 ELSE 0 END) AS test_3
FROM
`table_name`
WHERE
`custom_id` IN (10,9,8,6,5,4,3,2,1);Stack Overflow用户
发布于 2017-01-04 15:33:30
只需在SELECT语句中添加TOP子句...
SELECT TOP 1
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 1") AS test_1 ,
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 2") AS test_2 ,
(SELECT COUNT(`id`) FROM `table_name` WHERE `custom_id` IN (10,9,8,6,5,4,3,2,1) AND `content` = "test 3") AS test_3
FROM `table_name` GROUP BY `content`页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41458369
复制相关文章
相似问题
腾讯云开发者
