--------------------------------- -- Author: HEROWANG(让你望见影子的墙) -- Date : 2009-11-23 07:57:15 ---------------------------------
IF OBJECT_ID('[news_p]') IS NOT NULL DROP TABLE [news_p] go CREATE TABLE [news_p] (newsid INT,pid INT) INSERT INTO [news_p] SELECT 1,1 UNION ALL SELECT 1,2 UNION ALL SELECT 1,3 UNION ALL SELECT 2,1 UNION ALL SELECT 2,2select * from [news_p] --------------------------------- -- Author: HEROWANG(让你望见影子的墙) -- Date : 2009-11-23 07:57:49 ---------------------------------
IF OBJECT_ID('[class]') IS NOT NULL DROP TABLE [class] go CREATE TABLE [class] (classid INT,pid INT) INSERT INTO [class] SELECT 1,2 UNION ALL SELECT 1,3select * from [class]select distinct newsid from news_p t where not exists (select 1 from class where pid not in (select pid from news_p where newsid=t.newsid) )newsid 1
--> 测试数据: @class declare @class table (newsid int,pid int) insert into @class select 1,1 union all select 1,2 union all select 1,3 union all select 2,1 union all select 2,2 --> 测试数据: @class_p declare @class_p table (classid int,pid int) insert into @class_p select 1,2 union all select 1,3 union all select 2,1 union all select 2,2select a.* from @class a,@class_p b where a.pid=b.pid and b.classid=2 newsid pid ----------- ----------- 1 1 1 2 2 1 2 2(4 行受影响)
select distinct newsid from news_p t where not exists (select 1 from class where classid=1 and pid not in (select pid from news_p where newsid=t.newsid) )
-- ============================================= -- Author: T.O.P -- Create date: 2009/11/23 -- Version: SQL SERVER 2005 -- ============================================= declare @tb table([newsid] int,[pid] int) insert @tb select 1,1 union all select 1,2 union all select 1,3 union all select 2,1 union all select 2,2declare @tb1 table([classid] int,[pid] int) insert @tb1 select 1,2 union all select 1,3 SELECT distinct Newsid from @tb a where not exists(select 1 from @tb1 where pid not in(select pid from @tb where a.[newsid] = [newsid]) ) --测试结果: /* Newsid ----------- 1(1 row(s) affected) */
-- ============================================= -- Author: T.O.P -- Create date: 2009/11/23 -- Version: SQL SERVER 2005 -- ============================================= declare @tb table([newsid] int,[pid] int) insert @tb select 1,1 union all select 1,2 union all select 1,3 union all select 2,1 union all select 2,2declare @tb1 table([classid] int,[pid] int) insert @tb1 select 1,2 union all select 1,3 SELECT distinct Newsid from @tb a where not exists(select 1 from @tb1 where classid=1 and pid not in(select pid from @tb where a.[newsid] = [newsid]) ) --测试结果: /* Newsid ----------- 1(1 row(s) affected) */
from news_p join class on news_p.pid=class.pid
where classid='你的值'
1 1 1 2
1 2 1 3
1 3
2 1
2 2当我输入的classid = 1的时候,查询newsid的结果是 1
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-11-23 07:57:15
---------------------------------
IF OBJECT_ID('[news_p]') IS NOT NULL
DROP TABLE [news_p]
go
CREATE TABLE [news_p] (newsid INT,pid INT)
INSERT INTO [news_p]
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,2select * from [news_p]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-11-23 07:57:49
---------------------------------
IF OBJECT_ID('[class]') IS NOT NULL
DROP TABLE [class]
go
CREATE TABLE [class] (classid INT,pid INT)
INSERT INTO [class]
SELECT 1,2 UNION ALL
SELECT 1,3select * from [class]select distinct newsid
from news_p t
where not exists (select 1 from class where
pid not in (select pid from news_p where newsid=t.newsid)
)newsid
1
是什么意思?
classid pid
1 2
1 3
2 1
2 2
我想指定一个classid,又要加些什么呢?
declare @class table (newsid int,pid int)
insert into @class
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2
--> 测试数据: @class_p
declare @class_p table (classid int,pid int)
insert into @class_p
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2select a.* from @class a,@class_p b where a.pid=b.pid
and b.classid=2
newsid pid
----------- -----------
1 1
1 2
2 1
2 2(4 行受影响)
select distinct newsid
from news_p t
where not exists (select 1 from class where classid=1 and
pid not in (select pid from news_p where newsid=t.newsid)
)
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/23
-- Version: SQL SERVER 2005
-- =============================================
declare @tb table([newsid] int,[pid] int)
insert @tb
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2declare @tb1 table([classid] int,[pid] int)
insert @tb1
select 1,2 union all
select 1,3
SELECT distinct Newsid
from @tb a
where not exists(select 1 from @tb1 where pid not in(select pid from @tb where a.[newsid] = [newsid]) )
--测试结果:
/*
Newsid
-----------
1(1 row(s) affected)
*/
最后一个问题:如果newsid有几十万个,那这个查询的效率怎么样?这个问题完了就结帖
-- Author: T.O.P
-- Create date: 2009/11/23
-- Version: SQL SERVER 2005
-- =============================================
declare @tb table([newsid] int,[pid] int)
insert @tb
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,2declare @tb1 table([classid] int,[pid] int)
insert @tb1
select 1,2 union all
select 1,3
SELECT distinct Newsid
from @tb a
where not exists(select 1 from @tb1 where classid=1 and pid not in(select pid from @tb where a.[newsid] = [newsid]) )
--测试结果:
/*
Newsid
-----------
1(1 row(s) affected)
*/