关于group by查询得到分组后每组的最小值的记录

[forjzforjz]

ID 手机型号 价格 时间
----------------------------------------------------------------------
1 philips 625 1400 2003-1-1
2 philips 625 1200 2003-3-1
3 philips 625 1000 2003-6-1

4 NOKIE 8250 1800 2002-1-1
5 NOKIE 8250 1600 2002-6-1
6 NOKIE 8250 1400 2002-12-1

7 NOKIE 8210 1500 2001-1-1
8 NOKIE 8210 1200 2001-6-1
9 NOKIE 8210 1000 2001-12-1


目的:提取每款手机价格最低的那条记录的信息

希望得到的查询结果:

ID 手机型号 价格 时间
--------------------------------------------------------------------
3 philips 625 1000 2003-6-1
6 NOKIE 8250 1400 2002-12-1
9 NOKIE 8210 1000 2001-12-1

问题：请问如何实现这个查询?



为方便测试，以下是创建表的脚本。
===============================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Phone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Phone]
GO

CREATE TABLE [dbo].[Phone] (
[ID] [bigint] NOT NULL ,
[Model] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Rate] [numeric](18, 0) NULL ,
[dDate] [datetime] NULL
) ON [PRIMARY]
GO

insert into phone values(1,'philips 625',1400,'2003-1-1')
insert into phone values(2,'philips 625',1200,'2003-3-1')
insert into phone values(3,'philips 625',1000,'2003-6-1')
insert into phone values(4,'NOKIE 8250',1800,'2002-1-1')
insert into phone values(5,'NOKIE 8250',1600,'2002-6-1')
insert into phone values(6,'NOKIE 8250',1400,'2002-12-1')
insert into phone values(7,'NOKIE 8210',1500,'2001-1-1')
insert into phone values(8,'NOKIE 8210',1200,'2001-6-1')
insert into phone values(9,'NOKIE 8210',1000,'2001-12-1')
===============================================================================

[pbsql]

最新的价格表：
select ID,Model 手机型号,Rate 价格,dDate 时间
from phone
where dDate in
(select max(dDate) from phone group by Model)
结果：
3 philips 625 1000 2003-06-01 00:00:00.000
6 NOKIE 8250 1400 2002-12-01 00:00:00.000
9 NOKIE 8210 1000 2001-12-01 00:00:00.000

[forjzforjz]

=======================================================================
select * from Phone a where Rate in (select min(Rate) from Phone where a.Model=phone.Model )
=======================================================================
数据只在Phone表中查询，没有其他表

[playyuer]

select *
from Phone a
where Rate = (select min(Rate)
from Phone
where a.Model = phone.Model)

select *
from Phone a
where not exists (select *
from Phone
where Model > a.Model)

[forjzforjz]

==========================================================
最新的价格表：
select ID,Model 手机型号,Rate 价格,dDate 时间
from phone
where dDate in
(select max(dDate) from phone group by Model)
结果：
3 philips 625 1000 2003-06-01 00:00:00.000
6 NOKIE 8250 1400 2002-12-01 00:00:00.000
9 NOKIE 8210 1000 2001-12-01 00:00:00.000
=====================================================================

希望得到的数据是每款手机最低价格的那条记录，但根据你的语句改写成：
select * from phone where rate in(select min(rate) from phone group by Model)

但结果是：
1 philips 625 1400 2003-01-01 00:00:00.000
3 philips 625 1000 2003-06-01 00:00:00.000
6 NOKIE 8250 1400 2002-12-01 00:00:00.000
9 NOKIE 8210 1000 2001-12-01 00:00:00.000

但编号“1”的记录1400刚好是编号“6”记录的最小值。

[zjcxc]

--查询
select a.*
from Phone a join(
select Model,Rate=min(Rate)
from Phone
group by Model
)b on a.Model=b.Model and a.Rate=b.Rate
order by id

[zjcxc]

--再改一下:

select a.*
from Phone a join(
select id=min(a.id) from Phone a join(
select Model,Rate=min(Rate)
from Phone group by Model
)b on a.Model=b.Model and a.Rate=b.Rate
group by a.Model
)b on a.id=b.id
order by a.id

[zjcxc]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Phone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Phone]
GO

CREATE TABLE [dbo].[Phone] (
[ID] [bigint] NOT NULL ,
[Model] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Rate] [numeric](18, 0) NULL ,
[dDate] [datetime] NULL
) ON [PRIMARY]
GO

