求一条sql语句，关于分组

Question

大家好，
　　我现在有个表，数据如下：
Tabl
cpha  col2  col3   cnum
a1    b1    c1     1
a1    d2    e2     2
a3    c3    f3     5
d4    d2    d1     2
a3    e2    e3     4我想通过一条sql语句来获取下面的数据，规则是根据cpha分组后，取cnum最大的一条。
cpha  col2  col3  cnum
a1    d2    e2     2
a3    c3    f3     5
d4    d2    d1     2

Answer 1

select cpha,col2,col3,max(cnum) cnum
from tabl
group by cpha,col2,col3

Answer 2

select a.*
from tabl a,
(
select   cpha,max(cnum)   cnum 
from   tabl 
group   by   cpha)b
where a.cpha=b.cpha and a.cnum=b.cnum

Answer 3

select t2.cpha,t2.col2,t2.col3,t1.cnum
from Tabl t2,(select cpha,max(cnum) cnum from Tabl) t1
where t1.cpha = t2.cpha and t1.cnum = t2.cnum

Answer 4

现把建表语句附上：create table mtest(cpha varchar2(20),col2 varchar2(20),col3 varchar2(20),cnum integer)
insert into mtest(cpha,col2,col3,cnum) values('a1','b1','c1',1);
insert into mtest(cpha,col2,col3,cnum) values('a1','b2','e2',2);
insert into mtest(cpha,col2,col3,cnum) values('a3','c3','f3',5);
insert into mtest(cpha,col2,col3,cnum) values('d4','d2','d1',2);
insert into mtest(cpha,col2,col3,cnum) values('a3','e2','e3',4);to hebo2005 ,你的执行结果有问题的了。

Answer 5

select a.cpha,a.col2,a.col3,max(a.cum) from (select  *from Tabl 
group by cpha) a 

Answer 6

第二句语句应该没问题的，不过你数据这么多，肯定会慢的
换种方式试试select a.*
from tabl a
where exists
(select 'x'
from
(select   cpha,max(cnum)   cnum 
from   tabl 
group   by   cpha)b
where a.cpha=b.cpha and a.cnum=b.cnum)

Answer 7

select t.* from
(
select 'a1' cpha,'b1' col2,'c1' col3,'1' cnum  from dual union all
select 'a1','d2','e2','2' from dual union all 
select 'a3','c3','f3','5' from dual union all 
select 'd4','d2','d1','2' from dual union all 
select 'a3','e2','e3','4' from dual 
) t
where not exists(select 1 from (
select 'a1' cpha,'b1' col2,'c1' col3,'1' cnum  from dual union all
select 'a1','d2','e2','2' from dual union all 
select 'a3','c3','f3','5' from dual union all 
select 'd4','d2','d1','2' from dual union all 
select 'a3','e2','e3','4' from dual 
) where cpha = t.cpha and cnum > t.cnum)--------------------------------------------------
    CPHA COL2 COL3 CNUM
1 a1 d2 e2 2
2 a3 c3 f3 5
3 d4 d2 d1 2

Answer 8

我上面的应该比较适合楼主你
再转一个乌龟的帖子给你，应该能找到你要的（MSSQL的）--按某一字段分组取最大(小)值所在行的数据
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          3           a3:a的第三个值
b          5           b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          1           b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          5           b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    1   a1--a的第一个值
a    3   a3:a的第三个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
goselect * , px = identity(int,1,1) into tmp from tbselect m.name,m.val,m.memo from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)drop table tb,tmp/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
goselect m.name,m.val,m.memo from
(
  select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
  select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)drop table tb/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/

Answer 9

select count(*) from
(
select * from clxx t
where not exists(select 1 from  clxx where cpha = t.cpha and LSTI > t.LSTI)
)a 
总共花： 13.297m  数据总数：679287　（与别的sql数据总数对不上）
select count(*)
from clxx a,
(
select   cpha,max(lsti)   cnum 
from   clxx 
group   by   cpha)b
where a.cpha=b.cpha and a.lsti=b.cnum
总共花：13.578m   数据总数：678933select count(*) from
(
select a.*
from clxx a
where exists
(select 'x'
from
(select   cpha,max(LSTI)   cnum 
from   clxx 
group   by   cpha)b
where a.cpha=b.cpha and a.LSTI=b.cnum)
)a
总共花：28.75   数据总数：678933

Answer 10

你用COUNT（*）
本身就费时的

Answer 11

在cpha列上需要索引,使用cpha分组查出最大的,然后再联查,应该是最快的。数据量太多的话,需要调节数据库的内存,提高高速缓冲区的命中率,避免重复读取硬盘.如果数据量实在太大,可以保存中间结果到临时表中。最应该优化的是你的数据结构,能减少数据量就使数据量尽可能少。 

Answer 12

select a.cpha,t.col2,t.col3,a.m from 
(select cpha ,max(cnum) as m  from temp group by cpha)a,
temp t
where t.CPHA=a.cpha(+)
and a.m=t.CNUM

Answer 13

SELECT CPHA, COL2, COL3, CNUM
FROM (
      SELECT 
             DENSE_RANK() OVER (PARTITION BY CPHA ORDER BY CNUM DESC) AS SEQ,
             MAX(CNUM) OVER (PARTITION BY CPHA ORDER BY CNUM) AS CNUM
             ,CPHA,COL2,COL3
      FROM   MTEST
      )
WHERE SEQ = 1

Answer 14

