Mysql查询显示项目两次

问题描述：

I've been trying to figure this out for the last few week and got no where... I have two tables buy_ed and sell_ed with the following:

| id | item | system | station | price |

this is my query

$result = 
mysql_query("SELECT buy_ed.item, 
        buy_ed.price as MinPrice, 
        sell_ed.price as MaxPrice, 
        buy_ed.system as b_system, 
        buy_ed.station as b_station, 
        sell_ed.system as s_system,
        sell_ed.station as s_station, 
        (sell_ed.price - buy_ed.price) as profit  
    FROM buy_ed
    INNER JOIN sell_ed
        ON buy_ed.item=sell_ed.item
    ORDER BY profit + 0 DESC
    LIMIT 0, 50")

This is the result

--------------------------------------------------------------------------
| item1 | 100cr(system1 - station 1) | 700cr(system3 - station1) | 600cr |
| item1 | 100cr(system1 - station 1) | 400cr(system7 - station5) | 300cr |
| item2 | 700cr(system1 - station 1) | 900cr(system3 - station1) | 200cr |
| item4 | 700cr(system1 - station 1) | 850cr(system3 - station1) | 150cr |
--------------------------------------------------------------------------

As you see from above item1 is showing up twice, along with the buy price, sell price and system/station names. what I would like is to have it show only once with the lowest buy price and highest sell price and system/station names like this

--------------------------------------------------------------------------
| item1 | 100cr(system1 - station 1) | 700cr(system3 - station1) | 600cr |
| item2 | 700cr(system3 - station 5) | 900cr(system2 - station1) | 200cr |
| item4 | 700cr(system9 - station 7) | 850cr(system3 - station1) | 150cr |
--------------------------------------------------------------------------

Hopefully I explained this ok.

过去几周我一直试图解决这个问题而且没有在哪里... 我有两个表buy_ed和sell_ed具有以下内容： p>

  |  id | 项目| 系统| 车站| 价格|  code>  pre> 
 
 这是我的查询 p> 
 
 
  $ result = 
mysql_query（“SELECT buy_ed.item，
 buy_ed  .price为MinPrice，
 sell_ed.price为MaxPrice，
 buy_ed.system为b_system，
 buy_ed.station为b_station，
 sell_ed.system为s_system，
 sell_ed.station为s_station，
（sell_ed。 价格 -  buy_ed.price）作为利润
来自buy_ed 
 INNER JOIN sell_ed 
 ON buy_ed.item = sell_ed.item 
 ORDER BY利润+ 0 DESC 
 LIMIT 0,50“）
  code>  
 
 这是结果
  p> 
  ------------------------  --------------------------------------------------  
 |  item1 |  100cr（system1  -  station 1）|  700cr（system3  -  station1）|  600cr | 
 |  item1 |  100cr（system1  -  station 1）|  400cr（system7  -  station5）|  300cr | 
 |  item2 |  700cr（system1  -  station 1）|  900cr（system3  -  station1）|  200cr | 
 |  item4 |  700cr（system1  -  station 1）|  850cr（system3  -  station1）|  150cr | 
 ----------------------------------------------  ----------------------------  code>  pre> 
 
 正如您从上面看到的那样，项目1是 出现两次，以及购买价格，售价和系统/电台名称。 我想要的是它只显示一次最低的购买价格和最高的销售价格以及像这样的系统/电台名称 p> 
 
 
  p> 
   -  --------------------------------------------------  ---------------------- \ N |  item1 |  100cr（system1  -  station 1）|  700cr（system3  -  station1）|  600cr | 
 |  item2 |  700cr（system3  -  station 5）|  900cr（system2  -  station1）|  200cr | 
 |  item4 |  700cr（system9  -  station 7）|  850cr（system3  -  station1）|  150cr | 
 ----------------------------------------------  ----------------------------  code>  pre> 
 
 希望我能解释清楚。 
  div>

答

OK. To get the max sell price you need this query

SELECT item, MAX(price) as MaxPrice
FROM sell_ed
GROUP BY item

and similarly for the min buy price

SELECT item, MIN(price) AS MinPrice
FROM buy_ed
GROUP BY item

So now we need to use these queries as sub-queries in the main query to get the results you want.

SELECT DISTINCT b.item, q1.MinPrice, b.system AS b_system, b.station AS b_station, q2.MaxPrice, s.system AS s_system, s.station AS s_station, (q2.MaxPrice - q1.MinPrice) AS profit
FROM buy_ed AS b INNER JOIN
(SELECT item, MIN(price) AS MinPrice
    FROM buy_ed
    GROUP BY item) AS q1
ON b.item = q1.item AND b.price = q1.MinPrice
INNER JOIN sell_ed AS s ON b.item = s.item
INNER JOIN
(SELECT item, MAX(price) as MaxPrice
    FROM sell_ed
    GROUP BY item) AS q2
ON s.item = q2.item AND s.price = q2.MaxPrice

Of course you will still get problems when there are 2 buys or sells that have the same price but different systems or stations, that will leed to duplicate items being listed.

相关推荐