关于group by分组问题

Question

数据库环境是：sql2005
测试数据如下：       if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
    [USERNAME] [varchar] (25) NOT NULL,
    [JFSL] [int] NOT NULL,
    [SPBH] [varchar] (25)  NOT NULL ,
[TXLJ] [varchar] (150)  NOT NULL
) ON [PRIMARY]
insert  [test]
select 'doublebj28','3','fewfewf11','/client\images\default.gif' union all
select 'doublebj28','2','sp_102','/client\images\default.gif'    union all
select 'doublebj28','4','sp_110','/client\images\default.gif'    union all
select 'doublebj28','1','sp_119','/client\images\default.gif'    union all
select 'doublebj28','1','sp_52','/client\images\default.gif'     union all
select 'lnsx','5','bx_001','/client\images\default.gif'          union all
select 'lnsx','2','bx_002','/client\images\default.gif'  union all
select 'lnsx','47','cd_001','/client\images\default.gif'  union all
select 'lnsx','24','fffff_000','/client\images\default.gif'  union all
select 'lnsx','25','sp_0001','/client\images\default.gif'  union all
select 'lnsx','4','sp_100','/client\images\default.gif'  union all
select 'lnsx','2','sp_104','/client\images\default.gif'  union all
select 'lnsx','5','sp_106','/client\images\default.gif'  union all
select 'lnsx','9','sp_111','/client\images\default.gif'  union all
select 'lnsx','10','sp_113','/client\images\default.gif'  union all
select '中国红','2','sp_105','/client\images\default.gif'  union all
select '中国红','4','sp_107','/client\images\default.gif'  union all
select '中国红','2','sp_108','/client\images\default.gif'  union all
select '中国红','5','sp_123','/client\images\default.gif' 题目意思是：我想要获取同一组用户名中拥有最高的积分对应的商品编号是哪一行？
我想要得到分组之后的效果：
是doublebj28  4 sp_110 /client\images\default.gif
lnsx  47  cd_001 /client\images\default.gif
中国红 5 sp_123 /client\images\default.gif望大家给出T-sql语句或者思路？在线等待，谢谢！

Answer 1

select t.* from test t where JFSL = (select max(JFSL) from test where USERNAME = t.USERNAME)select t.* from test t where not exists (select 1 from test where USERNAME = t.USERNAME and JFSL > t.JFSL)

Answer 2

CREATE TABLE [dbo].[test] (
    [USERNAME] [varchar] (25) NOT NULL,
    [JFSL] [int] NOT NULL,
    [SPBH] [varchar] (25)  NOT NULL ,
    [TXLJ] [varchar] (150)  NOT NULL
) ON [PRIMARY]
insert  [test]
select 'doublebj28','3','fewfewf11','/client\images\default.gif' union all
select 'doublebj28','2','sp_102','/client\images\default.gif'    union all
select 'doublebj28','4','sp_110','/client\images\default.gif'    union all
select 'doublebj28','1','sp_119','/client\images\default.gif'    union all
select 'doublebj28','1','sp_52','/client\images\default.gif'     union all
select 'lnsx','5','bx_001','/client\images\default.gif'          union all
select 'lnsx','2','bx_002','/client\images\default.gif'             union all
select 'lnsx','47','cd_001','/client\images\default.gif'         union all
select 'lnsx','24','fffff_000','/client\images\default.gif'         union all
select 'lnsx','25','sp_0001','/client\images\default.gif'         union all
select 'lnsx','4','sp_100','/client\images\default.gif'             union all
select 'lnsx','2','sp_104','/client\images\default.gif'             union all
select 'lnsx','5','sp_106','/client\images\default.gif'             union all
select 'lnsx','9','sp_111','/client\images\default.gif'             union all
select 'lnsx','10','sp_113','/client\images\default.gif'         union all
select '中国红','2','sp_105','/client\images\default.gif'         union all
select '中国红','4','sp_107','/client\images\default.gif'         union all
select '中国红','2','sp_108','/client\images\default.gif'         union all
select '中国红','5','sp_123','/client\images\default.gif' select t.* from test t where JFSL = (select max(JFSL) from test where USERNAME = t.USERNAME)select t.* from test t where not exists (select 1 from test where USERNAME = t.USERNAME and JFSL > t.JFSL)drop table test/*USERNAME                  JFSL        SPBH                      TXLJ                                                                                                                                                   
------------------------- ----------- ------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 
中国红                       5           sp_123                    /client\images\default.gif
lnsx                      47          cd_001                    /client\images\default.gif
doublebj28                4           sp_110                    /client\images\default.gif（所影响的行数为 3 行）USERNAME                  JFSL        SPBH                      TXLJ                                                                                                                                                   
------------------------- ----------- ------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 
doublebj28                4           sp_110                    /client\images\default.gif
lnsx                      47          cd_001                    /client\images\default.gif
中国红                       5           sp_123                    /client\images\default.gif（所影响的行数为 3 行）*/

