如何取 分组纪录的最大值？ ( 积分: 100 )

S

sdjxmikill

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #1

如何取 分组纪录的最大值？如下：(以 设备名称 为分组,id为自增变量)
设备名称 id model 内容
a 1 red wer
a 2 black sd
b 3 brue sdf
b 4 green sdf2
c 5 black deee
c 6 yellow eedd
c 7 white 333s
需要得到的是每组中qty数量最大的纪录，也就是要得到：
设备名称 id model 内容
a 2 black sd
b 4 green sdf2
c 7 white 333s

 

L

luckyduanlian

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #2

select max(id) from table1 group by 设备名称

 

S

sdjxmikill

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #3

luckyduanlian:
你这样只能取一个结果

 

X

xinjinren

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #4

select distinct a.* from a inner join (select 设备名称,max(ID) as id from a group by 设备名称)b on a.设备名称=b.设备名称 and a.ID=b.ID order by a.设备名称,a.id

 

S

sunnyfairy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #5

刚才写的好像不行,再想想

 

S

sunnyfairy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #6

Select Name,
(Select Top 1 ID From abc aa Where aa.Name = a.Name Order by ID DESC) ID
From abc a
Group by Name

参考一下,刚刚建库测试了是可以的.
CREATE TABLE [abc] (
[ID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_abc] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

 

S

sdjxmikill

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #7

xinjinren,的对，感谢，送分

 

S

sdjxmikill

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #8

接受答案了.

 

S

sunnyfairy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #9

不过效率可能不好.

 

S

sdjxmikill

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-04

• #10

送错分了，xinjinren, 开贴补给你