SELECT Name FROM TB T WHERE EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='语文' AND Score>30) AND EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='数学' AND Score>40) AND EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='物理' AND Score>50) AND EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='英语' AND Score>60)
报错了啊 select name from sheep t where EXIST (SELECT 1 FROM sheep WHERE name=t.name AND subject='语文' AND score>30) 服务器: 消息 156,级别 15,状态 1,行 1 在关键字 'SELECT' 附近有语法错误。 服务器: 消息 170,级别 15,状态 1,行 1 第 1 行: ')' 附近有语法错误。
exist改成exists,不错不错,但是结果有重复的了,能不能再改进一下呢
这里你现在查语文数学都大于30的,但不查英语,假设以后也要查英语了,那已经写好的sql可能就要改动了。 我的思路是可以动态扩充所要查询的科目,但是不改动sql语句。 这里用一个配置表配置所要的科目CREATE TABLE subject ( subject VARCHAR(20) )假设你原有的这个表是score表 使用的查询语句为SELECT DISTINCT a.name FROM dbo.score a INNER JOIN dbo.subject b ON a.subject = b.subject WHERE a.score>30 GROUP BY a.name HAVING COUNT(a.name)=(SELECT COUNT(*) FROM dbo.subject)思路就是一句:筛选出在你所要的学科中,满足分数条件的学棵数与你查询的学科数量相同的人。你在subject表中加入语文、数学,则查的都是语文数学都大于30分的, 再加入英语,就是3各科目都大于30分的了。
这个思路非常精彩,效率也是最高的,用exists的话,对于已经查找成功的姓名,还会继续查找,效率不让人满意,无奈我sql语法不过关,select * from (select name from sheep where subject='语文' and score>=30 or subject='数学' and score>=30) as tempTable where count(name)=2麻烦帮我修正一下,在count那个地方报错了聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。谢谢!
count(name)=2这个不能放在where里,这里做聚合得用group by 和having,select name from sheep where subject in ('语文','数学' ) and score>=30 group by name having count(name)=2另外LZ你的另外一个帖子要求的调节要比这贴强一些,我也加强了一下我的查询,但是LZ结贴太快了,呵呵。
from tb j
where not exists(select * from tb where j.name=name and score<=30)
name in(select name from @a where subject='数学' and score>29)
2.SELECT a.NAME FROM @a a ,@a b WHERE a.name=b.name and a.[SUBJECT]='语文' AND a.score>29 AND b.subject='数学' AND b.score>30
select
name
from
tb t
where
not exists(select * from tb where name=t.name and score<=30)
name subject score
王 语文 30
李 数学 25
黄 语文 20
王 数学 44
李 语文 50
王 英语 20
可能不单是只有语文和数学,还有英语,但是英语成绩不作为查询条件
name
from
tb t
where
not exists(select * from tb where name=t.name and score<=30)
select distinct name
from tb j
where not exists(select * from tb where ([subject]='语文' or [subject]='数学') and j.name=name and score<=30)
呵呵。。
SELECT Name
FROM TB T
WHERE EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='语文' AND Score>30)
AND EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='数学' AND Score>40)
AND EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='物理' AND Score>50)
AND EXIST (SELECT 1 FROM TB WHERE name=T.name AND [subject]='英语' AND Score>60)
select name from sheep t where EXIST (SELECT 1 FROM sheep WHERE name=t.name AND subject='语文' AND score>30)
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'SELECT' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ')' 附近有语法错误。
我的思路是可以动态扩充所要查询的科目,但是不改动sql语句。
这里用一个配置表配置所要的科目CREATE TABLE subject
(
subject VARCHAR(20)
)假设你原有的这个表是score表
使用的查询语句为SELECT DISTINCT a.name
FROM dbo.score a
INNER JOIN dbo.subject b
ON a.subject = b.subject
WHERE a.score>30
GROUP BY a.name
HAVING COUNT(a.name)=(SELECT COUNT(*) FROM dbo.subject)思路就是一句:筛选出在你所要的学科中,满足分数条件的学棵数与你查询的学科数量相同的人。你在subject表中加入语文、数学,则查的都是语文数学都大于30分的,
再加入英语,就是3各科目都大于30分的了。
from sheep
where subject in ('语文','数学' ) and score>=30
group by name
having count(name)=2另外LZ你的另外一个帖子要求的调节要比这贴强一些,我也加强了一下我的查询,但是LZ结贴太快了,呵呵。