Answer 3

最好再加个order by select t.* from test t where JFSL = (select max(JFSL) from test where USERNAME = t.USERNAME) order by t.USERNAMEselect t.* from test t where not exists (select 1 from test where USERNAME = t.USERNAME and JFSL > t.JFSL) order by t.USERNAME

Answer 4

SELECT * FROM(
SELECT t.*,MAX(jfsl)OVER (PARTITION BY username) res
FROM 
test t)m
WHERE m.JFSL=m.resUSERNAME        JFSL        SPBH          TXLJ                          res
--------------------- --------------- ------------------------- -----------
doublebj28       4           sp_110       /client\images\default.gif      4
lnsx             47          cd_001      /client\images\default.gif       47
中国红           5           sp_123      /client\images\default.gif       5(3 row(s) affected)

Answer 5

   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
    [USERNAME] [varchar] (25) NOT NULL,
    [JFSL] [int] NOT NULL,
    [SPBH] [varchar] (25)  NOT NULL ,
    [TXLJ] [varchar] (150)  NOT NULL
) ON [PRIMARY]
insert  [test]
select 'doublebj28','3','fewfewf11','/client\images\default.gif' union all
select 'doublebj28','2','sp_102','/client\images\default.gif'    union all
select 'doublebj28','4','sp_110','/client\images\default.gif'    union all
select 'doublebj28','1','sp_119','/client\images\default.gif'    union all
select 'doublebj28','1','sp_52','/client\images\default.gif'     union all
select 'lnsx','5','bx_001','/client\images\default.gif'          union all
select 'lnsx','2','bx_002','/client\images\default.gif'             union all
select 'lnsx','47','cd_001','/client\images\default.gif'         union all
select 'lnsx','24','fffff_000','/client\images\default.gif'         union all
select 'lnsx','25','sp_0001','/client\images\default.gif'         union all
select 'lnsx','4','sp_100','/client\images\default.gif'             union all
select 'lnsx','2','sp_104','/client\images\default.gif'             union all
select 'lnsx','5','sp_106','/client\images\default.gif'             union all
select 'lnsx','9','sp_111','/client\images\default.gif'             union all
select 'lnsx','10','sp_113','/client\images\default.gif'         union all
select '中国红','2','sp_105','/client\images\default.gif'         union all
select '中国红','4','sp_107','/client\images\default.gif'         union all
select '中国红','2','sp_108','/client\images\default.gif'         union all
select '中国红','5','sp_123','/client\images\default.gif' SELECT dbo.test.username,dbo.test.jfsl,dbo.test.spbh,dbo.test.txlj FROM dbo.test INNER JOIN (
SELECT username,MAX(jfsl) AS num FROM dbo.test GROUP BY username) AS a ON a.username = dbo.test.username AND a.num = dbo.test.jfsl

Answer 6

6#的多了一列，此外没有考虑如果有两个相同的最大值这样最后的结果里面有可能有重复的记录，我写了一个看楼主觉得如何select distinct a.username,a.JFSL,t.SPBH,t.TXLJ
FROM
(
select username, MAX(JFSL) as JFSL from test
group by username
)a
inner join test t
on a.username=t.username and a.JFSL=t.JFSL

Answer 7

select t.* from test t where JFSL = (select max(JFSL) from test where USERNAME = t.USERNAME)
这句很神奇，百思不得其解。谁帮我分析一下啊...

Answer 8

顶二楼select t.* from test t where JFSL = (select max(JFSL) from test where USERNAME = t.USERNAME)select t.* from test t where not exists (select 1 from test where USERNAME = t.USERNAME and JFSL > t.JFSL)
--也可以使用连接
select distinct a.username,a.JFSL,t.SPBH,t.TXLJ
FROM
(
    select username, MAX(JFSL) as JFSL from test
    group by username
)a
inner join test t
on a.username=t.username and a.JFSL=t.JFSL

Answer 9

where USERNAME = t.USERNAME  相当于group by USERNAME