日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務時間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
.NET高級工程師面試題之SQL篇

1 題目

這確實是一個真實的面試題，琢磨一下吧！知識不用，就會丟掉，我太依賴各種框架和dll了，已經(jīng)忘記了最基本的東西。有多久沒有寫過SQL了，我已經(jīng)不記得了。

已知表信息如下：

Department(depID, depName),depID 系編號，DepName系名

Student(stuID, name, depID) 學生編號，姓名，系編號

Score(stuID, category, score) 學生編碼，科目，成績

找出每一個系的最高分，并且按系編號，學生編號升序排列，要求順序輸出以下信息：

系編號，系名，學生編號，姓名，總分

2 實驗

 
 
    
  
  
  1. USE [test] 
    2. 
  
  
  2. GO 
    3. 
  
  
  3. /****** Object:  Table [dbo].[Score]    Script Date: 05/11/2015 23:16:23 ******/ 
    4. 
  
  
  4. SET ANSI_NULLS ON 
    5. 
  
  
  5. GO 
    6. 
  
  
  6. SET QUOTED_IDENTIFIER ON 
    7. 
  
  
  7. GO 
    8. 
  
  
  8. SET ANSI_PADDING ON 
    9. 
  
  
  9. GO 
    10. 
  
  
  10. CREATE TABLE [dbo].[Score]( 
    11. 
  
  
  11.     [stuID] [int] NOT NULL, 
    12. 
  
  
  12.     [category] [varchar](50) NOT NULL, 
    13. 
  
  
  13.     [score] [int] NOT NULL 
    14. 
  
  
  14. ) ON [PRIMARY] 
    15. 
  
  
  15. GO 
    16. 
  
  
  16. SET ANSI_PADDING OFF 
    17. 
  
  
  17. GO 
    18. 
  
  
  18. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (1, N'英語', 80) 
    19. 
  
  
  19. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'數(shù)學', 80) 
    20. 
  
  
  20. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (1, N'數(shù)學', 70) 
    21. 
  
  
  21. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'英語', 89) 
    22. 
  
  
  22. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'英語', 81) 
    23. 
  
  
  23. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'數(shù)學', 71) 
    24. 
  
  
  24. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'數(shù)學', 91) 
    25. 
  
  
  25. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'英語', 61) 
    26. 
  
  
  26. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'英語', 91) 
    27. 
  
  
  27. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'英語', 89) 
    28. 
  
  
  28. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'英語', 77) 
    29. 
  
  
  29. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'英語', 97) 
    30. 
  
  
  30. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'英語', 57) 
    31. 
  
  
  31. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'數(shù)學', 87) 
    32. 
  
  
  32. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'數(shù)學', 89) 
    33. 
  
  
  33. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'數(shù)學', 80) 
    34. 
  
  
  34. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'數(shù)學', 81) 
    35. 
  
  
  35. INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'數(shù)學', 84) 
    36. 
  
  
  36. /****** Object:  Table [dbo].[Department]    Script Date: 05/11/2015 23:16:23 ******/ 
    37. 
  
  
  37. SET ANSI_NULLS ON 
    38. 
  
  
  38. GO 
    39. 
  
  
  39. SET QUOTED_IDENTIFIER ON 
    40. 
  
  
  40. GO 
    41. 
  
  
  41. SET ANSI_PADDING ON 
    42. 
  
  
  42. GO 
    43. 
  
  
  43. CREATE TABLE [dbo].[Department]( 
    44. 
  
  
  44.     [depID] [int] IDENTITY(1,1) NOT NULL, 
    45. 
  
  
  45.     [depName] [varchar](50) NOT NULL, 
    46. 
  
  
  46. PRIMARY KEY CLUSTERED  
    47. 
  
  
  47. ( 
    48. 
  
  
  48.     [depID] ASC 
    49. 
  
  
  49. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
    50. 
  
  
  50. ) ON [PRIMARY] 
    51. 
  
  
  51. GO 
    52. 
  
  
  52. SET ANSI_PADDING OFF 
    53. 
  
  
  53. GO 
    54. 
  
  
  54. SET IDENTITY_INSERT [dbo].[Department] ON 
    55. 
  
  
  55. INSERT [dbo].[Department] ([depID], [depName]) VALUES (1, N'計算機') 
    56. 
  
  
  56. INSERT [dbo].[Department] ([depID], [depName]) VALUES (2, N'生物') 
    57. 
  
  
  57. INSERT [dbo].[Department] ([depID], [depName]) VALUES (3, N'數(shù)學') 
    58. 
  
  
  58. SET IDENTITY_INSERT [dbo].[Department] OFF 
    59. 
  
  
  59. /****** Object:  Table [dbo].[Student]    Script Date: 05/11/2015 23:16:23 ******/ 
    60. 
  
  
  60. SET ANSI_NULLS ON 
    61. 
  
  
  61. GO 
    62. 
  
  
  62. SET QUOTED_IDENTIFIER ON 
    63. 
  
  
  63. GO 
    64. 
  
  
  64. SET ANSI_PADDING ON 
    65. 
  
  
  65. GO 
    66. 
  
  
  66. CREATE TABLE [dbo].[Student]( 
    67. 
  
  
  67.     [stuID] [int] IDENTITY(1,1) NOT NULL, 
    68. 
  
  
  68.     [stuName] [varchar](50) NOT NULL, 
    69. 
  
  
  69.     [deptID] [int] NOT NULL, 
    70. 
  
  
  70. PRIMARY KEY CLUSTERED  
    71. 
  
  
  71. ( 
    72. 
  
  
  72.     [stuID] ASC 
    73. 
  
  
  73. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
    74. 
  
  
  74. ) ON [PRIMARY] 
    75. 
  
  
  75. GO 
    76. 
  
  
  76. SET ANSI_PADDING OFF 
    77. 
  
  
  77. GO 
    78. 
  
  
  78. SET IDENTITY_INSERT [dbo].[Student] ON 
    79. 
  
  
  79. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (1, N'計算機張三', 1) 
    80. 
  
  
  80. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (2, N'計算機李四', 1) 
    81. 
  
  
  81. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (3, N'計算機王五', 1) 
    82. 
  
  
  82. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (4, N'生物amy', 2) 
    83. 
  
  
  83. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (5, N'生物kity', 2) 
    84. 
  
  
  84. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (6, N'生物lucky', 2) 
    85. 
  
  
  85. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (7, N'數(shù)學_yiming', 3) 
    86. 
  
  
  86. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (8, N'數(shù)學_haoxue', 3) 
    87. 
  
  
  87. INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (9, N'數(shù)學_wuyong', 3) 
    88. 
  
  
  88. SET IDENTITY_INSERT [dbo].[Student] OFF 
    89. 
  
  
  89. /****** Object:  Default [DF__Departmen__depNa__5441852A]    Script Date: 05/11/2015 23:16:23 ******/ 
    90. 
  
  
  90. ALTER TABLE [dbo].[Department] ADD  DEFAULT ('') FOR [depName] 
    91. 
  
  
  91. GO 
    92. 
  
  
  92. /****** Object:  Default [DF__Score__category__5EBF139D]    Script Date: 05/11/2015 23:16:23 ******/ 
    93. 
  
  
  93. ALTER TABLE [dbo].[Score] ADD  DEFAULT ('') FOR [category] 
    94. 
  
  
  94. GO 
    95. 
  
  
  95. /****** Object:  Default [DF__Score__score__5FB337D6]    Script Date: 05/11/2015 23:16:23 ******/ 
    96. 
  
  
  96. ALTER TABLE [dbo].[Score] ADD  DEFAULT ((0)) FOR [score] 
    97. 
  
  
  97. GO 
    98. 
  
  
  98. /****** Object:  Default [DF__Student__stuName__59063A47]    Script Date: 05/11/2015 23:16:23 ******/ 
    99. 
  
  
  99. ALTER TABLE [dbo].[Student] ADD  DEFAULT ('') FOR [stuName] 
    100. 
  
  
  100. GO 
    101. 
  
  
  101. /****** Object:  ForeignKey [FK__Student__deptID__59FA5E80]    Script Date: 05/11/2015 23:16:23 ******/ 
    102. 
  
  
  102. ALTER TABLE [dbo].[Student]  WITH CHECK ADD FOREIGN KEY([deptID]) 
    103. 
  
  
  103. REFERENCES [dbo].[Department] ([depID]) 
    104. 
  
  
  104. GO 
    105. 
  
  
  105.  
    106. 
  
  
  106. 準備環(huán)境 
    107.

 

3 結(jié)果

面試的時候，沒有寫出來，當時腦袋昏沉沉的。也確實好久沒有寫復雜的sql語句了。今天花了2到3個小時，終于試出來了。不知道有沒有更好的寫法？

 
 
    
  
  
  1. -- 每個系里的最高分的學生信息 
    2. 
  
  
  2. SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores 
    3. 
  
  
  3. FROM Department 
    4. 
  
  
  4. LEFT JOIN Student 
    5. 
  
  
  5. on department.depID = student.deptID 
    6. 
  
  
  6. LEFT JOIN (SELECT Score.stuId, SUM(Score) AS scores  
    7. 
  
  
  7.             FROM Score 
    8. 
  
  
  8.             GROUP by stuID 
    9. 
  
  
  9. ) AS Dscore 
    10. 
  
  
  10. on Student.stuID = dScore.stuID 
    11. 
  
  
  11. where exists    (     
    12. 
  
  
  12. select * 
    13. 
  
  
  13. from 
    14. 
  
  
  14. (         
    15. 
  
  
  15.  SELECT deptID, MAX(scores) AS topScores 
    16. 
  
  
  16.  FROM Student 
    17. 
  
  
  17.  LEFT JOIN  
    18. 
  
  
  18.         ( 
    19. 
  
  
  19.  SELECT stuID,SUM(score) AS scores 
    20. 
  
  
  20.  FROM Score 
    21. 
  
  
  21.  GROUP BY stuID) AS newScore 
    22. 
  
  
  22.  ON Student.stuID = newScore.stuID 
    23. 
  
  
  23.  group by deptID) AS depScore 
    24. 
  
  
  24.  where Department.depID = depScore.deptID and Dscore.scores=depScore.topScores 
    25. 
  
  
  25.  ) 
    26. 
  
  
  26.  order by Department.depID,Student.stuID; 
    27.

分享名稱：.NET高級工程師面試題之SQL篇
當前URL：http://www.5511xx.com/article/coppehc.html