34,575
社区成员
发帖
与我相关
我的任务
分享
declare @userid int
set @userid=1
select * from 表名 a where (a.ParentID=0 and a.userid=@userid)
or exists(select 1 from 表名 where userid=a.userid and a.id=ParentID)
--结果:
ID ParentID Title UserID
----------- ----------- ------------ -----------
1 0 testtitle 2
5 0 bbbbbbestg 1
--> 测试数据: @s
declare @t table (ID int,ParentID int,Title varchar(12),UserID int)
insert into @t
select 1,0,'testtitle',2 union all
select 2,0,'btesttitle',3 union all
select 3,1,'aaatesttitle',1 union all
select 4,1,'bbbbbbestg',1 union all
select 5,0,'bbbbbbestg',1
declare @userid int
set @userid=1
select * from @t a where (a.ParentID=0 and a.userid=@userid)
or exists(select 1 from @t where userid=@userid and a.id=ParentID)
--> liangCK小梁 于2008-10-18
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,ParentID INT,Title VARCHAR(12),UserID INT)
INSERT INTO @T
SELECT 1,0,'testtitle',2 UNION ALL
SELECT 2,0,'btesttitle',3 UNION ALL
SELECT 3,1,'aaatesttitle',1 UNION ALL
SELECT 4,1,'bbbbbbestg',1
--SQL查询如下:
SELECT DISTINCT a.ID,a.Title
FROM @T AS a
JOIN
(
SELECT ParentID FROM @T
WHERE UserID=1
AND UserID<>0
) AS b
ON a.ID=b.ParentID
/*
ID Title
----------- ------------
1 testtitle
(1 行受影响)
*/
DECLARE @TB TABLE(ID INT ,ParentID INT,Title VARCHAR(20),UserID INT)
INSERT @TB
SELECT 1, 0, 'testtitle', 2 UNION ALL
SELECT 2, 0, 'btesttitle', 3 UNION ALL
SELECT 3, 1, 'aaatesttitle', 1 UNION ALL
SELECT 4, 1, 'bbbbbbestg', 1
SELECT * FROM @TB WHERE UserID=1 AND ParentID<>0
/*
ID ParentID Title UserID
----------- ----------- -------------------- -----------
3 1 aaatesttitle 1
4 1 bbbbbbestg 1
(2 row(s) affected)
*/
--> liangCK小梁 于2008-10-18
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,ParentID INT,Title VARCHAR(12),UserID INT)
INSERT INTO @T
SELECT 1,0,'testtitle',2 UNION ALL
SELECT 2,0,'btesttitle',3 UNION ALL
SELECT 3,1,'aaatesttitle',1 UNION ALL
SELECT 4,1,'bbbbbbestg',1
--SQL查询如下:
SELECT DISTINCT a.ID,a.Title
FROM @T AS a
JOIN
(
SELECT ParentID FROM @T
WHERE UserID=1
) AS b
ON a.ID=b.ParentID
/*
ID Title
----------- ------------
1 testtitle
(1 行受影响)
*/
--> Test Data: @T
declare @T table ([ID] int,[ParentID] int,[Title] varchar(12),[UserID] int)
insert into @T
select 1,0,'testtitle',2 union all
select 2,0,'btesttitle',3 union all
select 3,1,'aaatesttitle',1 union all
select 4,1,'bbbbbbestg',1
select * from @T
--Code
select [ID],[Title] from @T
where [ID] in(select [ParentID] from @T where [ParentID]<>0 and [UserID]=1)
--Result
/*
ID Title
----------- ------------
1 testtitle
*/
------------------------------------
-- Author: happyflsytone
-- Date:2008-10-18 17:34:13
------------------------------------
-- Test Data: t2
IF OBJECT_ID('t2') IS NOT NULL
DROP TABLE t2
Go
CREATE TABLE t2(ID INT,ParentID NVARCHAR(1),Title NVARCHAR(12),UserID INT)
Go
INSERT INTO t2
SELECT 1,'0','testtitle',2 UNION ALL
SELECT 2,'0','btesttitle',3 UNION ALL
SELECT 3,'1','aaatesttitle',1 UNION ALL
SELECT 4,'1','bbbbbbestg',1
GO
--Start
select *
from t2 a
where exists(select 1 from t2 where userid = 1 and a.id = parentid)
--Result:
/*
ID ParentID Title UserID
----------- -------- ------------ -----------
1 0 testtitle 2
(1 行受影响)
*/
--End
SELECT * FROM TB WHERE UserID=1 AND parentID<>0