insert into phone values(1,'philips 625',1400,'2003-1-1')
insert into phone values(2,'philips 625',1200,'2003-3-1')
insert into phone values(3,'philips 625',1000,'2003-6-1')
insert into phone values(4,'NOKIE 8250',1800,'2002-1-1')
insert into phone values(5,'NOKIE 8250',1600,'2002-6-1')
insert into phone values(6,'NOKIE 8250',1400,'2002-12-1')
insert into phone values(7,'NOKIE 8210',1500,'2001-1-1')
insert into phone values(8,'NOKIE 8210',1200,'2001-6-1')
insert into phone values(9,'NOKIE 8210',1000,'2001-12-1')
go

--查询
select a.*
from Phone a join(
select id=min(a.id) from Phone a join(
select Model,Rate=min(Rate)
from Phone group by Model
)b on a.Model=b.Model and a.Rate=b.Rate
group by a.Model
)b on a.id=b.id
order by a.id

/*--测试结果
ID Model Rate dDate
-------- ----------------- -------- --------------------------
3 philips 625 1000 2003-06-01 00:00:00.000
6 NOKIE 8250 1400 2002-12-01 00:00:00.000
9 NOKIE 8210 1000 2001-12-01 00:00:00.000

（所影响的行数为 3 行）

--*/

[forjzforjz]

===================================================================
select *
from Phone a
where Rate = (select min(Rate)
from Phone
where a.Model = phone.Model)

select *
from Phone a
where not exists (select *
from Phone
where Model > a.Model)

=========================================================================
请问语句中的“a”代表什么？以及查询思想？“a”是跟“phone”表一样的临时表？望指点

[zjcxc]

a是别名,代表表:Phone

指定别名是为了避免与后面子查询中的Phone冲突

[forjzforjz]

平时比较少接触子查询，所以对子查询知之甚少，如果a是phone的别名，即两者是等立的，为何
select *
from Phone
where Rate = (select min(Rate)
from Phone
where phone.Model = phone.Model)
会得到以下的结果？

3 philips 625 1000 2003-06-01 00:00:00.000
9 NOKIE 8210 1000 2001-12-01 00:00:00.000

而与
select *
from Phone a
where Rate = (select min(Rate)
from Phone
where a.Model = phone.Model)

9 NOKIE 8210 1000 2001-12-01 00:00:00.000
6 NOKIE 8250 1400 2002-12-01 00:00:00.000
3 philips 625 1000 2003-06-01 00:00:00.000

产生不一致的结果？

[zjcxc]

写上别名,子查询中引用的就是外层的表,因为子查询中没有别名a

不写别名,就是引用子查询自己怕phone

这有一个优先级,或叫屏蔽的关系

[playyuer]

select *
from Phone a
where Rate = (select min(Rate)
from Phone
where Model = a.Model)

select *
from Phone a
where not exists (select *
from Phone
where Model = a.Model
and Rate > a.Rate
)


select *
from Phone a
where dDate = (select max(dDate)
from Phone
where Model = a.Model)

select *
from Phone a
where not exists (select *
from Phone
where Model = a.Model
and dDate < a.ddate
)

[winternet]

To:zjcxc(: 邹建 :)
--片段
select a.*
from phone a
inner join (
select a.id from phone a
inner join
(
select b.model,Rate=min(b.Rate)
from phone b
group by b.model
)b on a.model=b.model and a.Rate=b.Rate

)c on a.id=c.id


--zjcxc(: 邹建 :) 程序

select a.*
from Phone a join(
select id=min(a.id) from Phone a join(
select Model,Rate=min(Rate)
from Phone group by Model
)b on a.Model=b.Model and a.Rate=b.Rate
group by a.Model
)b on a.id=b.id
order by a.id

)b on a.model=b.model and a.Rate=b.Rate

)c on a.id=c.id

为什么要用id=min(a.id)这个呢？

[zjcxc]

因为同一型号的最小价格可能重复,所以这时要通过最小id只取一条.

[jinsfree]

select * from phone a where rate =(select min(rate) from phone where a.model=phone.model)

[forjzforjz]

谢谢大家的热心帮忙，帖子标题可能是一个错误的引导，可以不使用group by 语句，总之最终目的是得到每款手机的最低价格的那条记录，“Rate”是主要的条件参数，这个语句不错

select * from Phone a where Rate = (select min(Rate) from Phone where a.Model = phone.Model)

大家可能会碰到或者碰过或者将会碰到类似这样的问题，大家可以发表不同的解决方案，互相交流意见

[midnight2002]

这种帖子太多了!!随便找找

[forjzforjz]

补充：表中的时间字段(dDate)存在的意义是为了说明表中还存在其他字段，大家暂时不必理会这个时间字段(dDate)

[zjcxc]

我的方法有问题吗?

[forjzforjz]

你的方法没有问题,谢谢你的热心帮忙.因为除了取得每款手机的最低价格的那条记录外,还有很多附加条件,所以语句简单思路比较清晰,维护比较方便.