mysql - 如何合并select * 和select count(*)两种结果?
本文介绍了mysql - 如何合并select * 和select count(*)两种结果?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
问 题
现有一个组织表,列ID,PARENT_ID
我要做两次查询,第一次查询查询所有结果
select ID,PID from SM_ORGANIZATION;
+--------------------------------------+--------------------------------------+
| ID | PID |
+--------------------------------------+--------------------------------------+
| 02d48356-01a4-4e94-931e-8ded0bd7f3e5 | NULL |
| 03bb50c0-33a2-11e5-fc3e-2fe7bed2d47f | NULL |
| 0bb3a2e5-ee6f-11e5-fd4f-1b751af35d55 | NULL |
| 1fd0edc1-da90-11e5-fce6-21c5a127d649 | 43def2ae-a881-11e5-fc3e-35dcca6e148c |
| 2f5a44dd-54d0-11e5-fcd5-99ae2cb8560f | 43def2ae-a881-11e5-fc3e-35dcca6e148c |
| 3b3c6659-dff3-40f3-b528-2f595ac1c36b | NULL |
| 43def2ae-a881-11e5-fc3e-35dcca6e148c | NULL |
| 75241a24-b0a1-4101-9c09-4dd9f5e84f50 | NULL |
| 8305a3da-fef3-11e6-e79e-767a95d254a2 | NULL |
| e8d71f93-cdc3-4bdd-aa0c-1bc661a419ec | NULL |
| fd026b14-ec9c-4f4f-9178-ad3b4ff1f21f | NULL |
+--------------------------------------+--------------------------------------+
第二次查询是根据第一次查询出的每条结果ID作为条件,去查询PID,用来判断是不是父节点
SELECT
IF(IS_LEAF = 0, 0, 1) IS_LEAF
FROM
(SELECT
COUNT(*) IS_LEAF
FROM SM_ORGANIZATION
WHERE PID ='43def2ae-a881-11e5-fc3e-35dcca6e148c'
) SM_ORGANIZATION;
+---------+
| IS_LEAF |
+---------+
| 1 |
+---------+
现在我想把两次查询合并成一条查询语句,返回ID,PID,IS_LEAF三列应该怎么写?
+-------------------+--------------------+------------------+
| ID | PID |IS_LEAF |
+-------------------+--------------------+------------------+
解决方案
SELECT s1.*,IF(s2.PID IS NULL,0,1)
FROM SM_ORGANIZETION s1
LEFT JOIN (
SELECT PID
FROM SM_ORGANIZETION
GROUP BY PID) s2
ON s1.ID=s2.PID;
这篇关于mysql - 如何合并select * 和select count(*)两种结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文