使用select o.productid,p.supplierid,sum(quantity)
from orderdetails o inner join products p
on(o.productid=p.productid)
group by o.productid,p.supplierid
order by p.supplierid,sum(quantity) desc 返回了一个如下图的表:
现我想用子查询查出supplierid为1quantity最大的一列,然后supplierid为2quantity最大的一列,然后supplierid为3quantity最大的一列请问用子查询怎么写:
from orderdetails o inner join products p
on(o.productid=p.productid)
group by o.productid,p.supplierid
order by p.supplierid,sum(quantity) desc 返回了一个如下图的表:
现我想用子查询查出supplierid为1quantity最大的一列,然后supplierid为2quantity最大的一列,然后supplierid为3quantity最大的一列请问用子查询怎么写:
from tb
group by supplienrid
select o.productid,p.supplierid,max(quantity) as 最大值
from
orderdetails o
inner join
products p
on(o.productid=p.productid)
group by o.productid,p.supplierid
order by p.supplierid,最大值 desc
--try
create table T(
ID1 int,
ID2 int
)insert T select 1,10
insert T select 1,9
insert T select 1,50insert T select 2,55
insert T select 2,10
insert T select 2,10select * from T as tmp
where not exists(select * from T where ID1=tmp.ID1 and ID2>tmp.ID2)
我要返回的是这种的productid supplierid 无列名
2 1 1057
65 2 745
7 3 763
10 4 742
.... .... .....
.... .... .....
.... .... .....
.... .... .....
.... .... .....
.... .... .....
--trycreate table T(
productid int,
supplierid int
)
insert T select 2,1
insert T select 65,2
insert T select 7,3
insert T select 10,4select tmp.*,
[最大]=(select max(productid) from T where supplierid=tmp.supplierid)
from T as tmp
比如:
select o.productid,p.supplierid,max(quantity) from
(
select o.productid,p.supplierid,sum(quantity)
from orderdetails o inner join products p
on(o.productid=p.productid)
group by o.productid,p.supplierid
order by p.supplierid,sum(quantity) desc
)a
'''''''''''''''''''''''下面就不知道怎么写了,谢谢大家的关注
from
(
select o.productid,p.supplierid,sum(quantity) as quantity
from orderdetails o
inner join products p on o.productid=p.productid
group by o.productid,p.supplierid
order by p.supplierid,sum(quantity) desc
)a
group by supplierid