面试常用SQL整理
我們都知道面試的時候通常都會考數據庫部分的知識，所以在此整理了下我們常用的面試中常用的一些查詢語句SQL，本人測試數據庫為SQL Server2008
首先在數據庫中建立一個測試庫，此處用DavidTest代替
數據表與相應測試數據USE [DavidTest] GO /****** Object: Table [dbo].[Teacher] Script Date: 02/19/2013 01:09:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Teacher]([TId] [varchar](10) NULL,[Tname] [nvarchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[Teacher] ([TId], [Tname]) VALUES (N'01', N'張三') INSERT [dbo].[Teacher] ([TId], [Tname]) VALUES (N'02', N'李四') INSERT [dbo].[Teacher] ([TId], [Tname]) VALUES (N'03', N'王五') /****** Object: Table [dbo].[Student] Script Date: 02/19/2013 01:09:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Student]([SId] [varchar](10) NULL,[Sname] [nvarchar](10) NULL,[Sage] [datetime] NULL,[Ssex] [nvarchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'01', N'趙雷', CAST(0x0000806800000000 AS DateTime), N'男') INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'02', N'錢電', CAST(0x000081CA00000000 AS DateTime), N'男') INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'03', N'孫風', CAST(0x000080F300000000 AS DateTime), N'男') INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'04', N'李云', CAST(0x0000814100000000 AS DateTime), N'男') INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'05', N'周梅', CAST(0x0000832300000000 AS DateTime), N'女') INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'06', N'吳蘭', CAST(0x0000837E00000000 AS DateTime), N'女') INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'07', N'鄭竹', CAST(0x00007FB000000000 AS DateTime), N'女') INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'08', N'王菊', CAST(0x0000807B00000000 AS DateTime), N'女') /****** Object: Table [dbo].[SC] Script Date: 02/19/2013 01:09:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SC]([SId] [varchar](10) NULL,[CId] [varchar](10) NULL,[score] [decimal](18, 1) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'01', N'01', CAST(59.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'01', N'02', CAST(89.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'01', N'03', CAST(99.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'02', N'01', CAST(70.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'02', N'02', CAST(60.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'02', N'03', CAST(80.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'03', N'01', CAST(80.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'03', N'02', CAST(80.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'03', N'03', CAST(80.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'04', N'01', CAST(50.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'04', N'02', CAST(30.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'04', N'03', CAST(20.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'05', N'01', CAST(76.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'05', N'02', CAST(87.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'06', N'01', CAST(31.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'06', N'03', CAST(34.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'07', N'02', CAST(89.0 AS Decimal(18, 1))) INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'07', N'03', CAST(98.0 AS Decimal(18, 1))) /****** Object: Table [dbo].[Course] Script Date: 02/19/2013 01:09:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Course]([CId] [varchar](10) NULL,[Cname] [nvarchar](10) NULL,[TId] [varchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[Course] ([CId], [Cname], [TId]) VALUES (N'01', N'語文', N'02') INSERT [dbo].[Course] ([CId], [Cname], [TId]) VALUES (N'02', N'數學', N'01') INSERT [dbo].[Course] ([CId], [Cname], [TId]) VALUES (N'03', N'英語', N'03')
接下來使我們常用的一些面試查詢SQL
常用面試SQL--1. 查詢"01"課程比"02"課程成績高的學生的信息及課程分數 SELECT dbo.Student.SId, dbo.SC.CId, dbo.Course.Cname,sc.score FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId WHERE dbo.Student.SId IN (SELECT DISTINCT a.SId FROM dbo.SC a INNER JOIN dbo.SC b ON a.SId = b.SId WHERE a.CId='01' AND b.CId='02' AND a.score>b.score) AND dbo.Course.CId IN ('01', '02') ORDER BY dbo.SC.SId--2 查詢同時存在"01"課程和"02"課程的情況 SELECT * FROM dbo.Student WHERE SId IN (SELECT SId FROM dbo.SC WHERE CId IN ('01','02') GROUP BY SId HAVING COUNT(CId)=2)--3. 查詢平均成績大于等于60分的同學的學生編號和學生姓名和平均成績 SELECT SC.SId, dbo.Student.Sname, ROUND(CAST(SUM(SC.score) AS FLOAT)/3,4) avg_score FROM dbo.SC LEFT JOIN dbo.Student ON dbo.SC.SId = dbo.Student.SId GROUP BY SC.SId,Student.Sname HAVING ROUND(CAST(SUM(SC.score) AS FLOAT)/3,4)>=60 ORDER BY dbo.SC.SId--4. 查詢在sc表存在成績的學生信息的SQL語句。 SELECT dbo.Student.SId, dbo.Student.Sname, dbo.Student.Ssex FROM dbo.Student WHERE EXISTS (SELECT 1 FROM sc WHERE dbo.Student.SId = dbo.SC.SId)--5. 查詢在sc表中不存在成績的學生信息的SQL語句。 SELECT dbo.Student.SId, dbo.Student.Sname, dbo.Student.Ssex FROM dbo.Student WHERE NOT EXISTS (SELECT 1 FROM sc WHERE dbo.Student.SId = dbo.SC.SId)--6. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績 SELECT dbo.Student.SId, dbo.Student.Sname, COUNT(dbo.SC.CId) Course_Count, ISNULL(SUM(dbo.SC.score),0) Total_Score FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId GROUP BY dbo.Student.SId, dbo.Student.Sname ORDER BY dbo.Student.SId--7. 查詢所有有成績的學生SQL SELECT * FROM dbo.Student WHERE EXISTS (SELECT 1 FROM dbo.SC WHERE dbo.Student.SId = dbo.SC.SId)--8. 查詢"李"姓老師的數量 SELECT COUNT(1) Count FROM dbo.Teacher WHERE Tname LIKE '李%'--9. 查詢學過"張三"老師授課的同學的信息SELECT dbo.Student.SId, dbo.Student.Sname,dbo.Teacher.Tname FROM dbo.StudentLEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SIdLEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CIdLEFT JOIN dbo.Teacher ON dbo.Course.TId = dbo.Teacher.TIdWHERE dbo.Teacher.Tname = '張三'ORDER BY dbo.Student.SId--10. 查詢沒學過"張三"老師授課的同學的信息 SELECT * FROM dbo.Student WHERE SId NOT IN (SELECT dbo.Student.SId FROM dbo.StudentLEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SIdLEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CIdLEFT JOIN dbo.Teacher ON dbo.Course.TId = dbo.Teacher.TIdWHERE dbo.Teacher.Tname = '張三')--11. 查詢學過編號為"01"并且也學過編號為"02"的課程的同學的信息 SELECT * FROM dbo.Student WHERE EXISTS (SELECT 1 FROM dbo.SC WHERE SC.CId='01' AND dbo.SC.SId=dbo.Student.SId) AND EXISTS (SELECT 1 FROM dbo.SC WHERE dbo.SC.CId='02' AND dbo.SC.SId=dbo.Student.SId)--12. 查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息 SELECT * FROM dbo.Student WHERE EXISTS (SELECT 1 FROM dbo.SC WHERE SC.CId='01' AND dbo.SC.SId=dbo.Student.SId) AND NOT EXISTS (SELECT 1 FROM dbo.SC WHERE dbo.SC.CId='02' AND dbo.SC.SId=dbo.Student.SId) --另一種寫法 SELECT * FROM dbo.Student WHERE dbo.Student.Sid IN (SELECT dbo.SC.Sid FROM dbo.SC WHERE Cid = '01') AND dbo.Student.Sid NOT IN (SELECT dbo.SC.Sid FROM dbo.SC WHERE Cid = '02')--13. 查詢沒有學全所有課程的同學的信息 SELECT * FROM dbo.Student WHERE SId NOT IN ( SELECT SId FROM dbo.Student WHERE EXISTS (SELECT 1 FROM SC WHERE dbo.SC.CId='01' AND dbo.Student.SId=SId) AND EXISTS (SELECT 1 FROM SC WHERE dbo.SC.CId='02' AND dbo.Student.SId=SId) AND EXISTS (SELECT 1 FROM SC WHERE dbo.SC.CId='03' AND dbo.Student.SId=SId)) ORDER BY dbo.Student.SId--另一種寫法 SELECT * FROM dbo.Student WHERE SId IN ( SELECT dbo.Student.SId FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId GROUP BY dbo.Student.SId HAVING COUNT(dbo.SC.CId)<> 3)--14. 查詢至少有一門課與學號為"01"的同學所學相同的同學的信息 SELECT * FROM dbo.Student WHERE SId IN (SELECT SId FROM dbo.SC WHERE CId IN (SELECT cid FROM dbo.SC WHERE SId = '01') AND dbo.Student.SId = dbo.SC.SId)--15. 查詢和"01"號的同學學習的課程完全相同的其他同學的信息 SELECT * FROM dbo.Student WHERE dbo.Student.SId IN ( SELECT dbo.SC.SId FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId GROUP BY dbo.SC.SId HAVING COUNT(dbo.SC.CId) = (SELECT Count(CId) FROM dbo.SC WHERE SId = '01'))--16. 查詢沒學過"張三"老師講授的任一門課程的學生姓名 SELECT * FROM dbo.Student WHERE NOT EXISTS (SELECT 1 FROM SC WHERE dbo.Student.SId = dbo.SC.SId AND CId = (SELECT CId FROM dbo.Course WHERE TId = (SELECT TId FROM dbo.Teacher WHERE Tname='張三'))) --另一種寫法 SELECT * FROM dbo.Student WHERE SId NOT IN (SELECT SId FROM SC WHERE CId = (SELECT CId FROM dbo.Course WHERE TId = (SELECT TId FROM dbo.Teacher WHERE Tname='張三')))--17.查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績 SELECT dbo.Student.SId,dbo.Student.Sname,AVG(dbo.SC.score) AVG_Score FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId WHERE Student.SId IN ( SELECT SId FROM dbo.SC WHERE score<60 GROUP BY SId HAVING COUNT(CId)>=2) GROUP BY dbo.Student.SId,dbo.Student.Sname ORDER BY dbo.Student.SID--16、檢索"01"課程分數小于60，按分數降序排列的學生信息 SELECT dbo.Student.*,dbo.SC.score FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId AND CId='01' WHERE Student.SId IN ( SELECT SId FROM dbo.SC WHERE CId='01' AND score<60) ORDER BY dbo.Student.SId DESC--另一種 SELECT dbo.Student.SId,dbo.Student.Sname,dbo.SC.score FROM dbo.SC LEFT JOIN dbo.Student ON dbo.SC.SId = dbo.Student.SId WHERE CId='01' AND score<60 ORDER BY dbo.Student.SId DESC--17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績 SELECT avg_tab.SId,dbo.Student.Sname,dbo.Course.CId,dbo.Course.Cname,dbo.SC.score,avg_tab.Avg_Score FROM ( SELECT dbo.SC.SId, AVG(dbo.SC.score) Avg_Score FROM dbo.SC GROUP BY dbo.SC.SId) avg_tab LEFT JOIN dbo.SC ON avg_tab.SId = dbo.SC.SId LEFT JOIN dbo.Student ON avg_tab.SId = dbo.Student.SId LEFT JOIN dbo.Course ON dbo.SC.CId=dbo.Course.CId ORDER BY avg_tab.Avg_Score DESC--另一種方法 select a.Sid 學生編號, a.Sname 學生姓名,max(case c.Cname when N'語文' then b.score else null end) [語文],--max(case WHEN c.Cname LIKE N'語文' then b.score else null end) [語文], 此種寫法也OKmax(case c.Cname when N'數學' then b.score else null end) [數學],max(case c.Cname when N'英語' then b.score else null end) [英語],cast(avg(b.score) as decimal(18,2)) 平均分 from Student a left join SC b on a.Sid = b.Sid left join Course c on b.Cid = c.Cid group by a.Sid , a.Sname order by 平均分 DESC--18、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率 --及格為>=60，中等為：-80，優良為：-90，優秀為：>=90 (SELECT dbo.SC.CId,dbo.Course.Cname, MAX(CASE dbo.Course.Cname WHEN N'語文' THEN dbo.SC.score ELSE NULL END) 最高分, MIN(CASE dbo.Course.Cname WHEN N'語文' THEN dbo.SC.score ELSE NULL END) 最低分, Avg(CASE dbo.Course.Cname WHEN N'語文' THEN dbo.SC.score ELSE NULL END) 平均分 FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId WHERE dbo.Course.CId='01' GROUP BY sc.CId, dbo.Course.Cname) UNION ( SELECT dbo.SC.CId,dbo.Course.Cname, MAX(CASE dbo.Course.Cname WHEN N'數學' THEN dbo.SC.score ELSE NULL END) 最高分, MIN(CASE dbo.Course.Cname WHEN N'數學' THEN dbo.SC.score ELSE NULL END) 最低分, Avg(CASE dbo.Course.Cname WHEN N'數學' THEN dbo.SC.score ELSE NULL END) 平均分 FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId WHERE dbo.Course.CId='02' GROUP BY sc.CId, dbo.Course.Cname ) UNION ( SELECT dbo.SC.CId,dbo.Course.Cname, MAX(CASE dbo.Course.Cname WHEN N'英語' THEN dbo.SC.score ELSE NULL END) 最高分, MIN(CASE dbo.Course.Cname WHEN N'英語' THEN dbo.SC.score ELSE NULL END) 最低分, Avg(CASE dbo.Course.Cname WHEN N'英語' THEN dbo.SC.score ELSE NULL END) 平均分 FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId WHERE dbo.Course.CId='03' GROUP BY sc.CId, dbo.Course.Cname ) --另一種方法 select m.Cid [課程編號], m.Cname [課程名稱], max(n.score) [最高分],min(n.score) [最低分],cast(avg(n.score) as decimal(18,2)) [平均分],cast((select count(1) from SC where Cid = m.Cid and score >= 60)*100.0 / (select count(1) from SC where Cid = m.Cid) as decimal(18,2)) [及格率(%)],cast((select count(1) from SC where Cid = m.Cid and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where Cid = m.Cid) as decimal(18,2)) [中等率(%)],cast((select count(1) from SC where Cid = m.Cid and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where Cid = m.Cid) as decimal(18,2)) [優良率(%)],cast((select count(1) from SC where Cid = m.Cid and score >= 90)*100.0 / (select count(1) from SC where Cid = m.Cid) as decimal(18,2)) [優秀率(%)] from Course m, SC n where m.Cid = n.Cid group by m.Cid, m.Cname order by m.Cid--19. 查詢不同老師所教不同課程平均分從高到低顯示 SELECT dbo.SC.CId CourseId, dbo.Course.Cname CourseName, dbo.Teacher.Tname TeacherName, AVG(dbo.SC.score) Avg_Score FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId LEFT JOIN dbo.Teacher ON dbo.Course.TId = dbo.Teacher.TId GROUP BY dbo.SC.CId,dbo.Course.Cname,dbo.Teacher.Tname ORDER BY AVG(dbo.SC.score) DESC--20.查詢如下課程平均成績第3 名到第6 名的學生成績單 SELECT SCInfo.RowNumber,SCInfo.SId,SCInfo.Sname,SCInfo.Ssex,SCInfo.Avg_Score FROM (SELECT ROW_NUMBER() OVER (ORDER BY AVG(dbo.SC.score)) RowNumber, dbo.SC.SId, dbo.Student.Sname,dbo.Student.Ssex, AVG(dbo.SC.score) Avg_Score FROM dbo.SCLEFT JOIN dbo.Student ON dbo.SC.SId = dbo.Student.SIdGROUP BY dbo.SC.SId,dbo.Student.SId,dbo.Student.Sname,dbo.Student.Ssex) SCInfo WHERE SCInfo.RowNumber >=3 AND SCInfo.RowNumber<=6--21. 統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60] (橫向顯示-列轉行) SELECT dbo.SC.CId, dbo.Course.Cname, SUM(CASE WHEN dbo.SC.score >=85 AND dbo.SC.score<=100 THEN 1 ELSE 0 END) '分數段85-100', SUM(CASE WHEN dbo.SC.score >=70 AND dbo.SC.score<85 THEN 1 ELSE 0 END) '分數段70-85', SUM(CASE WHEN dbo.SC.score >=60 AND dbo.SC.score<70 THEN 1 ELSE 0 END) '分數段60-70', SUM(CASE WHEN dbo.SC.score <60 THEN 1 ELSE 0 END) '分數段<60' FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId GROUP BY dbo.SC.CId,dbo.Course.Cname--21. 統計列印各科成績,各分數段人數:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60] (橫向顯示-行轉列) SELECT dbo.SC.CId, dbo.Course.Cname, 分數段= (CASE WHEN dbo.SC.score >=85 AND dbo.SC.score<=100 THEN '85-100'WHEN dbo.SC.score >=70 AND dbo.SC.score<85 THEN '70-85'WHEN dbo.SC.score >=60 AND dbo.SC.score<70 THEN '60-70'WHEN dbo.SC.score <60 THEN '<60'END ), COUNT(1) Partial_Count FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId GROUP BY dbo.SC.CId,dbo.Course.Cname, (CASE WHEN dbo.SC.score >=85 AND dbo.SC.score<=100 THEN '85-100'WHEN dbo.SC.score >=70 AND dbo.SC.score<85 THEN '70-85'WHEN dbo.SC.score >=60 AND dbo.SC.score<70 THEN '60-70'WHEN dbo.SC.score <60 THEN '<60'END ) ORDER BY dbo.SC.CId,dbo.Course.Cname--22. 查詢學生平均成績及其名次 SELECT Student_RankInfo.* FROM (SELECT dbo.SC.SId, dbo.Student.Sname,dbo.Student.Ssex,ROW_NUMBER() OVER (ORDER BY AVG(dbo.SC.score)) RankNumber, AVG(dbo.SC.score) Avg_Score FROM dbo.SCLEFT JOIN dbo.Student ON dbo.SC.SId = dbo.Student.SIdGROUP BY dbo.SC.SId,dbo.Student.Sname,dbo.Student.Ssex ) Student_RankInfo ORDER BY Student_RankInfo.SId--23. 查詢各科成績前三名的記錄考慮成績并列情況 --23.1 分數重復時保留名次空缺Rank實現，PARTITION BY分區分組,Group By整體分組一般與Sum,Avg,等統計函數一起使用 SELECT ScoreRankInfo.* FROM (SELECT dbo.SC.SId,dbo.Student.Sname,dbo.SC.CId,dbo.SC.score, RankNumber = RANK() OVER (PARTITION BY dbo.SC.CId ORDER BY dbo.SC.score DESC) FROM dbo.SCLEFT JOIN dbo.Student ON dbo.SC.SId = dbo.Student.SId ) ScoreRankInfo--23.2 分數重復不保留名次空缺DENSE_RANK實現 SELECT ScoreRankInfo.* FROM (SELECT dbo.SC.SId,dbo.Student.Sname,dbo.SC.CId,dbo.SC.score, RankNumber = DENSE_RANK() OVER (PARTITION BY dbo.SC.CId ORDER BY dbo.SC.score DESC) FROM dbo.SCLEFT JOIN dbo.Student ON dbo.SC.SId = dbo.Student.SId ) ScoreRankInfo--23.3 rownumber實現不區分分數相同時的rank SELECT ScoreRankInfo.* FROM (SELECT dbo.SC.SId,dbo.Student.Sname,dbo.SC.CId,dbo.SC.score, RankNumber = ROW_NUMBER() OVER (PARTITION BY dbo.SC.CId ORDER BY dbo.SC.score DESC) FROM dbo.SCLEFT JOIN dbo.Student ON dbo.SC.SId = dbo.Student.SId ) ScoreRankInfo--24. 查詢每門課程被選修的學生數 SELECT dbo.SC.CId, dbo.Course.Cname, COUNT(dbo.SC.SId) PersonCount FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId GROUP BY dbo.SC.CId,dbo.Course.Cname--分區分組 SELECT dbo.SC.CId, dbo.Course.Cname, dbo.SC.SId, COUNT(dbo.SC.SId) OVER(PARTITION BY dbo.SC.SId) PersonCount FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId ORDER BY dbo.SC.CId--25. 查詢出只選修了兩門課程的全部學生的學號和姓名 SELECT dbo.Student.SId,dbo.Student.Sname,dbo.Student.Ssex FROM dbo.Student WHERE dbo.Student.SId IN (SELECT dbo.SC.SId FROM dbo.SC GROUP BY SId HAVING COUNT(dbo.SC.CId)=2) ORDER BY dbo.Student.SId--26. 查詢男生、女生人數 SELECT dbo.Student.Ssex, COUNT(dbo.Student.SId) StudentCount FROM dbo.Student GROUP BY dbo.Student.Ssex--列轉行顯示 SELECT SUM((CASE dbo.Student.Ssex WHEN '男' THEN 1 ELSE 0 END)) 男生人數, SUM((CASE dbo.Student.Ssex WHEN '女' THEN 1 ELSE 0 END)) 女生人數 FROM dbo.Student--27. 查詢姓“張”的學生名單 SELECT * FROM dbo.Student WHERE dbo.Student.Sname LIKE '錢%'--28. 查詢課程名稱為"數學"，且分數低于60的學生姓名和分數 SELECT dbo.Student.SId,dbo.Student.Sname,dbo.Course.CId,dbo.Course.Cname,dbo.SC.score FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId WHERE dbo.Course.Cname='數學' AND dbo.SC.score<60--30. 查詢所有學生的選課情況及各個課程排名情況 SELECT dbo.Student.*,dbo.Course.CId,dbo.Course.Cname, dbo.SC.score, RANK() OVER(PARTITION BY dbo.Student.SId ORDER BY dbo.SC.score DESC) Rank FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId ORDER BY dbo.Student.SId--31. 查詢任何一門課程成績在70分以上的姓名、課程名稱和分數 SELECT dbo.Student.SId,dbo.Student.Sname,dbo.Course.Cname,dbo.SC.score FROM dbo.Student LEFT JOIN dbo.SC ON dbo.Student.SId = dbo.SC.SId LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId WHERE EXISTS (SELECT 1 FROM dbo.SC WHERE score>=70 AND SId=dbo.Student.SId)--32. 查詢每位學生成績前2名的課程及分數 SELECT DISTINCT tempTable.SId,dbo.Student.Sname,tempTable.CId,dbo.Course.Cname,tempTable.score FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY dbo.SC.SId ORDER BY dbo.SC.score DESC) RowNumber, dbo.SC.SId,dbo.SC.CId,sc.score FROM dbo.SC ) tempTable LEFT JOIN dbo.Student ON tempTable.SId = dbo.Student.SId LEFT JOIN dbo.Course ON tempTable.CId = dbo.Course.CId WHERE RowNumber BETWEEN 1 AND 2--33. 查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績 SELECT TOP 1 dbo.SC.SId,dbo.Student.Sname, dbo.SC.score FROM dbo.SC LEFT JOIN dbo.Course ON dbo.SC.CId = dbo.Course.CId LEFT JOIN dbo.Teacher ON dbo.Course.TId = dbo.Teacher.TId LEFT JOIN dbo.Student ON dbo.Student.SId = dbo.SC.SId WHERE dbo.Teacher.Tname='張三' ORDER BY dbo.SC.score DESC
在整理和鞏固這些東西的過程中也重新學到了以前一些不大注意的地方和理解有誤的地方：
1. 以前沒有關注過SQL的具體執行順序，后來在博客園里面也找到一片別人整理的比較好的文章，http://blog.csdn.net/gyc1105/article/details/8074545
2. 以前沒仔細研究查看過的over partition by與group by的區別，http://blog.csdn.net/gyc1105/article/details/8074545
整理學習的越多越能發現自己還有許多地方需要補充~ 在此先共享下，希望對園子內的朋友有幫助嘍~
轉載于:https://www.cnblogs.com/daviddai/archive/2013/02/19/2916430.html
總結
- 上一篇： 32位汇编寄存器及汇编指令
- 下一篇： 和田玉足金值多少钱？