SQL 面试题两道,好手接招

SQL 面试题两道,高手接招！

SQL code

第一题--有如下表，删除数据表中重复记录（只保留一条重复记录）CREATE TABLE #T(ID INT PRIMARY KEY,[NAME] NVARCHAR(50),[CLASS] NVARCHAR(50),[StuNo] nvarchar(50),[Subject] nvarchar(50),[Score] int)GOINSERT INTO #T SELECT 1,'张三','10','5011','数学',95 UNIONSELECT 2,'李四','10','5011','数学',95 UNIONSELECT 3,'张三','10','5011','数学',95 UNION SELECT 4,'王五','10','5011','数学',95 GOSELECT * FROM #TGODROP TABLE #T第二题：--有四支球队（A,C,D,D），让他们，分别组合对打。如A-B;A-C;A-D;B-C;B-D;C-D;CREATE TABLE #B([NAME] VARCHAR(1))GOINSERT INTO #BSELECT 'A' UNIONSELECT 'B' UNIONSELECT 'C' UNIONSELECT 'D' GOSELECT * FROM #BGODROP TABLE #B

SQL code

SELECT A.NAME,B.NAMEFROM #BA,#B BWHERE A.NAME < B.NAME
[解决办法]
SQL code
--有如下表，删除数据表中重复记录（只保留一条重复记录）CREATE TABLE #T(ID INT PRIMARY KEY,[NAME] NVARCHAR(50),[CLASS] NVARCHAR(50),[StuNo] nvarchar(50),[Subject] nvarchar(50),[Score] int)GOINSERT INTO #T SELECT 1,'张三','10','5011','数学',95 UNIONSELECT 2,'李四','10','5012','数学',95 UNIONSELECT 3,'张三','10','5011','数学',95 UNION SELECT 4,'王五','10','5013','数学',95 GOdelete  from #T where exists(select 1 from #T b where b.[StuNo]=#T.[StuNo]  and b.ID>#T.ID)GOselect * from #t/*ID    NAME    CLASS    StuNo    Subject    Score2    李四    10    5012    数学    953    张三    10    5011    数学    954    王五    10    5013    数学    95*/--第二题：--有四支球队（A,C,D,D），让他们，分别组合对打。如A-B;A-C;A-D;B-C;B-D;C-D;CREATE TABLE #B([NAME] VARCHAR(1))GOINSERT INTO #BSELECT 'A' UNIONSELECT 'B' UNIONSELECT 'C' UNIONSELECT 'D' GOselect * from #B across join #B bwhere a.NAME<b.NAME/*NAME    NAME----------------A    BA    CB    CA    DB    DC    D*/
[解决办法]
如果是除了ID其他字段值都相同的话，如下
DELETE FROM #T
WHERE EXISTS(SELECT 1 FROM #T T1 WHERE T1.NAME=#T.NAME AND T1.CLASS=#T.CLASS AND
 T1.StuNo=#T.StuNo AND T1.Subject=#T.Subject AND T1.Score=#T.Score AND T1.ID>#T.ID )





[解决办法]
SQL code
--第一题;with t as(select id=row_number() over(partition by [NAME] order by [StuNo]),*)delete from t where id>1
[解决办法]
SELECT * FROM #t
DELETE FROM  #T WHERE ID not IN (SELECT max(ID) id FROM #T  GROUP BY name,CLASS,StuNo,Subject,Score)