设tb表如下:
name subject grademike math 79
lee chinese 81
lee math 78
tom chinese 85
tom math 81
tom english 88
。查询所有科目分数都在80分(含)以上的学生姓名哎,不会做....
特来请教~~~
先谢谢了~
name subject grademike math 79
lee chinese 81
lee math 78
tom chinese 85
tom math 81
tom english 88
。查询所有科目分数都在80分(含)以上的学生姓名哎,不会做....
特来请教~~~
先谢谢了~
from tb k
where not exists(select * from tb where k.name=name and grade<80)
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( name varchar(10),subject varchar(10) ,grade int )
go
insert tb SELECT
'mike', 'math' ,79 UNION ALL SELECT
'lee' , 'chinese' ,81 UNION ALL SELECT
'lee' , 'math' ,78 UNION ALL SELECT
'tom' , 'chinese' ,85 UNION ALL SELECT
'tom' ,'math' ,81 UNION ALL SELECT
'tom' , 'english' ,88
go
select distinct name
from tb k
where not exists(select * from tb where k.name=name and grade<80)go
name
----------
tom
HAVING COUNT(*) =(SELECT COUNT(DISTINCT SUBJECT) FROM TB )
DROP TABLE tb
GO
CREATE TABLE tb( name varchar(10),subject varchar(10) ,grade int )
go
insert tb SELECT
'mike', 'math' ,79 UNION ALL SELECT
'lee' , 'chinese' ,81 UNION ALL SELECT
'lee' , 'math' ,78 UNION ALL SELECT
'tom' , 'chinese' ,85 UNION ALL SELECT
'tom' ,'math' ,81 UNION ALL SELECT
'tom' , 'english' ,88
goSELECT NAME FROM TB WHERE grade >=80 GROUP BY NAME
HAVING COUNT(*) =(SELECT COUNT(DISTINCT SUBJECT) FROM TB )(所影响的行数为 6 行)NAME
----------
tom(所影响的行数为 1 行)
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( name varchar(10),subject varchar(10) ,grade int )
go
insert tb SELECT
'mike', 'math' ,79 UNION ALL SELECT
'lee' , 'chinese' ,81 UNION ALL SELECT
'lee' , 'math' ,78 UNION ALL SELECT
'tom' , 'chinese' ,85 UNION ALL SELECT
'tom' ,'math' ,81 UNION ALL SELECT
'tom' , 'english' ,88
go
SELECT DISTINCT NAME FROM TB T
WHERE NAME NOT IN(SELECT NAME FROM TB WHERE GRADE<80)NAME
----------
tom(1 行受影响)
--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步,却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-08-17 20:34:20=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(4),subject varchar(7),grade int)
insert into #T
select 'mike','math',79 union all
select 'lee','chinese',81 union all
select 'lee','math',78 union all
select 'tom','chinese',85 union all
select 'tom','math',81 union all
select 'tom','english',88
----------------查询------------
select name,min(grade) 最低分 from #T group by name having min(grade)>80
----------------结果--------------
/*
name 最低分
tom 81
*/
/*如果科目是固定的话(当然,你这个看起来就只有三科,貌似小学成绩软件)
命题可以理解为计算成绩大于80分的科目数量为最大数,即3,由此可得*/
SELECT [Name] FROM [tb]
WHERE [Grade] >= 80
GROUP BY [Name] HAVING COUNT(*) = 3
ORDER BY [Name];
GO
你可以试试你例子里
select 'mike','math',81 union all
你选择的是考试科目最低分也高于80的,
但是只有2科成绩或者1科成绩,也就是说缺考的都会被考虑,
这样应该违反搂主所有科目(个人理解是全科80以上的优等生)大于80……