wherestr ="where titles like '%A%'"
Select * From opportunity Where id in (Select Max(id) From opportunity where " + wherestr + " Group by accountid)例如有10条信息
id accountid titles
1 1 AAA..
2 1 AAA..
3 1 AAA..
4 1 AAA..
5 3 AAA..
6 3 AAA..
7 3 AAA...
8 3 AAA..
9 2 AAA...
10 2 AAA...查询出来的是 ,重复的只显示一条
4 1 AAA...
8 3 AAA...
10 2 AAA...
现在郁闷了 客户要求如果有重复的话,就显示重复记录的前三条信息,也就是显示成
以下
4 1 AAA...
3 1 AAA...
2 1 AAA...
8 3 AAA...
7 3 AAA...
6 3 AAA...
10 2 AAA...
9 2 AAA...求高手帮我啊
Select * From opportunity Where id in (Select Max(id) From opportunity where " + wherestr + " Group by accountid)例如有10条信息
id accountid titles
1 1 AAA..
2 1 AAA..
3 1 AAA..
4 1 AAA..
5 3 AAA..
6 3 AAA..
7 3 AAA...
8 3 AAA..
9 2 AAA...
10 2 AAA...查询出来的是 ,重复的只显示一条
4 1 AAA...
8 3 AAA...
10 2 AAA...
现在郁闷了 客户要求如果有重复的话,就显示重复记录的前三条信息,也就是显示成
以下
4 1 AAA...
3 1 AAA...
2 1 AAA...
8 3 AAA...
7 3 AAA...
6 3 AAA...
10 2 AAA...
9 2 AAA...求高手帮我啊
create table #tb(id int,accountid int,titles varchar(10))
insert #tb(id,accountid,titles)
select '1','1','AAA' union all
select '2','1','AAA' union all
select '3','1','AAA' union all
select '4','1','AAA' union all
select '5','3','AAA' union all
select '6','3','AAA' union all
select '7','3','AAA' union all
select '8','3','AAA' union all
select '9','2','AAA' union all
select '10','2','AAA'
go
--执行测试语句
select t.id,t.accountid,t.titles
from #tb t
where id in(
select top 3 id from #tb where accountid = t.accountid order by id desc
)
order by accountid,id desc
go
--删除测试环境
drop table #tb
go
/*--测试结果
id accountid titles
----------- ----------- ----------
4 1 AAA
3 1 AAA
2 1 AAA
10 2 AAA
9 2 AAA
8 3 AAA
7 3 AAA
6 3 AAA(所影响的行数为 8 行)
*/
insert @Test(id, accountid, titles)
select 1, 1, 'AAA' union all
select 2, 1, 'AAA' union all
select 3, 1, 'AAA' union all
select 4, 1, 'AAA' union all
select 5, 3, 'AAA' union all
select 6, 3, 'AAA' union all
select 7, 3, 'AAA' union all
select 8, 3, 'AAA' union all
select 9, 2, 'AAA' union all
select 10, 2, 'BBB' union all
select 11, 2, 'AAA'
Select * From @Test a Where id in (Select top 3 id From @Test where accountid=a.accountid and titles like '%A%' order by id desc)
/*
id accountid titles
2 1 AAA
3 1 AAA
4 1 AAA
6 3 AAA
7 3 AAA
8 3 AAA
9 2 AAA
11 2 AAA
*/
select * from opportunity as a where 3 > (select count(*) from opportunity where accountid = a.accountid and id > a.id)
where (select count(*) from opportunity where accountid=a.accountid
and id>a.id )<3
order by accountid,id desc
insert @Test select 1,1,'AAA'
union all select 2,1,'AAA'
union all select 3,1,'AAA'
union all select 4,1,'AAA'
union all select 5,3,'AAA'
union all select 6,3,'AAA'
union all select 7,3,'AAA'
union all select 8,3,'AAA'
union all select 9,2,'AAA'
union all select 10,2,'AAA'
--select * from @Test select ID,Accountid,Titles
from @Test
where ID in (select MAX(ID) from @Test group by Accountid)
order by ID/*
4 1 AAA
8 3 AAA
10 2 AAA
*/
用下面的script可以得到.declare @Test table (ID int,Accountid int,Titles varchar(10))
insert @Test select 1,1,'AAA'
union all select 2,1,'AAA'
union all select 3,1,'AAA'
union all select 4,1,'AAA'
union all select 5,3,'AAA'
union all select 6,3,'AAA'
union all select 7,3,'AAA'
union all select 8,3,'AAA'
union all select 9,2,'AAA'
union all select 10,2,'AAA'
--select * from @Test select ID,Accountid,Titles
from @Test as a
where ID in
(select top 3 ID
from @Test
where Accountid=a.Accountid
order by ID desc )
order by Accountid ,ID desc