create table table1(
columnA char(1),
columnB char(1),
columnC int,
columnD varchar(10),
)insert into table1 values('a','b',1,'apple')
insert into table1 values('a','b',2,'orange')
insert into table1 values('a','b',3,'grape')
insert into table1 values('a','b',4,'banana')
insert into table1 values('a','b',5,'cherry')
insert into table1 values('a','b',6,'pear')
insert into table1 values('a','b',7,'peach')
insert into table1 values('c','d',8,'chestnut')
insert into table1 values('c','d',2,'peanut')
insert into table1 values('c','d',3,'tomato')
insert into table1 values('e','f',1,'pimiento')select t1.* from table1 t1
where not exists(
select 1 from table1 t2
where t2.columnA=t1.columnA and t1.columnB=t2.columnB
and t2.columnC>t1.columnC
having count(*)>1
)
columnA char(1),
columnB char(1),
columnC int,
columnD varchar(10),
)insert into table1 values('a','b',1,'apple')
insert into table1 values('a','b',2,'orange')
insert into table1 values('a','b',3,'grape')
insert into table1 values('a','b',4,'banana')
insert into table1 values('a','b',5,'cherry')
insert into table1 values('a','b',6,'pear')
insert into table1 values('a','b',7,'peach')
insert into table1 values('c','d',8,'chestnut')
insert into table1 values('c','d',2,'peanut')
insert into table1 values('c','d',3,'tomato')
insert into table1 values('e','f',1,'pimiento')select t1.* from table1 t1
where not exists(
select 1 from table1 t2
where t2.columnA=t1.columnA and t1.columnB=t2.columnB
and t2.columnC>t1.columnC
having count(*)>1
)
columnA char(1),
columnB char(1),
columnC int,
columnD varchar(10),
)insert into table1 values('a','b',1,'apple')
insert into table1 values('a','b',2,'orange')
insert into table1 values('a','b',3,'grape')
insert into table1 values('a','b',4,'banana')
insert into table1 values('a','b',5,'cherry')
insert into table1 values('a','b',6,'pear')
insert into table1 values('a','b',7,'peach')
insert into table1 values('c','d',8,'chestnut')
insert into table1 values('c','d',1,'peanut')
insert into table1 values('c','d',2,'tomato')
insert into table1 values('e','f',1,'pimiento')select t1.* from table1 t1
where not exists(
select 1 from table1 t2
where t2.columnA=t1.columnA and t1.columnB=t2.columnB
and t2.columnC<t1.columnC
having count(*)>1
)
--
columnA columnB columnC columnD
------- ------- ----------- ----------
a b 1 apple
a b 2 orange
c d 1 peanut
c d 2 tomato
e f 1 pimiento(所影响的行数为 5 行)
create table table1(columnA varchar(2),columnB varchar(2),columnC int,columnD varchar(20))
insert into table1 select 'a','b',1,rtrim('apple ')
insert into table1 select 'a','b',2,rtrim('orange ')
insert into table1 select 'a','b',3,rtrim('grape ')
insert into table1 select 'a','b',4,rtrim('banana ')
insert into table1 select 'a','b',5,rtrim('cherry ')
insert into table1 select 'a','b',6,rtrim('pear ')
insert into table1 select 'a','b',7,rtrim('peach ')
insert into table1 select 'c','d',1,rtrim('chestnut')
insert into table1 select 'c','d',2,rtrim('peanut ')
insert into table1 select 'c','d',3,rtrim('tomato ')
insert into table1 select 'e','f',1,rtrim('pimiento')
--执行查询
select
a.*
from
table1 a
where
a.columnC in(select
top 2 columnC
from
table1
where
columnA=a.columnA and columnB=a.columnB
order by
columnC)
order by
a.columnA,a.columnB
--输出结果
columnA columnB columnC columnD
------- ------- ------- --------
a b 1 apple
a b 2 orange
c d 1 chestnut
c d 2 peanut
e f 1 pimiento
where not exists(
select 1 from table1 t2
where t2.columnA=t1.columnA and t1.columnB=t2.columnB
and t2.columnC<t1.columnC
having count(*)>1
)
order by columnA,columnB,columnC --这里
GROUP 以及这中单表的自连接查询 不会
--order by 加在什么地方啊??
--你要按什么条件排序??
”a.columnC in(select top 2 columnC “这个有问题。
还有一个columnD列, 你要除group by 外的其它列都比较了才行。
to 楼主:”columnC 不是int类型 是datetime 类型呢“,
select a.*
from table1 a
where a.columnC in(
select top 2 columnC
from table1
where columnA=a.columnA and columnB=a.columnB
order by columnC, columnD)
and a.columnD in(
select top 2 columnD
from table1
where columnA=a.columnA and columnB=a.columnB
order by columnC, columnD)
order by a.columnA,a.columnB
from table1 where columnA=a.columnA and columnB=a.columnB
order by columnc)order by a.columnA,a.columnB
-----------------------------------------------------------------------------
我觉得所谓group by是楼主在表达需求方面的一个手误,从输出的需求而言,完全可以理解为输出同一个columnA,columnB的前两条记录。