SQL面试题解决思路

2012-04-27

SQL面试题刚看到的时候，感觉挺简单的，但是自己真的写出来却不是那么容易。呵呵，今天算是给自己上了一课。重

SQL面试题
刚看到的时候，感觉挺简单的，但是自己真的写出来却不是那么容易。呵呵，今天算是给自己上了一课。
重新温习了下SQL语句。
首先，创建数据表并插入一定量的数据

SQL code

create table Grade(GradeID int identity(1,1) primary key,SNO int,CNO int,Score float)insert into Grade(SNO,CNO,Score) values(100,10,100);insert into Grade(SNO,CNO,Score) values(100,11,45);insert into Grade(SNO,CNO,Score) values(100,12,30);insert into Grade(SNO,CNO,Score) values(101,10,87);insert into Grade(SNO,CNO,Score) values(101,11,100);insert into Grade(SNO,CNO,Score) values(101,12,87);insert into Grade(SNO,CNO,Score) values(102,10,87);insert into Grade(SNO,CNO,Score) values(102,11,45);insert into Grade(SNO,CNO,Score) values(102,12,100);insert into Grade(SNO,CNO,Score) values(103,12,50);

问题如下：
--查询每门课程的平均（最高/最低）分及课程号
--查询每门课程第1名的学生的学号
--查询每门课程中超过平均分的所有学生的学号

请您记录下自己的SQL语句与返回结果。没测试过的就别贴出来了。

另外，想问下，谁有MySql创建存储过程的例子，比较全的。
最好能像这样子的(MySql参数神马的好像用in out，inout，本人只会比较简单的，\(^o^)/~ )
（SQL Server）

SQL code

create proc [dbo].[Product_Insert]@ProductCategoryID int,@ProductName nvarchar(50),@ProductImage image,@Description text,@Price smallmoneyas--Start the transactionbegin transactiondeclare @ProductImageID intinsert into ProductImages(ProductImage) values(@ProductImage)--Roll back the transaction if there were any errorif @@error<>0begin--Roll back the transactionrollback--Raise an error and returnraiserror('Error insert into ProductImages.',16,1)returnendset @ProductImageID=@@identityinsert into Products(ProductCategoryID,ProductName,ProductImageID,Description,Price) values(@ProductCategoryID,@ProductName,@ProductImageID,@Description,@Price)--Roll back the transaction if there were any errorif @@error<>0begin--Roll back the transactionrollback--Raise an error and returnraiserror('Error insert into Products.',16,1)returnendcommit

（晚上我再贴上自己的语句以及查询结果。O(∩_∩)O~，顺便温习下T-SQL，嘿嘿。分数不多。）

[解决办法]
呵呵.
[解决办法]

[解决办法]

SQL code

-----------------------------------  Author: liangCK 小梁--  Title : 查每个分组前N条记录--  Date  : 2008-11-13 17:19:23-----------------------------------> 生成测试数据: #TIF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #TCREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)INSERT INTO #TSELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALLSELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALLSELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALLSELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALLSELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALLSELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALLSELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALLSELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALLSELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALLSELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'--SQL查询如下:--按GID分组,查每个分组中Date最新的前2条记录--1.字段ID唯一时:SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)--2.如果ID不唯一时:SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)--SQL Server 2005 使用新方法--3.使用ROW_NUMBER()进行排位分组SELECT ID,GID,Author,Title,DateFROM(   SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*   FROM #T) AS TWHERE rid<=2--4.使用APPLYSELECT DISTINCT b.*FROM #T AS aCROSS APPLY(    SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC) AS b--结果/*ID   GID         Author                        Title                                   Date---- ----------- ----------------------------- --------------------------------------- -----------------------003  1           格罗夫Groff.J.R.                 SQL完全手册                                 2009-07-01 00:00:00.000004  1           KalenDelaney                  SQLServer2005技术内幕存储引擎                   2008-08-01 00:00:00.000005  2           Alex.Kriegel.Boris.M.Trukhnov SQL宝典                                   2007-10-05 00:00:00.000007  2           胡百敬                           SQLServer2005数据库开发详解                    2008-06-15 00:00:00.000009  3           赵松涛                           SQLServer2005系统管理实录                     2008-10-01 00:00:00.000010  3           黄占涛                           SQL技术手册                                 2006-01-01 00:00:00.000(6 行受影响)*/--得到每组前几条数据--假設每組Col1中， Col3不會重復--建立測試環境Create Table TEST(Col1 Varchar(10), Col2 Varchar(10), Col3 Int)--插入數據Insert TEST Select 'BD1V','Label', 4Union All Select 'BD1V', 'BATT', 2Union All Select 'BD1V', 'ODD', 3Union All Select 'BD1V', 'HDD', 5Union All Select 'BD1V', 'LCD', 1Union All Select 'BD1W','HDD', 3Union All Select 'BD1W','RAM', 8Union All Select 'BD1W','TP CABLE', 5Union All Select 'BD1W','LCD', 6Union All Select 'BD1W','Label', 2Union All Select 'BL3', 'LCD CABLE', 7Union All Select 'BL3', 'LABEL', 6Union All Select 'BL3', 'LCD', 5Union All Select 'BL3', 'RAM', 1Union All Select 'BL3D', 'Label', 4GO--測試--方法一：Select Col1, Col2, Col3 From TEST AWhere (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3) < 3Order By Col1, Col3 Desc--方法二：Select Col1, Col2, Col3 From TEST AWhere Exists (Select Count(*) From TEST Where Col1 = A.Col1 And Col3 > A.Col3 Having Count(*) < 3)Order By Col1, Col3 Desc--方法三：Select Col1, Col2, Col3 From TEST AWhere Col3 In (Select TOP 3 Col3 From TEST Where Col1 = A.Col1 Order By Col3 Desc)Order By Col1, Col3 DescGO--刪除測試環境Drop Table TEST--結果/*Col1  Col2   Col3BD1V HDD  5BD1V Label  4BD1V ODD  3BD1W RAM  8BD1W LCD   6BD1W TP CABLE 5BL3  LCD CABLE 7BL3  LABEL  6BL3  LCD   5BL3D Label  4*/ 
 
[解决办法]
路过看看
[解决办法]
SQL codeselect cno,avg(score) as '平均分' from grade group by cnoselect *from grade ,(select cno,max(score) as 'Maxs' from grade group by cno) as T where grade.score = T.maxs and T.cno=grade.cnoselect *from grade,(select cno,avg(score) as 'avgs' from grade group by cno) as T where grade.score>T.avgs and grade.cno=T.cno
[解决办法]
学习了

热点排行