一个面试题，被考住了

Question

表Class
classID  className
1        衣服
2        裤子
5        帽子
10       鞋子
表Products
ProductID     ProductName   ClassID   ClickNum(点击数)
1             男士衣服      1         90
2             女士衣服      1         85
3             男士裤子      2         30
4             女士裤子      2         45
5             男士帽子      5         66
6             女士帽子      5         10
7             男士鞋子      10        55
8             女士鞋子      10        30
求每个分类的最高点击数商品，按下面降序方式显示
ProductID    ProductName    ClickNum
1            男士衣服       90
5            男士帽子       66
7            男士鞋子       55
4            女士裤子       45       有几种解决方法，高手指点下....

Answer 1

select *
from tb t
where not exists (select 1 from tb where ClassID = t.ClassId and ClickNum > t.ClickNum)

Answer 2

select *
from tb t
where not exists (select 1 from tb where ClassID = t.ClassId and ClickNum > t.ClickNum)
order by ClickNum desc  --降序！

Answer 3

select *from tb t
where not exists (select 1 from tb where ClassID = t.ClassId and ClickNum > t.ClickNum)

Answer 4

CREATE TABLE #Class
(
classID INT, 
className NVARCHAR(100)
)
INSERT #Class
SELECT 1, N'衣服' UNION ALL
SELECT 2, N'裤子' UNION ALL
SELECT 5, N'帽子' UNION ALL
SELECT 10, N'鞋子'
CREATE TABLE #Products
(
ProductID INT, 
ProductName NVARCHAR(100),
ClassID INT,
ClickNum INT
)
INSERT #Products
SELECT 1, N'男士衣服', 1, 90 UNION ALL
SELECT 2, N'女士衣服', 1, 85 UNION ALL
SELECT 3, N'男士裤子', 2, 30 UNION ALL
SELECT 4, N'女士裤子', 2, 45 UNION ALL
SELECT 5, N'男士帽子', 5, 66 UNION ALL
SELECT 6, N'女士帽子', 5, 10 UNION ALL
SELECT 7, N'男士鞋子', 10, 55 UNION ALL
SELECT 8, N'女士鞋子', 10, 30
GO
--SQL:
--#1
SELECT N.ProductID, N.ProductName, N.ClickNum FROM 
(
SELECT ClassID, ClickNum=MAX(ClickNum) FROM
(SELECT B.* FROM #Class A LEFT JOIN #Products B ON A.classID = B.classID) C
GROUP BY ClassID
) M
INNER JOIN #Products N
ON M.ClickNum = N.ClickNum
ORDER BY M.ClickNum DESC
--#2
SELECT B.* FROM
(SELECT classID FROM #Class) A
OUTER APPLY
(SELECT TOP(1) /*WITH TIES*/ ProductID, ProductName, ClickNum FROM #Products WHERE ClassID = A.ClassID ORDER BY ClickNum DESC) B
ORDER BY B.ClickNum DESC
--RESULT
/*
1 男士衣服 90
5 男士帽子 66
7 男士鞋子 55
4 女士裤子 45
*/

Answer 5

select * from tb t
where not exists (select 1 from tb where ClassID = t.ClassId and ClickNum > t.ClickNum)
order by ClickNum desc

Answer 6

CREATE TABLE Class
(
    classID INT, 
    className NVARCHAR(100)
)
INSERT Class
SELECT 1, N'衣服' UNION ALL
SELECT 2, N'裤子' UNION ALL
SELECT 5, N'帽子' UNION ALL
SELECT 10, N'鞋子'
CREATE TABLE Products
(
    ProductID INT, 
    ProductName NVARCHAR(100),
    ClassID INT,
    ClickNum INT
)
INSERT Products
SELECT 1, N'男士衣服', 1, 90 UNION ALL
SELECT 2, N'女士衣服', 1, 85 UNION ALL
SELECT 3, N'男士裤子', 2, 30 UNION ALL
SELECT 4, N'女士裤子', 2, 45 UNION ALL
SELECT 5, N'男士帽子', 5, 66 UNION ALL
SELECT 6, N'女士帽子', 5, 10 UNION ALL
SELECT 7, N'男士鞋子', 10, 55 UNION ALL
SELECT 8, N'女士鞋子', 10, 30
GOselect m.ProductID ,m.ProductName,m.ClickNum
from Products m  , Class n
where m.ClassID = n.ClassID and
m.ClickNum = (select max(ClickNum) from Products where ClassID = m.ClassID)select m.ProductID ,m.ProductName,m.ClickNum
from Products m  , Class n
where m.ClassID = n.ClassID and
not exists (select 1 from Products where ClassID = m.ClassID and ClickNum > m.ClickNum)drop table Class , Products/*
ProductID   ProductName                                                                                          ClickNum    
----------- ---------------------------------------------------------------------------------------------------- ----------- 
1           男士衣服                                                                                                 90
4           女士裤子                                                                                                 45
5           男士帽子                                                                                                 66
7           男士鞋子                                                                                                 55（所影响的行数为 4 行）ProductID   ProductName                                                                                          ClickNum    
----------- ---------------------------------------------------------------------------------------------------- ----------- 
1           男士衣服                                                                                                 90
4           女士裤子                                                                                                 45
5           男士帽子                                                                                                 66
7           男士鞋子                                                                                                 55（所影响的行数为 4 行）
*/

Answer 7

CREATE TABLE Class
(
    classID INT, 
    className NVARCHAR(100)
)
INSERT Class
SELECT 1, N'衣服' UNION ALL
SELECT 2, N'裤子' UNION ALL
SELECT 5, N'帽子' UNION ALL
SELECT 10, N'鞋子'
CREATE TABLE Products
(
    ProductID INT, 
    ProductName NVARCHAR(100),
    ClassID INT,
    ClickNum INT
)
INSERT Products
SELECT 1, N'男士衣服', 1, 90 UNION ALL
SELECT 2, N'女士衣服', 1, 85 UNION ALL
SELECT 3, N'男士裤子', 2, 30 UNION ALL
SELECT 4, N'女士裤子', 2, 45 UNION ALL
SELECT 5, N'男士帽子', 5, 66 UNION ALL
SELECT 6, N'女士帽子', 5, 10 UNION ALL
SELECT 7, N'男士鞋子', 10, 55 UNION ALL
SELECT 8, N'女士鞋子', 10, 30
GO--SQL 2005
SELECT ProductID,ProductName,ClickNum from (
SELECT h.ProductID,h.ProductName,h.ClickNum,Row_number() over(partition by h.ClassID order by h.ClickNum DESC) as num
FROM Products h) m
WHERE num = 1
ORDER BY ClickNum DESCDROP TABLE Class,Products/*
ProductID ProductName ClickNum
1 男士衣服 90
5 男士帽子 66
7 男士鞋子 55
4 女士裤子 45
*/

Answer 8

2005用CTE方法：
with Md
(
  SELECT 
    ProductID,
    ProductName,
    ClickNum,
    Row_number() over(partition by h.ClassID order by h.ClickNum DESC) as num
)
SELECT ProductID,ProductName,ClickNum from MdWHERE num = 1
ORDER BY ClickNum DESC

Answer 9

select m.cn,n.productname,n.productid from #products n inner join (select b.cn from #class a inner join (select max(ClickNum) as cn,ClassID from #products group by ClassID) b On a.classID=b.classID ) M on n.clicknum=m.cn order by cn desc

Answer 10

select P.ProductID,P.ProductName,P.ClickNum
from Products as P,(select ProductID,max(ClickNum) from Products group by ClassID) as Q
where ProductID = Q.ProductID order by P.ClickNum desc

Answer 11

use tempdb;
/*
create table class
(
classID int not null,
className nvarchar(10) not null
);
insert into class(classID,className)
values
(1,'衣服'),
(2,'裤子'),
(5,'帽子'),
(10,'鞋子');create table product
(
ProductID int not null,
ProductName nvarchar(10) not null,
ClassID int not null,
ClickNum int not null
);
insert into product(ProductID,ProductName,ClassID,ClickNum)
values
(1,'男士衣服',1,90),
(2,'女士衣服',1,85),
(3,'男士裤子',2,30),
(4,'女士裤子',2,45),
(5,'男士帽子',5,66),
(6,'女士帽子',5,10),
(7,'男士鞋子',10,55),
(8,'女士鞋子',10,30);
*/
select t.ProductID,t.ProductName,t.ClickNum
from 
(
select *,
ROW_NUMBER() over(partition by classid order by clicknum desc) as [sortnum]
from product
 ) as t
 where t.sortnum = 1
 order by t.ClickNum desc;

Answer 12

SELECT B.* FROM
(SELECT classID FROM #Class) A
OUTER APPLY
(SELECT TOP(1) /*WITH TIES*/ ProductID, ProductName, ClickNum FROM #Products WHERE ClassID = A.ClassID ORDER BY ClickNum DESC) B
ORDER BY B.ClickNum DESC
SELECT ProductID,ProductName,ClickNum from (
SELECT h.ProductID,h.ProductName,h.ClickNum,Row_number() over(partition by h.ClassID order by h.ClickNum DESC) as num
FROM Products h) m
WHERE num = 1
ORDER BY ClickNum DESC
只有这两个方法没问题，其余的都有点问题，
比如当男士衣服和女士衣服点击数一样的时候就会把两条都显示出来
ProductID     ProductName   ClassID   ClickNum(点击数)
1             男士衣服      1         90
2             女士衣服      1         90

Answer 13

SELECT * FROM tb a where not exists( select 1 from tb b where b.classId=a.classId and b.clickNum>a.clickNum )
group by a.clicknum desc
-- 我要习惯这种写法！

Answer 14

try the following sql:
select productid,productname,clicknum
from class a
cross apply 
(select top 1 productid,productname,clicknum from products b where
a.classid=b.classid order by clicknum desc) c
order by clicknum desc

Answer 15

SELECT ProductID, ProductName, ClickNum
FROM Products t
WHERE (NOT EXISTS
          (SELECT 1
         FROM Products
         WHERE ClassID = t.ClassID AND ClickNum > t.ClickNum))
ORDER BY ClickNum DESC

Answer 16

create table Products
(
ProductID int primary key,
ProductName varchar(20),
ClassID int,
ClickNum int)
insert Products
select 1,'男士衣服',1,90 union all
select 2,'女士衣服',1,85 union all
select 3,'男士裤子',2,30 union all
select 4,'女士裤子',2,45 union all
select 5,'男士帽子',5,66 union all
select 6,'女士帽子',5,10 union all
select 7,'男士鞋子',10,55 union all
select 8,'女士鞋子',10,30
select ProductID,ProductName,ClickNum from Products a where exists(select 1 from Products where ClassID = a.ClassID and ClickNum <a.ClickNum) order by a.ClickNum desc

Answer 17

另外还有一种是用外联的方法，不过根据某高人说，这种方法性能不比exists的好。

Answer 18

select productid,productname,clicknum from class,products
where class.classid=products.productid
group by classid 
order by  clicknum desc;
先搜索出productid ,productname clicknum 的所有值，根据classid
在根据clsssid对其分组
最后根据 clicknum 排序

Answer 19

Declare @t1 table (classID varchar(5),className nvarchar(30))
Insert into @t1(classID,className)
Select '1',N'衣服' union all
Select '2',N'裤子' union all
Select '5',N'帽子' union all
Select '10',N'鞋子'Declare @t2 table (ProductID int,ProductName nvarchar(30),ClassID varchar(5),[ClickNum(点击数)] int)
Insert into @t2(ProductID,ProductName,ClassID,[ClickNum(点击数)])
Select 1,N'男士衣服','1',90 union all
Select 2,N'女士衣服','1',85 union all
Select 3,N'男士裤子','2',30 union all
Select 4,N'女士裤子','2',45 union all
Select 5,N'男士帽子','5',66 union all
Select 6,N'女士帽子','5',10 union all
Select 7,N'男士鞋子','10',55 union all
Select 8,N'女士鞋子','10',30Select a.ProductID,a.ProductName,a.[ClickNum(点击数)]
From @t2 a
Where not exists (Select * from @t2 b where a.ClassID=b.ClassID and a.[ClickNum(点击数)]<b.[ClickNum(点击数)])
Order by a.[ClickNum(点击数)] descSelect a.ProductID,a.ProductName,[ClickNum(点击数)]
From
(
Select ROW_NUMBER() over(partition by ClassID order by [ClickNum(点击数)] desc) as ID,
ProductID,ProductName,[ClickNum(点击数)]
From @t2 
) a
Where a.ID=1
Order by [ClickNum(点击数)] desc

Answer 20

select p.ProductID,p.ProductName,p.ClickNum from Products p ,Class c
where c.classID = p.ClassID
and p.ClickNum in (select max(ClickNum) from Products group by ClassID)
order by p.ClickNum desc-----
ProductID ProductName ClickNum
1 男士衣服 90
5 男士帽子 66
7 男士鞋子 55
4 女士裤子 45

Answer 21

;with cte as(
select a.className,b.*,rn=ROW_NUMBER() over(PARTITION by a.classID order by b.ClickNum desc)
from #Class a,#Products b where a.classID=b.ClassID
)select ProductID,ProductName,ClickNum
from cte where rn=1ProductID   ProductName ClickNum
----------- ----------- ---------
1           男士衣服 90
4           女士裤子 45
5           男士帽子 66
7           男士鞋子 55(4 行受影响)

Answer 22

select ProductID,ProductName,ClickNum from Products P1 where ClickNum in(select Max(ClickNum) from Products where P1.ClassID=ClassID Group by ClassID) order by ClickNum