WITH A AS( SELECT 1 id,'张3' name,0 pid FROM dual UNION ALL SELECT 2, '李4', 1 FROM DUAL UNION ALL SELECT 3, '王5',1 FROM DUAL UNION ALL SELECT 4, '里1', 2 FROM DUAL ) SELECT M.ID,M.NAME,COUNT(N.ID) S1 FROM A M,A N WHERE M.ID=N.PID GROUP BY M.ID,M.NAME HAVING COUNT(N.ID)>0 结果如下: ID NAME S1 ---------------------- ---- ---------------------- 1 张3 2 2 李4 1 2 rows selected
WITH t AS( SELECT 1 id,'张3' name,0 pid FROM dual UNION ALL SELECT 2, '李4', 1 FROM DUAL UNION ALL SELECT 3, '王5',1 FROM DUAL UNION ALL SELECT 4, '里1', 2 FROM DUAL ) SELECT id,NAME,Nvl(t1.num,0)num FROM t left join (SELECT pid,Count(pid)num FROM t GROUP BY pid)t1 ON t.id=t1.pid ORDER BY id
create table t(id int,name varchar2(20),pid int); select t.id, t.name, nvl(t1.c, 0) from t, (select pid, count(1) c from t group by pid) t1 where t.id = t1.pid(+) ID NAME NVL(T1.C,0) --------------------------------------- -------------------- ----------- 1 张3 2 2 李4 1 4 里1 0 3 王5 0
SELECT 1 id,'张3' name,0 pid FROM dual
UNION ALL
SELECT 2, '李4', 1 FROM DUAL
UNION ALL
SELECT 3, '王5',1 FROM DUAL
UNION ALL
SELECT 4, '里1', 2 FROM DUAL
)
SELECT M.ID,M.NAME,COUNT(N.ID) S1 FROM A M,A N
WHERE M.ID=N.PID
GROUP BY M.ID,M.NAME
HAVING COUNT(N.ID)>0
结果如下:
ID NAME S1
---------------------- ---- ----------------------
1 张3 2
2 李4 1 2 rows selected
SELECT 1 id,'张3' name,0 pid FROM dual
UNION ALL
SELECT 2, '李4', 1 FROM DUAL
UNION ALL
SELECT 3, '王5',1 FROM DUAL
UNION ALL
SELECT 4, '里1', 2 FROM DUAL
)
SELECT id,NAME,Nvl(t1.num,0)num FROM t left join
(SELECT pid,Count(pid)num FROM t GROUP BY pid)t1
ON t.id=t1.pid ORDER BY id
DROM子句 --指定查询来自哪个表或者试图
[WHERE 子句] --指定查询的条件
[GROUP BY 子句] --指定查询结果集的分组的条件
[HAVING 子句] --指定分组或者集合的查询条件
[ORDERBY 子句] --对查询的排列顺序
[UNION 子句] --多个SELET语句组合,得到结果集的并集
create table t(id int,name varchar2(20),pid int);
select t.id, t.name, nvl(t1.c, 0)
from t, (select pid, count(1) c from t group by pid) t1
where t.id = t1.pid(+) ID NAME NVL(T1.C,0)
--------------------------------------- -------------------- -----------
1 张3 2
2 李4 1
4 里1 0
3 王5 0