/*
要求:用一條SQL語句查出所有課程均為85以上的姓名XM 姓名 KeCheng 課程 FenShu 分數
-------- -------------------- -----------
張三 語文 80
張三 數學 91
李四 英語 90
李四 語文 88
李四 數學 100
王五 數學 79
王五 語文 60其正確結果:李四
*/--測試環境
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 --查詢:一條SQL語句哦
要求:用一條SQL語句查出所有課程均為85以上的姓名XM 姓名 KeCheng 課程 FenShu 分數
-------- -------------------- -----------
張三 語文 80
張三 數學 91
李四 英語 90
李四 語文 88
李四 數學 100
王五 數學 79
王五 語文 60其正確結果:李四
*/--測試環境
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 --查詢:一條SQL語句哦
insert into tb
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 select distinct xm from tb where xm not in
(select distinct xm from tb where FenShu < 85)drop table tb/*
xm
--------
李四(所影响的行数为 1 行)
*/
insert into tb
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 select xm from tb group by xm having min(FenShu)>=85drop table tb/*
xm
--------
李四(所影响的行数为 1 行)
*/
select distinct XM from (
select XM,KeCheng,min(FenShu) as FenShu from @tbl
group by XM,KeCheng
having min(FenShu)>=85
) a
要求:用一條SQL語句查出所有課程均為85以上的姓名 XM 姓名 KeCheng 課程 FenShu 分數
-------- -------------------- -----------
張三 語文 80
張三 數學 91
李四 英語 90
李四 語文 88
李四 數學 100
王五 數學 79
王五 語文 60 其正確結果:李四
*/ --測試環境
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 --查詢:一條SQL語句哦select xm from @tbl group by xm having min(fenshu)>=85
/*
xm
--------
李四
*/
/*
要求:用一條SQL語句查出所有課程均為85以上的姓名,若缺考則無記錄XM KeCheng FenShu
-------- -------------------- -----------
張三 語文 88
張三 數學 91
李四 英語 90
李四 語文 88
李四 數學 100
王五 數學 79
王五 語文 60其正確結果:李四
*/--測試環境
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',88 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 --查詢:一條SQL語句哦
select distinct xm from @tbl where xm not in
(select distinct xm from @tbl where FenShu < 85)
--張三 英語缺考的情況未考慮select distinct XM from (
select XM,KeCheng,min(FenShu) as FenShu from @tbl
group by XM,KeCheng
having min(FenShu)>=85
) a
--此句同上:張三 英語缺考的情況未考慮
INSERT INTO TB
SELECT '張三','語文',85 UNION ALL
SELECT '張三','數學',91 UNION ALL
SELECT '李四','英語',90 UNION ALL
SELECT '李四','語文',88 UNION ALL
SELECT '李四','數學',100 UNION ALL
SELECT '王五','數學',79 UNION ALL
SELECT '王五','語文',60
--3門課程全部都在85分以上的
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=3
/*
XM
--------
李四
*/--至少2門課程全部都在85分以上的
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)>=2
/*
XM
--------
李四
張三
*/
DROP TABLE TB
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=3
不知道就用:
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)>=2
問題是多少門課程不清楚,即由下面決定
select count(distinct(KeCheng)) from @tbl
(select distinct xm from tb where FenShu < 85)
都是OK的
insert into tb
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 select xm from tb where xm in
(
select xm from tb group by xm having min(FenShu)>=85
)
group by xm having(count(*)) = (select count(distinct KeCheng) from tb)drop table tb/*
xm
--------
李四(所影响的行数为 1 行)
*/
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=(SELECT TOP 1 COUNT(DISTINCT KECHENG) FROM TB)
select @i=count(distinct(KeCheng)) from @tblSELECT XM
FROM @tbl
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=@i
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60
union all select 'ss','數學','90'select XM from @tbl Group By XM Having(min(FenShu)>85 and count(1)=(select count(distinct KeCheng) from @tbl))
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',88 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 SELECT * FROM @tbl a
WHERE NOT EXISTS
(
SELECT KeCheng FROM
(
SELECT DISTINCT KeCheng FROM @tbl
)b
WHERE NOT EXISTS
(
SELECT * FROM @tbl c
WHERE a.XM=c.XM
AND b.KeCheng=c.KeCheng
AND c.FenShu>=85
)
)/*
XM KeCheng FenShu
-------- -------------------- -----------
李四 英語 90
李四 語文 88
李四 數學 100(3 行受影响)
*/
rockyvan 答案正確。結帖了。謝謝大家!
where xm=a.xm and FenShu<85) group by xm
/*
xm
--------
李四
*/
CREATE TABLE TB(XM [VARCHAR](8),KECHENG [VARCHAR](20),FENSHU [INT])
INSERT INTO TB
SELECT '張三','語文',85 UNION ALL
SELECT '張三','數學',91 UNION ALL
SELECT '李四','英語',90 UNION ALL
SELECT '李四','語文',88 UNION ALL
SELECT '李四','數學',100 UNION ALL
SELECT '王五','數學',79 UNION ALL
SELECT '王五','語文',60
--A、所有課程全部都在85分以上的姓名
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=(SELECT TOP 1 COUNT(DISTINCT KECHENG) FROM TB)
/*
XM
--------
李四
*/--B、3門課程全部都在85分以上的姓名
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=3
/*
XM
--------
李四
*/--C、至少2門課程全部都在85分以上的姓名
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)>=2
/*
XM
--------
李四
張三
*/
DROP TABLE TB
SELECT XM
FROM TB
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=(SELECT TOP 1 COUNT(DISTINCT KECHENG) FROM TB)
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60
select * from @tbl
select XM from @tbl
where FenShu>85
group by XM having(count(XM)>2)
要求:用一條SQL語句查出所有課程均為85以上的姓名,若缺考則無記錄XM KeCheng FenShu
-------- -------------------- -----------
張三 語文 88
張三 數學 91
李四 英語 90
李四 語文 88
李四 數學 100
王五 數學 79
王五 語文 60其正確結果:李四
*/--測試環境
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',88 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60 --查詢:一條SQL語句哦
--方法一:
SELECT XM
FROM @tbl
WHERE FENSHU>=85
GROUP BY XM
HAVING COUNT(XM)=(SELECT TOP 1 COUNT(DISTINCT KECHENG) FROM @tbl)
--方法二:
select xm from @tbl where xm in
(
select xm from @tbl group by xm having min(FenShu)>=85
)
group by xm having(count(xm)) = (select count(distinct KeCheng) from @tbl)--方法三:
SELECT distinct xm FROM @tbl a
WHERE NOT EXISTS
(
SELECT KeCheng FROM
(
SELECT DISTINCT KeCheng FROM @tbl
)b
WHERE NOT EXISTS
(
SELECT * FROM @tbl c
WHERE a.XM=c.XM
AND b.KeCheng=c.KeCheng
AND c.FenShu>=85
)
)/*結果:
XM
--------
李四
*/
create TABLE tbl(XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into tbl
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60
With trg_w
as
(select XM AS XM,MAX(case when KeCheng='語文' then FenShu else 0 end) 语文,
MAX(case when KeCheng='數學' then FenShu else 0 end) 数学,
MAX(case when KeCheng='英語' then FenShu else 0 end) 英语
from tbl where FenShu>85
group by XM)
select * from trg_w where 语文>85 and 数学>85 and 英语>85
(select XM from tb where FenShu<85)
/*
要求:用一條SQL語句查出所有課程均為85以上的姓名 XM 姓名 KeCheng 課程 FenShu 分數
-------- -------------------- -----------
張三 語文 80
張三 數學 91
李四 英語 90
李四 語文 88
李四 數學 100
王五 數學 79
王五 語文 60 其正確結果:李四
*/ --測試環境
declare @tbl TABLE (XM [varchar](8),KeCheng [varchar](20),FenShu [int])
insert into @tbl
select '張三','語文',80 union all select
'張三','數學',91 union all select
'李四','英語',90 union all select
'李四','語文',88 union all select
'李四','數學',100 union all select
'王五','數學',79 union all select
'王五','語文',60
select A.XM from (select XM,count(*) count from @tbl where FenShu>=85 group by XM ) A
join
(select XM,count(*) count from @tbl group by XM ) B on A.XM=B.XM AND A.count=B.count
--查詢:一條SQL語句哦