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

啊啦是奶牛

should be
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.aid and a.price <= b.price

我是杀人狂

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

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


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 A.ID, A.NAME, B.ID, B.AID, B.PRICE from a a
join b b on a.id = b.aid
where b.price in (select min (price) from b b group by b.aid)
drop table a
drop table b
result:
ID NAME ID AID PRICE
----------- ---------- ----------- ----------- ------- ----
1 DEMO 3 1 26

(1 row affected)

xx521xx

select a. *, t. * fro a,
(
  select m. * from b m,
  (select aid, min (price) as price from b) n
  where m.aid = n.aid and m.price = n.price
) t
where a.id = t.aid

wgfxman

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 A.id AS 'A.ID', A.name AS 'A.NAME', MAX (B.id) AS 'B.ID', B.aid as 'B.AID', MIN (B.price) AS 'B.PRICE'
from B LEFT join A on A.id = B.aid
GROUP BY A.id, A.name, B.aid

xx521xx

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

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


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 a. *, t. * from a,
(
  select m. * from b m,
  (select aid, min (price) as price from b group by aid) n
  where m.aid = n.aid and m.price = n.price
) t
where a.id = t.aid

drop table a, b

id name id aid price
----------- ---------- ----------- ----------- ------- ----
1 DEMO 3 1 26

(1 row affected)

donkey_ngacn

If there are two data in table B with the same price, why only one data is displayed?
Form B
------------------------------------------
ID AID PRICE
1 1 30
2 1 32
3 1 26
4 1 26

wgfxman

select M.id AS 'A.ID', M.name AS 'A.NAME',
M.aid as 'B.AID', B.id AS 'B.ID', M.price AS 'B.PRICE' FROM
(select A.id, A.name,
B.aid, MIN (B.price) price
from B LEFT join A on A.id = B.aid
GROUP BY A.id, A.name, B.aid) M LEFT JOIN B ON M.aid = B.aid AND M.price = B.price

wgfxman

select M.id AS 'A.ID', M.name AS 'A.NAME',
M.aid as 'B.AID', MAX (B.id) AS 'B.ID', M.price AS 'B.PRICE' FROM
(select A.id, A.name,
B.aid, MIN (B.price) price
from B LEFT join A on A.id = B.aid
GROUP BY A.id, A.name, B.aid) M LEFT JOIN B ON M.aid = B.aid AND M.price = B.price
GROUP BY M.id, M.name, M.aid, M.price

donkey_ngacn

Still show two records

id name id aid price
----------- ---------- ----------- ----------- ------- ----
1 DEMO 3 1 26
1 DEMO 4 1 26

I just want to show one

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
union all select 4, 1, 26

select * from A
inner join
(
select max (ID) as ID, AID, min (PRICE) as PRICE from B group by AID
) B on A.ID = B.AID

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

(1 row (s) affected)