求一sql语句

[zhongqixing1104]

两个表：
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。

[阿_布]

mysql> select * from product;
+-----+-------------+
| 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)

[zhongqixing1104]

[Quote=引用 6 楼 zhoupuyue 的回复:]
mysql> select * from product;
+-----+-------------+
| 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)
[/Quote]

少了个shop！

[阿_布]

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 

[zhongqixing1104]

[Quote=引用 8 楼 zhoupuyue 的回复:]
SQL codeselect*from
(select pid,max(price)as ma,min(price)as mifrom productpricegroupby pid) bleftjoin productprice aon a.pid=b.pidand a.price=b.maleftjoin productprice con c.pid=b.pidand c.price=b.¡­
[/Quote]

这个可以用！谢谢！

[zhongqixing1104]

[Quote=引用 4 楼 dzxccsu 的回复:]
select a.maxprice,a.maxshop,b.minprice,b.minshop,a.pid,d.pname from (((select max(price) as maxprice,shop as maxshop, pid from productprice group by pid )a left join (select min(price) as minprice,sho¡­
[/Quote]

你这个会报错！

[dzxccsu]

select max(b.price),min(b.price),a.pid,b.pname from product a left join productprice b ona.pid=b.pid gorup by a.pid

[dzxccsu]

有点错误：

select max(b.price),min(b.price),a.pid,b.pname from  product a left join productprice b on a.pid=b.pid gorup by a.pid 

[zhongqixing1104]

[Quote=引用 1 楼 dzxccsu 的回复:]
select max(b.price),min(b.price),a.pid,b.pname from  product a left join productprice b ona.pid=b.pid gorup by a.pid
[/Quote]

你这个肯定不行，你没明白题目意思。题目是要求出每一个pid中max（price）对应的shop以及min（price）对应的shop，同时要把pid和pname一起显示出来。最后查询出的视图结构是：

pid pname maxprice maxshop minprice minshop

其中 pname、maxprice和minprice都是对应同一个pid，maxprice和maxshop 相对应，minprice 和minshop相对应。

[dzxccsu]

select a.maxprice,a.maxshop,b.minprice,b.minshop,a.pid,d.pname from (((select max(price) as maxprice,shop as maxshop, pid from productprice group by pid )a left join (select min(price) as minprice,shop as minshop, pid from productprice group by pid)b on a.pid=b.pid)c inner join product d on c.pid=d.pid)

[zhongqixing1104]

[Quote=引用 4 楼 dzxccsu 的回复:]
select a.maxprice,a.maxshop,b.minprice,b.minshop,a.pid,d.pname from (((select max(price) as maxprice,shop as maxshop, pid from productprice group by pid )a left join (select min(price) as minprice,sho¡­
[/Quote]

这个貌似可以，不过现在没有环境测试下，晚上回家测试下，到时候再结帖，thanks。