一个SQL语句查询问题（查询最小值）（急）

donkey_ngacn

两个表A,B
A有ID,NAME等字段
B有ID,AID,PRICE等字段
注：A.ID关联B.AID

我想按A分类查询B产品的最低价？

假如有数据：
A表
－－－－－－－－－－－－
ID    NAME
1      DEMO
2      DEMO2
------------------------

B表
－－－－－－－－－－－－－
ID       AID    PRICE
1         1      30
2         1      32
3         1      26
－－－－－－－－－－－－－
如何查询到以下结果
－－－－－－－－－－－－－－－
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,应该是最低价：
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