IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba') BEGIN DROP TABLE tba END GO CREATE TABLE tba ( s VARCHAR(10), c INT, grade INT )GOINSERT INTO tba SELECT '01', 1, 80 UNION SELECT '01', 2, 90 UNION SELECT '02', 1, 80 UNION SELECT '02', 2, 80 UNION SELECT '03', 1, 79 UNION SELECT '03', 2, 80 UNION SELECT '03', 3, 90SELECT * FROM tba AS A WHERE EXISTS (SELECT 1 FROM tba WHERE s = '02' AND c = A.c) AND S NOT IN( SELECT s FROM tba AS A WHERE NOT EXISTS (SELECT 1 FROM tba WHERE s = '02' AND c = A.c))
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([s#] varchar(2),[c#] int,[grade] int) insert [test] select '01',1,80 union all select '01',2,90 union all select '02',1,80 union all select '02',2,80 union all select '03',1,79 union all select '03',2,80 union all select '03',3,90with t as( select COUNT(1)over(partition by [s#]) px,* from test ) select s# from t a where exists( select 1 from t b where a.px=b.px and a.s#<>b.s# ) and a.[c#] in(select [c#] from test where s#='02') and a.s#<>'02'
上位2位哥们,好像你们的答案都有些问题呢 我把数据多添加了几条如下: insert [test] select '01',1,80 union all select '01',2,90 union all select '02',1,80 union all select '02',2,80 union allselect '03',1,79 union all select '03',2,80 union all select '03',3,90 union all select '04',1,90 union all select '04',2,90 union all select '04',3,90 union allselect '05',1,90 则查询结果为02呀,但是你们查出来的结果都是把03,04也查进来了呢?求解哟,本人sql太弱
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
s VARCHAR(10),
c INT,
grade INT
)GOINSERT INTO tba
SELECT '01', 1, 80 UNION
SELECT '01', 2, 90 UNION
SELECT '02', 1, 80 UNION
SELECT '02', 2, 80 UNION
SELECT '03', 1, 79 UNION
SELECT '03', 2, 80 UNION
SELECT '03', 3, 90SELECT * FROM tba AS A
WHERE EXISTS (SELECT 1 FROM tba WHERE s = '02' AND c = A.c) AND S NOT IN(
SELECT s FROM tba AS A
WHERE NOT EXISTS (SELECT 1 FROM tba WHERE s = '02' AND c = A.c))
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([s#] varchar(2),[c#] int,[grade] int)
insert [test]
select '01',1,80 union all
select '01',2,90 union all
select '02',1,80 union all
select '02',2,80 union all
select '03',1,79 union all
select '03',2,80 union all
select '03',3,90with t
as(
select COUNT(1)over(partition by [s#]) px,*
from test
)
select s# from t a
where exists(
select 1 from t b where a.px=b.px and a.s#<>b.s#
)
and a.[c#] in(select [c#] from test where s#='02')
and a.s#<>'02'
我把数据多添加了几条如下:
insert [test]
select '01',1,80 union all
select '01',2,90 union all
select '02',1,80 union all
select '02',2,80 union allselect '03',1,79 union all
select '03',2,80 union all
select '03',3,90 union all
select '04',1,90 union all
select '04',2,90 union all
select '04',3,90 union allselect '05',1,90
则查询结果为02呀,但是你们查出来的结果都是把03,04也查进来了呢?求解哟,本人sql太弱