Mysql查询显示项目两次

Question

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.

Answer 1

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.