表中的数据如下:
product表
+-----+-------------+
| pid | pname |
+-----+-------------+
| 1 | 诺基亚N78 |
| 2 | 诺基亚 5800 |
| 3 | W595c |
| 4 | W580c |
+-----+-------------+
productprice表
+----+------+---------------------------+--------+
| id | pid | shop | price |
+----+------+---------------------------+--------+
| 1 | 1 | ZOL指定诚信商010-82536615 | 1700.0 |
| 2 | 1 | 淘宝网店 | 1580.0 |
| 3 | 1 | 易趣网店 | 1650.0 |
| 4 | 3 | 太平洋报价 | 1588.0 |
| 5 | 3 | 泡泡网报价 | 1680.0 |
| 6 | 3 | 天极报价 | 1650.0 |
+----+------+---------------------------+--------+要求:
用一条语句查询出所有产品的最高报价和最低报价,最高报价和最低报价的shop,以及对应的pid、pname。另请大侠指点我的语句为什么只能够查询出价格最高的商店名称,如下:select p.pid,p.pname,pt2.maxprice,pt1.minprice,pt1.shop_min,pt2.shop_max
from product p left join (
select pid,min(price) minprice,shop shop_min from
productprice t1 group by pid)pt1
on p.pid=pt1.pid
left join(
select pid,max(price) maxprice,shop shop_max from
productprice t2 group by pid)pt2
on p.pid=pt2.pid;查询结果:
+-----+-------------+----------+----------+---------------------------+---------
------------------+
| pid | pname | maxprice | minprice | shop_min | shop_max
|
+-----+-------------+----------+----------+---------------------------+---------
------------------+
| 1 | 诺基亚N78 | 1700.0 | 1580.0 | ZOL指定诚信商010-82536615 | ZOL指定
诚信商010-82536615 |
| 2 | 诺基亚 5800 | NULL | NULL | NULL | NULL
|
| 3 | W595c | 1680.0 | 1588.0 | 太平洋报价 | 太平洋报
价 |
| 4 | W580c | NULL | NULL | NULL | NULL
|
+-----+-------------+----------+----------+---------------------------+---------
在线等!谢谢!
product表
+-----+-------------+
| pid | pname |
+-----+-------------+
| 1 | 诺基亚N78 |
| 2 | 诺基亚 5800 |
| 3 | W595c |
| 4 | W580c |
+-----+-------------+
productprice表
+----+------+---------------------------+--------+
| id | pid | shop | price |
+----+------+---------------------------+--------+
| 1 | 1 | ZOL指定诚信商010-82536615 | 1700.0 |
| 2 | 1 | 淘宝网店 | 1580.0 |
| 3 | 1 | 易趣网店 | 1650.0 |
| 4 | 3 | 太平洋报价 | 1588.0 |
| 5 | 3 | 泡泡网报价 | 1680.0 |
| 6 | 3 | 天极报价 | 1650.0 |
+----+------+---------------------------+--------+要求:
用一条语句查询出所有产品的最高报价和最低报价,最高报价和最低报价的shop,以及对应的pid、pname。另请大侠指点我的语句为什么只能够查询出价格最高的商店名称,如下:select p.pid,p.pname,pt2.maxprice,pt1.minprice,pt1.shop_min,pt2.shop_max
from product p left join (
select pid,min(price) minprice,shop shop_min from
productprice t1 group by pid)pt1
on p.pid=pt1.pid
left join(
select pid,max(price) maxprice,shop shop_max from
productprice t2 group by pid)pt2
on p.pid=pt2.pid;查询结果:
+-----+-------------+----------+----------+---------------------------+---------
------------------+
| pid | pname | maxprice | minprice | shop_min | shop_max
|
+-----+-------------+----------+----------+---------------------------+---------
------------------+
| 1 | 诺基亚N78 | 1700.0 | 1580.0 | ZOL指定诚信商010-82536615 | ZOL指定
诚信商010-82536615 |
| 2 | 诺基亚 5800 | NULL | NULL | NULL | NULL
|
| 3 | W595c | 1680.0 | 1588.0 | 太平洋报价 | 太平洋报
价 |
| 4 | W580c | NULL | NULL | NULL | NULL
|
+-----+-------------+----------+----------+---------------------------+---------
在线等!谢谢!
left join
(select pid,max(price) as ma,min(price) as mi
from productprice group by pid) b
on a.pid=b.pid and a.price=b.maleft join productprice c
on c.pid=b.pid and c.price=b.maleft join product d
on d.pid=b.pid
select p.pid,p.pname,rs1.price as maxprice,rs2.price as minprice,rs2.shop as shop_min,rs2.shop as shop_max
from
product p left join
(
select a.* from productprice a where not exists (select 1 from productprice b where b.pid=a.pid and b.price>a.price)
) rs1 on p.pid=rs1.pid
left join
select a.* from productprice a where not exists (select 1 from productprice b where b.pid=a.pid and b.price<a.price)
) rs2 on p.pid=rs2.pid
我运行了一下,结果是这样的:
+----+------+---------------------------+--------+------+-----------+
| id | pid | shop | price | pid | pname |
+----+------+---------------------------+--------+------+-----------+
| 1 | 1 | ZOL指定诚信商010-82536615 | 1700.0 | 1 | 诺基亚N78 |
| 2 | 1 | 淘宝网店 | 1580.0 | NULL | NULL |
| 3 | 1 | 易趣网店 | 1650.0 | NULL | NULL |
| 4 | 3 | 太平洋报价 | 1588.0 | NULL | NULL |
| 5 | 3 | 泡泡网报价 | 1680.0 | 3 | W595c |
| 6 | 3 | 天极报价 | 1650.0 | NULL | NULL |
+----+------+---------------------------+--------+------+-----------+
我要的是每件商品的最高价和最低价以及商店的名称。
from productprice group by pid) b
left join productprice a
on a.pid=b.pid and a.price=b.ma
left join productprice c
on c.pid=b.pid and c.price=b.mi left join product d
on d.pid=b.pid
select a.*,d.*,c.* from ((productprice a
left join
(select pid,max(price) as ma,min(price) as mi
from productprice group by pid) b
on a.pid=b.pid and a.price=b.ma )left join productprice c
on c.pid=b.pid and c.price=b.mi )left join product d
on d.pid=b.pid
where d.pid is not null
on c.pid=b.pid and c.price=b.ma ->
on c.pid=b.pid and c.price=b.mi
from product a,productprice b,
(select id,pid,shop,max(price) as max_price from productprice group by pid) c,
(select id,pid,shop,min(price) as max_price from productprice group by pid) d
where a.pid=b.pid and b.id=c.id and b.id=d.id