[sql server] 问题总结3 - 获取最大值，最小值的 not exists

表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      
=========================================

 

 

if object_id('Class') is not null drop table Class
create 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 Products
create 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 p
where 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     90
2     45
5     66
10   55

 

这个不能体现具体记录

 ----------------------------------------------

 

 

 

表
A B C
5 6 7
5 6 8
5 7 5
5 7 6
6 6 7
6 6 8
6 7 5
6 7 6

求一句sql 结果为
5 7 6
6 7 6

groupby 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)
go

select 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           6
6           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 出門開