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

啊啦是奶牛

应该是
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
结果:
ID          NAME       ID          AID         PRICE      
----------- ---------- ----------- ----------- -----------
1           DEMO       3           1           26

（所影响的行数为 1 行）

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 行）

donkey_ngacn

假如B表中有个价格一样的两条数据，那怎么只显示一条
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

还是显示两条记录

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

我只想显示一条

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)