where up_id is nullselect id,count(*) 记录数 from tablename where id=1 and up_id=1select id,count(*) 记录数 from tablename where id=2 and up_id=2
try:1.如何取出up_id为空的记录? select * from 表 where up_id is null2.如何取出id为1和up_id为1的记录数以及id为2和up_id为2的记录数?在本例中结果为: id 记录数 1 4 2 1 select 1 id,count(*) 记录数 from 表 where up_id=1 or id=1 union all select 2 id,count(*) 记录数 from 表 where up_id=2 or id=2
1、select * from 表 where up_id is null 2、 select 1 id,count(*) 记录数 from 表 where up_id=1 or id=1 group by id union all select 2 id,count(*) 记录数 from 表 where up_id=2 or id=2 group by id
我不大了解你的需求。如果是想取出id 与up_id相同的记录的数量的话SQL语句如下 SELECT id,COUNT(*) 记录数 FROM tableName WHERE id=up_id AND up_id is not null GROUP BY id
太记得了,好像是下面的思路,没经过调试,你可以查一下select语句的帮助: select id,count(*) from( select id,up_id from t start with up_id is null connect prior.up_id = id ) group by up_id
试试这个,不是太了解你的需求 select id,count(*) from( select id from t connect by prior id = up_id start with up_id is null ) group by id
条件子句不能用COUNT
select * from 表 where up_id is null2.如何取出id为1和up_id为1的记录数以及id为2和up_id为2的记录数?在本例中结果为:
id 记录数
1 4
2 1
select 1 id,count(*) 记录数 from 表 where up_id=1 or id=1
union all
select 2 id,count(*) 记录数 from 表 where up_id=2 or id=2
2、
select 1 id,count(*) 记录数 from 表 where up_id=1 or id=1 group by id
union all
select 2 id,count(*) 记录数 from 表 where up_id=2 or id=2 group by id
SELECT id,COUNT(*) 记录数
FROM tableName
WHERE id=up_id AND up_id is not null
GROUP BY id
select id,count(*)
from(
select id,up_id
from t
start with
up_id is null
connect prior.up_id = id
)
group by up_id
select id,count(*)
from(
select id
from t
connect by prior id = up_id
start with up_id is null
)
group by id