--> 生成测试数据: @tb DECLARE @tb TABLE (name varchar(4),kecheng varchar(4),fenshu int) INSERT INTO @tb SELECT '张三','语文',81 UNION ALL SELECT '张三','数学',75 UNION ALL SELECT '李四','语文',76 UNION ALL SELECT '李四','数学',90 UNION ALL SELECT '王五','语文',81 UNION ALL SELECT '王五','数学',100--SQL查询如下:SELECT * FROM @tb AS A WHERE NOT EXISTS(SELECT * FROM @tb WHERE name = A.name AND fenshu <= 80); /* name kecheng fenshu ---- ------- ----------- 王五 语文 81 王五 数学 100(2 行受影响)*/
select * from 表 t where not exists( select 1 from 表 where name=t.name and fenshu<80 )
DECLARE @tb TABLE (name varchar(4),kecheng varchar(4),fenshu int) INSERT INTO @tb SELECT '张三','语文',81 UNION ALL SELECT '张三','数学',75 UNION ALL SELECT '李四','语文',76 UNION ALL SELECT '李四','数学',90 UNION ALL SELECT '王五','语文',81 UNION ALL SELECT '王五','数学',100select * from @tb A where (select count(1) from @tb where name = A.name and fenshu<=80)=0 /* name kecheng fenshu ---- ------- ----------- 王五 语文 81 王五 数学 100(2 行受影响) */
select * from ta where name in(select name from ta group by name having sum(case when [fenshu]>80 then 1 else 0 end ) = 2)
select * from table where not exists(select * from table where name=table.name and fenshu<=80 )呵呵 可以用了哦!
select * from ta a where not exists(select 1 from ta where name = a.name and fenshu<=80)
select * from table where not exists(select * from table where name=table.name and fenshu <=80 ) 应该可以
WHERE NOT EXISTS(SELECT * FROM tb
WHERE name = A.name AND fenshu <= 80);
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-19 10:34:49
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (name varchar(4),kecheng varchar(4),fenshu int)
INSERT INTO @tb
SELECT '张三','语文',81 UNION ALL
SELECT '张三','数学',75 UNION ALL
SELECT '李四','语文',76 UNION ALL
SELECT '李四','数学',90 UNION ALL
SELECT '王五','语文',81 UNION ALL
SELECT '王五','数学',100--SQL查询如下:SELECT * FROM @tb AS A
WHERE NOT EXISTS(SELECT * FROM @tb
WHERE name = A.name AND fenshu <= 80);
/*
name kecheng fenshu
---- ------- -----------
王五 语文 81
王五 数学 100(2 行受影响)*/
select 1 from 表 where name=t.name and fenshu<80
)
INSERT INTO @tb
SELECT '张三','语文',81 UNION ALL
SELECT '张三','数学',75 UNION ALL
SELECT '李四','语文',76 UNION ALL
SELECT '李四','数学',90 UNION ALL
SELECT '王五','语文',81 UNION ALL
SELECT '王五','数学',100select * from @tb A where (select count(1) from @tb where name = A.name and fenshu<=80)=0
/*
name kecheng fenshu
---- ------- -----------
王五 语文 81
王五 数学 100(2 行受影响)
*/
from ta
where name in(select name from ta group by name having sum(case when [fenshu]>80 then 1 else 0 end ) = 2)
from ta a
where not exists(select 1 from ta where name = a.name and fenshu<=80)
应该可以