最近面试的两道SQL题目,自认为行走江湖这么多年,这些题目总难不倒我吧!结果却相差巨大,都没做对。愧对大家一直以来对我的期望。我要反思自己的不足之处,虚心听取建议,认真的学习。这些答案,都是在CSDN上面发帖后,论坛里的高手给的答案。真是感激不尽。谢谢!
CREATE TABLE #T
(
ID INT PRIMARY KEY,
[NAME] NVARCHAR(50),
[CLASS] NVARCHAR(50),
[StuNo] nvarchar(50),
[Subject] nvarchar(50),
[Score] int
)
GO
INSERT INTO #T
SELECT 1,'张三','10','5011','数学',95 UNION
SELECT 2,'李四','10','5012','数学',95 UNION
SELECT 3,'张三','10','5011','数学',95 UNION
SELECT 4,'王五','10','5013','数学',95 UNION
SELECT 5,'王五','10','5013','数学',95
GO
--第一种方法:TravyLee
DELETE #T WHERE EXISTS
(SELECT 1 FROM #T B
WHERE B.NAME=#T.NAME AND B.CLASS=#T.CLASS AND B.StuNo =#T.StuNo AND B.[Subject]=#T.[Subject] AND B.ID<#T.ID
)
GO
--第二种方法:TravyLee
;with t as
(
select RINDEX=row_number() over(partition by [NAME] order by [StuNo]),* FROM #T
)
DELETE #T WHERE ID IN( SELECT ID FROM t where RINDEX>1)
GO
--第三种方法:ni_sheng
DELETE FROM #T WHERE ID not IN (SELECT max(ID) id FROM #T GROUP BY name,CLASS,StuNo,Subject,Score)
GO
SELECT * FROM #T
GO
DROP TABLE #T
--第二题:
--有四支球队(A,C,D,D),让他们,分别组合对打。如A-B;A-C;A-D;B-C;B-D;C-D;
CREATE TABLE #B
(
[NAME] VARCHAR(1)
)
GO
INSERT INTO #B
SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C' UNION
SELECT 'D'
GO
SELECT A.NAME,B.NAME FROM #B A,#B B WHERE A.NAME < B.NAME ORDER BY a.NAME
GO
DROP TABLE #B