我现在有这样一个表
ID name amount
1 a 5
1 a 3
1 a 7
2 b 9
2 b 2
3 c 5
3 c 7其中ID和Name是一一对应的
现在要达到的效果是
ID name amount
1 a 3
2 b 2
3 c 5也就是列出最小的数值
请高手帮忙,小弟先谢了啊
ID name amount
1 a 5
1 a 3
1 a 7
2 b 9
2 b 2
3 c 5
3 c 7其中ID和Name是一一对应的
现在要达到的效果是
ID name amount
1 a 3
2 b 2
3 c 5也就是列出最小的数值
请高手帮忙,小弟先谢了啊
create table #tb(id int,[name] varchar(10),amount int)
insert #tb select '1','a','9'
union all select '1','a','2'
union all select '1','c','5'
union all select '2','b','7'
union all select '2','b','5'
union all select '3','c','3'
union all select '3','b','7'
select * from #tbselect id,name, min(amount) amount from #tb group by id,namedrop table #tb1 a 2
2 b 5
3 b 7
1 c 5
3 c 3
7楼的,你这正确?
select 1,'a',5
union all
select 1,'a',3
union all
select 1,'a',7
union all
select 2,'b',9
union all
select 2,'b',2
union all
select 3,'c',5
union all
select 3,'c',7select *
from #a a
where amount in (select min(amount)
from #a
where id=a.id)
(
id int ,
name varchar(20) ,
amount int
)insert tb1 values (1,'a',5)
insert tb1 values (1,'a',3)
insert tb1 values (1,'a',7)
insert tb1 values (2,'b',9)
insert tb1 values (2,'b',2)
insert tb1 values (3,'c',5)
insert tb1 values (3,'c',7) select a.* from tb1 a ,(select name from tb1 group by name) b ,(select name,min(amount) as amount from tb1 group by name) c
where a.name = b.name and b.name = c.name and a.amount = c.amount id name amount
----------- -------------------- -----------
1 a 3
2 b 2
3 c 5(所影响的行数为 3 行)LZ看看这样可以吗?那个表要是有个主键列就好了
/*
我现在有这样一个表
ID name amount
1 a 5
1 a 3
1 a 7
2 b 9
2 b 2
3 c 5
3 c 7 其中ID和Name是一一对应的
现在要达到的效果是
ID name amount
1 a 3
2 b 2
3 c 5
*/create table #table ([ID] int ,[name] varchar(5),amount int)insert into #table values('1','a','5')
insert into #table values('1','a','3')
insert into #table values('1','a','7')
insert into #table values('2','b','9')
insert into #table values('2','b','2')
insert into #table values('3','c','5')
insert into #table values('3','c','7')select [ID],[name],min(amount) amount from #table group by [ID],[name]ID name amount
----------- ----- -----------
1 a 3
2 b 2
3 c 5(所影响的行数为 3 行)
declare @B table (id int , b int)
insert into @B
select 1,1 union all
select 1,2 union all
select 2,5 union all
select 2,3 union all
select 2,2select id,min(b) as b from @B group by id/*
id b
1 1
2 2
create table #tb(id int,[name] varchar(10),amount int)
insert #tb select '1','a','9'
union all select '1','a','2'
union all select '1','c','5'
union all select '2','b','7'
union all select '2','b','5'
union all select '3','c','3'
union all select '3','b','7'
select * from #tbselect *
from #tb a
where amount in (select min(amount)
from #tb
where id=a.id)id name amount
----------- ---------- -----------
1 a 2
2 b 5
3 c 3(所影响的行数为 3 行)