不用那么麻烦吧
SELECT 
CPHA,
MAX(COL2) KEEP(DENSE_RANK FIRST ORDER BY CNUM DESC) ,
MAX(COL3) KEEP(DENSE_RANK FIRST ORDER BY CNUM DESC) ,
MAX(CNUM) KEEP(DENSE_RANK FIRST ORDER BY CNUM DESC) 
FROM TABL 
GROUP BY CPHA

Answer 15

select * from Tabl where (cpha, cnum) in
(select cpha, max(cnum) from Tabl group by cpha)

Answer 16

在连接的条件上，是不是需要看表的结构呢？只有cpha,   max(cnum) 两个条件，就可以连接了？肯定是唯一的？

Answer 17

select cpha,col2,col3,cnum from 
(
select t.*,rank() over(partition by cpha order by cnum desc ) as rankRec from Tabl t
) R
where R.rankRec = 1;

Answer 18

我给的答案，如果有小的地方不合适，那么rank和dense_rank适当的替换就可以解决问题。ORACLE的rank函数是相当好用的东东，建议多多使用

Answer 19

popeye627您也太幽默了，好几亿的数据，要是都冰起来，奥运会完了也写不完程序啊

Answer 20

21楼的方案为标准方案,今天刚来就看到两个同样的问题. 解决方案就是ORACLE的分析函数, RANK(). 细节的楼主不妨查查手册吧. 语句不仅简洁而且执行效率高,尤其是数据量大的时候.select   cpha,col2,col3,cnum   from   
( 
select   t.*,rank()   over(partition   by   cpha   order   by   cnum   desc   )   as   rankRec   from   Tabl   t 
)   R 
where   R.rankRec   =   1;

Answer 21

不知wenwt7所指的临时表是什么? 不过通常来讲,能避免用临时表的最好不用,虽然某些时候临时表能帮上忙,但切不可滥用啊. 在这个SQL问题中,用临时表未免太小题大做了。

Answer 22

如果LZ不是一定要用Group By，也可以用其它方法的话，那我下面的这个方法也许能帮到你。先解释下，LZ你要的结果，可以通过下面3点的查询合并而得。
第一:有两行或两行以上的cpha字段值相等时,但cnum不等时,取其cnum字段值最大的一行.
第二:有两行或两行以上的cpha字段值相等时,且cnum相等时,取任意一行(默认取第一行).
第三:取cpha字段值没有相同的,即cpha字段值有且只有一行,那就取这一行.
先创建表：
create   table   Tabl(cpha   varchar(20),col2   varchar(20),col3   varchar(20),cnum integer) 
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tabl] ON [dbo].[Tabl] 
(
[cpha] ASC,
[cnum] ASC,
[col2] ASC,
[col3] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
insert   into   Tabl(cpha,col2,col3,cnum)   values('a1','b1','c1',1); 
insert   into   Tabl(cpha,col2,col3,cnum)   values('a1','b2','e2',2); 
insert   into   Tabl(cpha,col2,col3,cnum)   values('a3','c3','f3',5); 
insert   into   Tabl(cpha,col2,col3,cnum)   values('d4','d2','d1',2); 
insert   into   Tabl(cpha,col2,col3,cnum)   values('d4','w1','t2',2); 
insert   into   Tabl(cpha,col2,col3,cnum)   values('a3','e2','e3',4); 
insert   into   Tabl(cpha,col2,col3,cnum)   values('d5','w1','t2',4); 
查询语句如下：--第一：取cpha相同，cnum最大的一条
select  a.cpha,a.col2,a.col3 ,a.cnum    from   Tabl a,Tabl b
where (a.cnum>b.cnum and a.cpha=b.cpha)
union
--第二：取cpha相同,cnum相同的第一条(表要约束不能插入相同的行)
select top 1  a.cpha,a.col2,a.col3 ,a.cnum    from   Tabl a,Tabl b
where (a.cnum=b.cnum and a.cpha=b.cpha and a.col2<>b.col2 and a.col3<>b.col3)
union
--第三：取cpha没有相同的,即有且只有一行cpha字段值
select * from Tabl d 
where cpha in 
(
    select  a.cpha  from   Tabl a 
    group by a.cpha having count(*)=1
)
望各位多多批评指教！

Answer 23

SELECT a.CPHA,
(SELECT col2 FROM mtest WHERE CPHA=a.CPHA AND CNUM=a.CNUM)col2,
(SELECT col3 FROM mtest WHERE CPHA=a.CPHA AND CNUM=a.CNUM)col3,
a.CNUM
FROM(SELECT CPHA,Max(CNUM)CNUM FROM mtest GROUP BY CPHA)a这就是你想要的了

Answer 24

SELECT a.CPHA,
(SELECT col2 FROM mtest WHERE CPHA=a.CPHA AND CNUM=a.CNUM AND rowNum=1)col2,
(SELECT col3 FROM mtest WHERE CPHA=a.CPHA AND CNUM=a.CNUM AND rowNum=1)col3,
a.CNUM
FROM(SELECT CPHA,Max(CNUM)CNUM FROM mtest GROUP BY CPHA)a如果cpHA与CNUM有重复的话就要加个 AND rowNum=1 这样会确保col2与col3不会取不同行的记录

Answer 25

这样试试
select * from                                                                       
    (    
      select cpha,col2,col3,rank()over(partition by cpha order by cnum desc) cnumm from tabname
)where cnumm==1

Answer 26

select * from mtest
where cnum in(
select max(cnum) from mtest
group by cpha 
)