表内容如下:
字段1,字段2
abc , 001
wtq , 001
jaq , 001
xva , 002
lko , 002
cru , 003
wer , 003
utd , 003要求字段2相同编号最多显示两条,查询结果如下:
字段1,字段2
abc , 001
wtq , 001
xva , 002
lko , 002
cru , 003
wer , 003另,本人使用ACCESS数据库
字段1,字段2
abc , 001
wtq , 001
jaq , 001
xva , 002
lko , 002
cru , 003
wer , 003
utd , 003要求字段2相同编号最多显示两条,查询结果如下:
字段1,字段2
abc , 001
wtq , 001
xva , 002
lko , 002
cru , 003
wer , 003另,本人使用ACCESS数据库
create table tb(字段1 varchar(10),字段2 varchar(10))
insert into tb values('abc' , '001')
insert into tb values('wtq' , '001')
insert into tb values('jaq' , '001')
insert into tb values('xva' , '002')
insert into tb values('lko' , '002')
insert into tb values('cru' , '003')
insert into tb values('wer' , '003')
insert into tb values('utd' , '003')
go--取按字段2分组后字段1出现的先后顺序取前两个。
select a.* from tb a where 字段1 in (select top 2 字段1 from tb where 字段2 = a.字段2) order by a.字段2
/*
字段1 字段2
---------- ----------
abc 001
wtq 001
xva 002
lko 002
cru 003
wer 003(所影响的行数为 6 行)
*/--取按字段2分组后字段1最大的两个
select a.* from tb a where 2 > (select count(*) from tb where 字段2 = a.字段2 and 字段1 > a.字段1 ) order by a.字段2,a.字段1
select a.* from tb a where 字段1 in (select top 2 字段1 from tb where 字段2=a.字段2 order by 字段1 desc) order by a.字段2,a.字段1
select a.* from tb a where exists (select count(*) from tb where 字段2 = a.字段2 and 字段1 > a.字段1 having Count(*) < 2) order by a.字段2,a.字段1
/*
字段1 字段2
---------- ----------
jaq 001
wtq 001
lko 002
xva 002
utd 003
wer 003(所影响的行数为 6 行)
*/--取按字段2分组后字段1最小的两个
select a.* from tb a where 2 > (select count(*) from tb where 字段2 = a.字段2 and 字段1 < a.字段1 ) order by a.字段2,a.字段1
select a.* from tb a where 字段1 in (select top 2 字段1 from tb where 字段2=a.字段2 order by 字段1) order by a.字段2,a.字段1
select a.* from tb a where exists (select count(*) from tb where 字段2 = a.字段2 and 字段1 < a.字段1 having Count(*) < 2) order by a.字段2,a.字段1
/*
字段1 字段2
---------- ----------
abc 001
jaq 001
lko 002
xva 002
cru 003
utd 003(所影响的行数为 6 行)
*/drop table tb
declare @s table (字段1 varchar(3),字段2 varchar(3))
insert into @s
select 'abc','001' union all
select 'wtq','001' union all
select 'jaq','001' union all
select 'xva','002' union all
select 'lko','002' union all
select 'cru','003' union all
select 'wer','003' union all
select 'utd','003'--方法1:
select * from @s a
where (select count(distinct 字段1) from @s where 字段2=a.字段2 and 字段1<=a.字段1)<3--方法2:
select * from @s a
where 字段1 in(select top 2 字段1 from @s where 字段2=a.字段2 order by 字段1)
SELECT *
FROM tb a
WHERE 字段1 IN
(
SELECT TOP 2 字段1
FROM tb
WHERE 字段2=a.字段2
)
where 字段1 in(select top 2 字段1 from @s where 字段2=a.字段2 order by 字段1)
--这种access都不支持?
非常感谢pt1314917的两个方法都可以的了,但本人使用的是双表查询,那么双表时select * from @s a where,这里应该怎么写?已知:select * from @s1,@s2 a where是不行的,敬候解答
if object_id('表1') is not null
drop table 表1
go
create table 表1
(字段1 nvarchar(5),
字段2 int
)
insert into 表1 select 'abc',001
union all select 'wtq',001
union all select 'jaq',001
union all select 'xva',002
union all select 'lko',002
union all select 'cru',003
union all select 'wer',003
union all select 'utd',003
go
select * from 表1
----------------------
select * from 表1 a
where (select count(distinct 字段1) from 表1 where 字段2=a.字段2 and 字段1<=a.字段1)<3
field1,field2
abc , 001
wtq , 001
jaq , 001
xva , 002
lko , 002
cru , 003
wer , 003
utd , 003
xqx , 004表二 data2内容如下:
id,field2
1 , 001
2 , 002
3 , 003
4 , 004要求表data1中相同field2编号最多显示两条,查询结果应如下:
field1,field2
abc , 001
wtq , 001
xva , 002
lko , 002
cru , 003
wer , 003
xqx , 004其中表data1的field2与 表data2的field2是指相同的账号(条件可以写成:data1.field2=data2.field2);
最后要求查询结果按表data2的id排序
b.*
from
data2 a,data1 b
where
a.field2=b.field2
and
(select count(*) from data1 where field2=b.field2 and field1>=b.field1)<3
order by
b.field2,b.field1
楼主查询出来的结果都没有涉及到data2表噢,都是data1的字段啊,没必要关联data2