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

donkey_ngacn

Two tables A, B
A has fields like ID, NAME, etc.
B has fields such as ID, AID, PRICE
Note: A.ID is related to B.AID

I want to find the lowest price of product B by category A?

If there is data:
Form A
------------------------------------------
ID NAME
1 DEMO
2 DEMO2
------------------------

Form B
------------------------------------------
ID AID PRICE
1 1 30
2 1 32
3 1 26
------------------------------------------
How to find the following results
------------------------------------------
A.ID A.NAME B.ID B.AID B.PRICE
1 DEMO 3 1 26

我是杀人狂

select A.ID, A.NAME, B.ID, B.AID, B.PRICE from tablea a join tableb b on a.id = b.aid where b.price in (select min (price) from tableb group by aid )

mydriver

SELECT *
FROM A
INNER JOIN B
ON a.id = b.aid
WHERE b.price =
(SELECT MAX (prics)
FROM B bx
WHERE b.aid = bx.aid)

mydriver

Sorry, it should be the lowest price:
SELECT *
FROM A
INNER JOIN B
ON a.id = b.aid
WHERE b.price =
(SELECT MIN (prics)
FROM B bx
WHERE b.aid = bx.aid)

cv4bf5d65fd

create table A (id int, name varchar (10))

insert into a (ID, NAME)
select 1, 'DEMO'
union all
select 2, 'DEMO2'
select * from a

create table B (id int, aid int, price int)

insert into b (id, aid, price)
select 1,1,30
union all
select 2,1,32
union all
select 3,1,26

---
select distinct *
from a, b
where a.id = (select max (id) from a) and b.id = (select max (id) from b)

michealhenry

select *
from a inner join b t1 on a.id = t1.id
where not exists (select 1 from b where aid = t1.aid and price <t1.price)

cv4bf5d65fd

select distinct *
from a, b
where a.id = b.aid and b.price = (select min (price) from b)

fanfan1980

create table A (ID int, Name varchar (10))
insert A select 1, 'DEMO'
union all select 2, 'DEMO2'

create table B (ID int, AID int, PRICE int)
insert B select 1, 1, 30
union all select 2, 1, 32
union all select 3, 1, 26

select * from A
inner join
(
select * from B as tmpB
where not exists (select 1 from B where AID = tmpB.AID and PRICE <tmpB.PRICE)
) B on A.ID = B.AID

--result
ID Name ID AID PRICE
----------- ---------- ----------- ----------- ------- ----
1 DEMO 3 1 26

(1 row (s) affected)

啊啦是奶牛

create table A (id int identity (1,1), name varchar (10))
create table B (id int identity (1,1), aid int, price varchar (10))
insert into a
select 'demo1'
union all select 'demo2'
insert into b
 select 1, '30 'union all
select 1, '32 'union all
select 1, '36 '
select a.id, a.name, a.bid, a.aid, a.price from (select a.id, a.name, b.id as bid, b.aid, b.price from A a inner join B b on a.id = b.aid) a, b where a.id = b.id and a.price <= b.price
drop table a
drop table b

waxiaoning

select a. *, b. * from
a join b on a.id = b.aid
join (select aid, min (price) as price from b group by aid) c on b.aid = c.aid and b.price = c.price