SQL : Query to compare two subsets of data from the same table

SQL : Query to compare two subsets of data from the same table
วิธีเปรียบเทียบข้อมูล 2 แถวจากตารางเดียวกัน
ตัวอย่างฐานข้อมูลอัตราแลกเปลี่ยน :

1. SELECT `fx_date`,`fx_round`,`fx_buy` FROM `fx` WHERE 1 ORDER  BY fx_date LIMIT 0,10

ข้อมูล :
+------------+----------+--------+
| fx_date | fx_round | fx_buy |
+------------+----------+--------+
| 2015-01-05 | 0 | 32.84 |
| 2015-01-05 | 1 | 32.85 |
| 2015-01-06 | 1 | 32.85 |
| 2015-01-06 | 0 | 32.77 |
| 2015-01-07 | 1 | 32.74 |
| 2015-01-07 | 0 | 32.77 |
| 2015-01-08 | 0 | 32.77 |
| 2015-01-08 | 1 | 32.74 |
| 2015-01-09 | 1 | 32.73 |
| 2015-01-09 | 0 | 32.78 |
+------------+----------+--------+

เปรียบเทียบรายการ :

1. SELECT main.fx_date, round1.fx_buy AS r1, round2.fx_buy AS r2
2.   FROM (SELECT DISTINCT fx_date FROM fx) AS main
3.   LEFT JOIN fx AS round1 ON round1.fx_date = main.fx_date AND round1.fx_round = '0'
4.   LEFT JOIN fx AS round2 ON round2.fx_date = main.fx_date AND round2.fx_round = '1'
5. WHERE round1.fx_buy > round2.fx_buy

http://dba.stackexchange.com/questions/50773/query-to-compare-two-subsets-of-data-from-the-same-table