SQL面试题解决思路

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);

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

-----------------------------------  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*/