SQL 面试题之头疼篇

收集一下最近的SQL 面试题和各位一同分享讨论，如果大家有更好的思路请拍板：

题目一：

if exists (select * from sysobjects where id = OBJECT_ID('[proceInfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [proceInfo]

CREATE TABLE [proceInfo] (
[ID] [int]  NOT NULL primary key,
[pName] [varchar]  (50) NOT NULL,
[pClick] [int]  NOT NULL,
[parentID] [int]  NOT NULL)


INSERT [proceInfo] ([ID],[pName],[pClick],[parentID]) VALUES ( 1,'男士裤子',200,2)
INSERT [proceInfo] ([ID],[pName],[pClick],[parentID]) VALUES ( 2,'女士裤子',250,2)
INSERT [proceInfo] ([ID],[pName],[pClick],[parentID]) VALUES ( 3,'男士帽子',100,3)
INSERT [proceInfo] ([ID],[pName],[pClick],[parentID]) VALUES ( 4,'女士帽子',90,3)
INSERT [proceInfo] ([ID],[pName],[pClick],[parentID]) VALUES ( 5,'男士鞋子',350,4)
INSERT [proceInfo] ([ID],[pName],[pClick],[parentID]) VALUES ( 6,'女士鞋子',250,4)

 我的答案：

select * from proceInfo where ID in(
select Min(ID) from
(
select * from proceInfo where pClick in(select MAX(pclick) from proceInfo group by parentID)
)a group by parentID
)

题目二

if exists (select * from sysobjects where id = OBJECT_ID('[Stu_Tea]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [Stu_Tea]

CREATE TABLE [Stu_Tea] (
[sid] [int]  NOT NULL,
[stuid] [int]  NOT NULL,
[tid] [int]  NOT NULL)

INSERT [Stu_Tea] ([sid],[stuid],[tid]) VALUES ( 1,1,2)
INSERT [Stu_Tea] ([sid],[stuid],[tid]) VALUES ( 2,2,2)
INSERT [Stu_Tea] ([sid],[stuid],[tid]) VALUES ( 3,3,2)
INSERT [Stu_Tea] ([sid],[stuid],[tid]) VALUES ( 4,4,2)
INSERT [Stu_Tea] ([sid],[stuid],[tid]) VALUES ( 5,1,3)
INSERT [Stu_Tea] ([sid],[stuid],[tid]) VALUES ( 6,1,4)
INSERT [Stu_Tea] ([sid],[stuid],[tid]) VALUES ( 7,2,4)

我的答案：

select distinct(stuid) from dbo.Stu_Tea where tid in(
(select top(1) tid from(
select  COUNT(tid) c,tid from Stu_Tea group by tid)a
order by C desc),
(select top(1) tid from(
select  COUNT(tid) c,tid from Stu_Tea group by tid)a
order by C asc))