A SQL statement query problem (query minimum value) (urgent)

cool_mohr

To:99429499

Pay attention to on a.id=b.aid in我是杀人狂's sql

jak1984

No one seems to write like me

jak1984

If there are two pieces of data with the same price in Table B, how to display only one
Table B
－－－－－－－－－－－－－－
ID AID PRICE
1 1 30
2 1 32
3 1 26
4 1 26
------------------------------------------
donkey_ngacnis right
So the algorithm is improved to
select top 1 with ties A.id, A.name, B.id, B.AID, B.price
from @B B
join @A A on A.id=B.AID
order by price
I guarantee fast and accurate

mill1128

Well written, it’s OK, for me, hahaha

mill1128

I think so too. Hahaha

zhangli_37

select distinct * from a,b
where a.id=b.aid and
b.price = (select min(price) from b group by aid)
This is the best solution

danny1030

Are all the questions answered?

yangzuwang

Which answer is correct, I haven’t run a correct one yet

The effect to be achieved should be to find out the lowest price of each product in Table A!

vcblue

select a.aid as id,a.name,b.id,b.aid,b.price from a
inner join b on a.aid=b.aid
where b.price in(select min(price) from b)

njzq_258

Use view

create view price_view
as
select b.aid,min=min(b.price) from b group by b.aid

select a.name,a.id,price_view.aid,min from a,price_view where a.id=price_view.aid