两个表:
1、产品表:product
字段:pid int auto_increment, //产品id
pname varchar (50) //产品名称2、报价表:productprice
字段:id int auto_increment, //报价单id
pid int , //产品id
shop varchar, //报价者
price float //价格用一条语句查询出所有产品的最高报价和最低报价,最高报价和最低报价的shop,以及对应的pid、pname。
1、产品表:product
字段:pid int auto_increment, //产品id
pname varchar (50) //产品名称2、报价表:productprice
字段:id int auto_increment, //报价单id
pid int , //产品id
shop varchar, //报价者
price float //价格用一条语句查询出所有产品的最高报价和最低报价,最高报价和最低报价的shop,以及对应的pid、pname。
你这个肯定不行,你没明白题目意思。题目是要求出每一个pid中max(price)对应的shop以及min(price)对应的shop,同时要把pid和pname一起显示出来。最后查询出的视图结构是:pid pname maxprice maxshop minprice minshop其中 pname、maxprice和minprice都是对应同一个pid,maxprice和maxshop 相对应,minprice 和minshop相对应。
这个貌似可以,不过现在没有环境测试下,晚上回家测试下,到时候再结帖,thanks。
+-----+-------------+
| pid | pname |
+-----+-------------+
| 1 | 诺基亚N78 |
| 2 | 诺基亚 5800 |
| 3 | W595c |
| 4 | W580c |
+-----+-------------+
4 rows in set (0.00 sec)mysql> select * from 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 |
+----+------+---------------------------+--------+
6 rows in set (0.00 sec) select p.pid,p.pname,pt2.maxprice,pt1.minprice,pt1.shop
from product p left join (
select pid,min(price) minprice,shop from
productprice group by pid)pt1
on pt1.pid=p.pid
left join(
select pid,max(price) maxprice from
productprice group by pid)pt2
on p.pid=pt2.pid;+-----+-------------+----------+----------+---------------------------+
| pid | pname | maxprice | minprice | shop |
+-----+-------------+----------+----------+---------------------------+
| 1 | 诺基亚N78 | 1700.0 | 1580.0 | ZOL指定诚信商010-82536615 |
| 2 | 诺基亚 5800 | NULL | NULL | NULL |
| 3 | W595c | 1680.0 | 1588.0 | 太平洋报价 |
| 4 | W580c | NULL | NULL | NULL |
+-----+-------------+----------+----------+---------------------------+
4 rows in set (0.00 sec)
select * from
(select pid,max(price) as ma,min(price) as mi
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