34,590
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[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,90
with 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'
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
)
GO
INSERT 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, 90
SELECT * 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))