测试:
create table table1
(col1 tinyint ,
col2 char(4))
insert into e select 1,'aaa'
insert into e select 2,'aaa'
insert into e select 3 ,'bb'
insert into e select 4,'bb'
insert into e select 5,'dd'
insert into e select 6 ,'bb'
查询语句:
select col2 col1,count(*) as table1 from e group by col2
create table table1
(col1 tinyint ,
col2 char(4))
insert into e select 1,'aaa'
insert into e select 2,'aaa'
insert into e select 3 ,'bb'
insert into e select 4,'bb'
insert into e select 5,'dd'
insert into e select 6 ,'bb'
查询语句:
select col2 col1,count(*) as table1 from e group by col2
join
(Select col2 ,count(col1) as cnt,max(col4) as col4 from table1 group by col2) b
on a.col2 = b.col2 and a.col4 = b.col4
答案不对。
txlicenhe(马可) 的有一点小小错误。改正一下是对的。
(col1 tinyint ,
col2 char(4),
col3 varchar(10),
col4 datetime)insert into table1 select 1,'aaa','aasdf','2003-1-1'
insert into table1 select 2,'aaa','ddgag','2003-2-1'
insert into table1 select 3 ,'bb','adsfab','2003-1-2'
insert into table1 select 4,'bb','aff','2002-1-1'
insert into table1 select 5,'dd','bbbn','2003-1-6'
insert into table1 select 6 ,'bb','bnv','2003-8-7'Select a.col2 as col1,b.cnt as col2,a.col3,b.col4 from table1 a
join
(Select col2 ,count(col1) as cnt,max(col4) as col4 from table1 group by col2) b
on a.col2 = b.col2 and a.col4 = b.col4col1 col2 col3 col4
---- ----------- ---------- ------------------------------------------------------
aaa 2 ddgag 2003-02-01 00:00:00.000
bb 3 bnv 2003-08-07 00:00:00.000
dd 1 bbbn 2003-01-06 00:00:00.000(所影响的行数为 3 行)