一个SQL语句查询问题（查询最小值）（急）
两个表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
[解决办法]
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
[解决办法]
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
[解决办法]
应该是
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 行）
[解决办法]
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
[解决办法]
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
[解决办法]
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 行）
[解决办法]
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
[解决办法]
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
[解决办法]
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)
