表ClassclassID className1 衣服2 裤子5 帽子10 鞋子表ProductsProductID ProductName ClassID ClickNum(点击数)1 男士衣服 1 902 女士衣服 1 853 男士裤子 2 304 女士裤子 2 455 男士帽子 5 666 女士帽子 5 107 男士鞋子 10 558 女士鞋子 10 30求每个分类的最高点击数商品，按下面降序方式显示ProductID ProductName ClickNum1 男士衣服 905 男士帽子 667 男士鞋子 554 女士裤子 45 =========================================
if object_id('Class') is not null drop table Classcreate table Class(classID int, className varchar(30))insert into Class(classID , className) select 1,'衣服'union all select 2, '裤子'union all select 5, '帽子'union all select 10,'鞋子'
if object_id('Products') is not null drop table Productscreate table Products(ProductID int identity, ProductName varchar(50) , ClassID int , ClickNum int)
insert into Products select '男士衣服',1,90 union all select '女士衣服',1,85 union all select '男士裤子',2,30 union all select '女士裤子',2,45 union all select '男士帽子',5,66 union all select '女士帽子',5,10 union all select '男士鞋子',10,55 union all select '女士鞋子',10,30
select * from Class
select *from Products pwhere not exists (select 1 from Products where ClassID = p.ClassId and ClickNum > p.ClickNum)
思路 后面的 SELECT 提取出来的是 最大的结果。 not exists 就是不排除这些记录（也就是说其他的都排除）所以最后结果是 最大的结果
如果用 exists 就是排除这些最大的记录结果。所以得到的结果就是 除了最大的记录以外的所有记录
还有一种方法获取最大值的和最小值的，就是用 group by 和 max 或是 min 但这种结果 只能找到最大值，最小值，不能找到具体的记录。因为他是分组数据。
select ClassID,max(ClickNum) as [ClickNum] from Products group by ClassID
ClassID ClickNum
1 902 455 6610 55
这个不能体现具体记录
----------------------------------------------
表A B C5 6 75 6 85 7 55 7 66 6 76 6 86 7 56 7 6求一句sql 结果为5 7 66 7 6groupby A中相同情况 B最大中的C最大的记录
create table tb(A int,B int,C int)insert into tb values(5 ,6 ,7)insert into tb values(5 ,6 ,8)insert into tb values(5 ,7 ,5)insert into tb values(5 ,7 ,6)insert into tb values(6 ,6 ,7)insert into tb values(6 ,6 ,8)insert into tb values(6 ,7 ,5)insert into tb values(6 ,7 ,6)goselect t.* from tb t where not exists(select 1 from tb where a= t.a and (b > t.b or (b= t.b and c > t.c)))drop table tb/*A B C ----------- ----------- ----------- 5 7 66 7 6（所影响的行数为 2 行）*/
====================================
如:有二条此记录,但我只要显示第一条记录.因为此时间只相差1秒,编号 卡号 日期与时间 状态GD311000003 2918263676 2011-02-23 15:18:30.543 進門開GD111000006 3094130220 2011-02-23 15:19:11.390 進門開GD111000007 3094130220 2011-02-23 15:20:26.420 出門開GD311000004 2918263676 2011-02-23 15:43:53.590 出門開GD111000008 3094130220 2011-02-23 16:16:06.483 出門開GD111000009 3094130220 2011-02-23 16:16:35.483 出門開GD311000005 2918263676 2011-02-23 16:34:08.610 出門開GD111000010 3094130220 2011-02-23 16:45:38.483 進門開GD111000011 3094130220 2011-02-23 16:45:39.483 進門開GD311000006 2918263676 2011-02-23 16:52:10.577 出門開GD311000007 2918263676 2011-02-23 16:54:31.577 進門開GD311000008 2918263676 2011-02-23 16:54:32.577 進門開GD311000009 2918263676 2011-02-23 17:03:38.577 出門開GD111000012 3094130220 2011-02-24 08:41:15.090 出門開GD311000010 2918263676 2011-02-24 08:33:53.670 出門開
编号自动增加的.如果卡号相同,时间相差在3秒之内刚为同一条记录,只显示其中一条.
-------------------------------------
/*******准备*********/declare @a table ( id varchar(50), cardno varchar(50), dt datetime, remark varchar(50))insert into @a select 'GD111000006','3094130220','2011-02-23 15:19:11.390','進門開'union all select 'GD111000007','3094130220','2011-02-23 15:20:26.420','出門開'union all select 'GD111000008','3094130220','2011-02-23 16:16:06.483','出門開'union all select 'GD111000009','3094130220','2011-02-23 16:16:35.483','出門開'union all select 'GD111000010','3094130220','2011-02-23 16:45:38.483','進門開'union all select 'GD111000011','3094130220','2011-02-23 16:45:39.483','進門開'union all select 'GD111000012','3094130220','2011-02-24 08:41:15.090','出門開'union all select 'GD311000003','2918263676','2011-02-23 15:18:30.543','進門開'union all select 'GD311000004','2918263676','2011-02-23 15:43:53.590','出門開'union all select 'GD311000005','2918263676','2011-02-23 16:34:08.610','出門開'union all select 'GD311000006','2918263676','2011-02-23 16:52:10.577','出門開'union all select 'GD311000007','2918263676','2011-02-23 16:54:31.577','進門開'union all select 'GD311000008','2918263676','2011-02-23 16:54:32.577','進門開'union all select 'GD311000009','2918263676','2011-02-23 17:03:38.577','出門開'union all select 'GD311000010','2918263676','2011-02-24 08:33:53.670','出門開'/*****执行****/select * from @a as a where not exists(select * from @a as b where b.cardno =a.cardno and b.remark=a.remark and b.dt>a.dt AND abs(datediff(s,b.dt,a.dt))<=3)/**********结果*****/GD111000006 3094130220 2011-02-23 15:19:11.390 進門開GD111000007 3094130220 2011-02-23 15:20:26.420 出門開GD111000008 3094130220 2011-02-23 16:16:06.483 出門開GD111000009 3094130220 2011-02-23 16:16:35.483 出門開GD111000011 3094130220 2011-02-23 16:45:39.483 進門開GD111000012 3094130220 2011-02-24 08:41:15.090 出門開GD311000003 2918263676 2011-02-23 15:18:30.543 進門開GD311000004 2918263676 2011-02-23 15:43:53.590 出門開GD311000005 2918263676 2011-02-23 16:34:08.610 出門開GD311000006 2918263676 2011-02-23 16:52:10.577 出門開GD311000008 2918263676 2011-02-23 16:54:32.577 進門開GD311000009 2918263676 2011-02-23 17:03:38.577 出門開GD311000010 2918263676 2011-02-24 08:33:53.670 出門開
