求一SQL语句，两条数据合并为一条
name subname price amount
a Aa 100 null
a Aa 90 5
结果
name subname price amount
a Aa 100 5
可能例子不是很恰当，但是我想要的效果就是这样的
求哪位大侠指教一下
[最优解释]
勉强给你拼一个，仅用于你这个例子：
select name ,subname,price,sum(amount) amount
from
(
select name,subname,price,0 as amount
from 表
where amount is null
union all
select name,subname,price, amount
from 表
where amount is not null
)
group by name ,subname,price
with tb as
(
select 'a' as name, 'Aa' as subname, 90 as price, 5 as amount
union all
select 'a', 'Aa', 100, null
)
select name, subname, max(price), max(amount)
from tb
group by name, subname
declare @test table(name varchar(1), subname varchar(2), price int, amount int)
insert into @test
select 'a', 'Aa', 100, null union all
select 'a', 'Aa', 90, 5
select name,
subname,
price=(select top 1 price from @test
where t.name=name and t.subname=subname order by price desc),
amount=(select top 1 amount from @test
where t.name=name and t.subname=subname order by amount desc)
from (select distinct name,subname from @test) t
/*
name subname price amount
---- ------- ----------- -----------
a Aa 100 5
*/
declare @test table(name varchar(1), subname varchar(2), price int, amount int)
insert into @test
select 'a', 'Aa', 100, null union all
select 'a', 'Aa', 90, 5
select name,subname,MAX(price) price,MAX(amount) amount from @test group by name,subname
