SQL查询值
问题描述:
试图让不同的行的计数在一个查询中,并且在一个行SQL查询值
表1 ..
ID name
1 a
2 b
3 c
表2
ID Parent status
1 1 0
2 1 1
3 1 0
4 1 0
5 1 2
6 2 0
期望结果(不同子元素的计数)
ID name 0a 1s 2s
1 a 3(count of 0s) 1 (counts of 1s) 2 (count of 2s)
,我们可以得到这样一个查询中..
我曾尝试是导致我值3行
Select t1.id, t1.name, count(status) from TABLE_1 t1 Left JOIN TABLE_2 t2
ON t1.id = t2.parent
group by status
ID name status
1 a 3
1 a 1
1 a 1
2 b 1
答
您可以加入并使用Count和Case语句。例如:
SELECT T1.ID
,T1.name
,COUNT(CASE WHEN T2.status = 0 THEN T2.ID END) [0a]
,COUNT(CASE WHEN T2.status = 1 THEN T2.ID END) [1s]
,COUNT(CASE WHEN T2.status = 2 THEN T2.ID END) [2s]
FROM TABLE_1 T1 LEFT JOIN TABLE_2 T2 ON T1.ID = T2.Parent
GROUP BY T1.ID, T1.name
会产生输出:
ID name 0s 1s 2s
1 a 3 1 1
2 b 1 0 0
3 c 0 0 0
下面是完整的代码sqlfiddle:http://sqlfiddle.com/#!6/ab92d/10/0
+0
所有这三种解决方案都可以工作,但是这种查询所花费的时间最少,因此将其标记为答案。 – Luckyy
答
Select t1.id, t1.name,
(select count(*) from t2 where t2.parent = t1.id and status=0) as 0s,
(select count(*) from t2 where t2.parent = t1.id and status=1) as 1s,
(select count(*) from t2 where t2.parent = t1.id and status=2) as 2s
from t1 inner join t2
ON t1.id = t2.parent
group by t1.id;
答
您可以使用条件聚集此:
Select t1.id, t1.name,
coalesce(sum(status=0), 0) AS '0s',
coalesce(sum(status=1), 0) AS '1s',
coalesce(sum(status=2), 0) AS '2s'
from TABLE_1 t1
Left JOIN TABLE_2 t2 ON t1.id = t2.parent
group by t1.id, t1.name
+0
一个更简单和有效的方法。 –
你用什么? MySql或SqlServer - sql方言有不同的实现。看起来你可以在连接表上使用PIVOT:看看这里:https://*.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql –
一旦你'我想到了你正在使用的RDBMS,实际上考虑处理应用程序代码中的数据显示问题。 – Strawberry
它的Mysql现在..认为它没有多大关系 – Luckyy