Code
drop table test create table test(产品 varchar(10),颜色 varchar(10),数量 int) insert test select '产品1','红色',123 insert test select '产品1','蓝色',126 insert test select '产品2','蓝色',103 insert test select '产品2','红色',NULL insert test select '产品2','红色',89 insert test select '产品1','红色',203 select 产品,sum(case 颜色 when '蓝色' then 数量 when '红色' then -数量 else 0 end) from test group by 产品 having sum(case 颜色 when '蓝色' then 数量 when '红色' then -数量 else 0 end)>0drop table testCodeproductid productname MaxPrice MinPrice MaxSeller MinSeller
----------- -------------------------------------------------- ----------- ----------- --------- ---------
1 mp5 700 600 seller1 seller2
2 eagle 500 500 seller1 seller1
3 awp 6000 4500 seller3 seller1
if object_id('[product]') is not null drop table [product]
go
create table [product]([productid] int,[productname] varchar(50))
insert [product]
select 1,'mp5' union all
select 2,'eagle' union all
select 3,'awp' union all
select 4,'m4a1' union all
select 5,'ak47'
if object_id('[sale]') is not null drop table [sale]
go
create table [sale]([saleid] int,[productid] int,[seller] varchar(7),[price] int)
insert [sale]
select 1,1,'seller1',700 union all
select 2,1,'seller2',600 union all
select 3,2,'seller1',500 union all
select 4,3,'seller1',4500 union all
select 5,3,'seller2',5500 union all
select 6,3,'seller3',6000
select * from product
select * from sale
select p.productid,p.productname,maxprice=max(price),minprice=min(price),
maxsaller=(select top 1 seller from sale where productid=p.productid order by price desc),
minsaller=(select top 1 seller from sale where productid=p.productid)
from sale s inner join product p on p.productid=s.productid
group by p.productid,p.productname