declare @tempTable table (
rowID int
, groupName varchar(10)
, value int
, userID int
)insert into
@tempTable
select
1, 'a', 10, 32
union
select
2, 'a', 43, 21
union
select
3, 'a', 3, 55
union
select
4, 'b', 10, 35
union
select
5, 'c', 10, 67
union
select
6, 'c', 2, 22
-- ---------------------------
select
*
from
@tempTable数据表如下:
rowID groupName value userID
1 a 10 32
2 a 43 21
3 a 3 55
4 b 10 35
5 c 10 67
6 c 2 22
想要得到的结果:
rowID groupName value userID rowCount
3 a 3 55 3
4 b 10 35 1
6 c 2 22 2
即以groupName分组,取value列的最小值,并上行ID(非重复)要得这样的结果很简单,要请教的是如何取到上表的结果。
groupName value rowCount
a 3 3
b 10 1
c 2 2多谢!
rowID int
, groupName varchar(10)
, value int
, userID int
)insert into
@tempTable
select
1, 'a', 10, 32
union
select
2, 'a', 43, 21
union
select
3, 'a', 3, 55
union
select
4, 'b', 10, 35
union
select
5, 'c', 10, 67
union
select
6, 'c', 2, 22
-- ---------------------------
select
*
from
@tempTable数据表如下:
rowID groupName value userID
1 a 10 32
2 a 43 21
3 a 3 55
4 b 10 35
5 c 10 67
6 c 2 22
想要得到的结果:
rowID groupName value userID rowCount
3 a 3 55 3
4 b 10 35 1
6 c 2 22 2
即以groupName分组,取value列的最小值,并上行ID(非重复)要得这样的结果很简单,要请教的是如何取到上表的结果。
groupName value rowCount
a 3 3
b 10 1
c 2 2多谢!
from (select groupname, min(value) as value, count(*) as rowcount
from @temptable) a
join @temptable b on a.groupname = b.groupname and a.value = b.value
groupname,min(value),count(*)
from
@tempTable
group by groupname
rowID int
, groupName varchar(10)
, value int
, userID int
)insert into @tempTable
select 1, 'a', 10, 32
union select 2, 'a', 43, 21
union select 3, 'a', 3, 55
union select 4, 'b', 10, 35
union select 5, 'c', 10, 67
union select 6, 'c', 2, 22
-- ---------------------------
select * from @tempTableselect b.*, a.[rowcount]
from (select groupname, min(value) as value, count(rowid) as [rowcount]
from @temptable
group by groupname) a
left join @temptable b on a.groupname = b.groupname and a.value = b.value
declare @tempTable table (
rowID int
, groupName varchar(10)
, value int
, userID int
)insert into @tempTable
select 1, 'a', 10, 32
union select 2, 'a', 43, 21
union select 3, 'a', 3, 55
union select 4, 'b', 10, 35
union select 5, 'c', 10, 67
union select 6, 'c', 2, 22
-- ---------------------------
select * from @tempTableselect b.*, a.[rowcount]
from (select groupname, min(value) as value, count(rowid) as [rowcount]
from @temptable
group by groupname) a
left join @temptable b on a.groupname = b.groupname and a.value = b.value
/*
rowID groupName value userID rowCount
3 a 3 55 3
4 b 10 35 1
6 c 2 22 2
*/
a 3 3
b 10 1
c 2 2看楼主的数据似乎不是行id,应该时统计数吧?
rowID int
, groupName varchar(10)
, value int
, userID int
)insert into @tempTable
select 1, 'a', 10, 32
union select
2, 'a', 43, 21
union select
3, 'a', 3, 55
union select
4, 'b', 10, 35
union select
5, 'c', 10, 67
union select
6, 'c', 2, 22Select *,
(Select Count(*) from @tempTable Where groupName=A.groupName And rowID<=A.rowID) As [rowCount]
from @tempTable A
Where Not Exists(Select * from @tempTable Where groupName=A.groupName And value<A.value )
--結果
/*
rowID groupName value userID rowCount
3 a 3 55 3
4 b 10 35 1
6 c 2 22 2
*/