表
id parentId content
1 null 'a'
2 null 'b'
3 null 'c'
4 3 'd'
5 3 'e'
6 2 'f'
欲得到的结果,就是说:如果没有他的child 那么就显示他本身,如果有child则显示id最大的那个child
1 null 'a'
5 3 'e'
6 2 'f' 多谢
id parentId content
1 null 'a'
2 null 'b'
3 null 'c'
4 3 'd'
5 3 'e'
6 2 'f'
欲得到的结果,就是说:如果没有他的child 那么就显示他本身,如果有child则显示id最大的那个child
1 null 'a'
5 3 'e'
6 2 'f' 多谢
where no exists(select 1 from tb where parentId=t.parentId and id>t.id)
where not exists(select 1 from tb where parentId=t.parentId and id>t.id)
,[parentID]
,[content]
from 表
where [id] in
(select
case when parentID is null then min([id])
else max([id]) end
from 表
group by parentID )
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,parentId int ,content varchar(10))
go
insert tb SELECT
1, null ,'a' UNION ALL SELECT
2, null , 'b' UNION ALL SELECT
3, null, 'c' UNION ALL SELECT
4, 3 ,'d'UNION ALL SELECT
5, 3 , 'e'UNION ALL SELECT
6, 2, 'f'
go
select *
FROM TB K
WHERE parentId IS NULL AND NOT EXISTS( SELECT * FROM TB WHERE K.id=parentId )
UNION ALL
SELECT *
FROM TB K
WHERE parentId IS NOT NULL AND NOT EXISTS(SELECT * FROM TB WHERE K.parentId=parentId AND K.id<ID)
/*
id parentId content
----------- ----------- ----------
1 NULL a
5 3 e
6 2 f
*/
WHERE NOT EXISTS(SELECT * FROM T WHERE parentId = A.Id)
AND NOT EXISTS(SELECT * FROM T
WHERE parentId = A.parentId AND Id > A.Id)
parentId=t.parentId
替换成
parentid = isnull(t.parentid, t.id)
就正确了可是我感觉这个效率不高啊
有没有其他方法?
select * from TB where id not in(select isnull(parentId,0) from TB))t group by isnull(parentId,0)
order by id
if object_id('tb') is not null
drop table tb
gocreate table tb(id varchar(10),parentid varchar(10),contents varchar(20))
goinsert into tb(id,parentid,contents)
select '1',null,'a'
union all
select '2',null,'b'
union all
select '3',null,'c'
union all
select '4','3','d'
union all
select '5','3','e'
union all
select '6','2','f'
goselect tb.id,tb.parentid,tb.contents
from tb join (select parentid,max(id) id from tb where parentid is not null group by parentid ) t on tb.parentid=t.parentid and tb.id=t.id
union
select id,parentid,contents
from tb
where parentid is null and id not in (select distinct parentid from tb where parentid is not null)drop table tb