比較快的寫法是什麼
我現在用left join 這個表本身，但結果好像不對
SELECT
<code> table.id ,SUM(table.s-yestoday.a) as sum
FROM table
LEFT JOIN table yestoday
ON yestoday.uid = wy_appdata.uid
WHERE table.year = '.$year.' AND table.month = '.$month.' AND table.day = '.$day.' AND yestoday.year = '.$bre_data['year'] .' AND yestoday.month ='.$bre_data['month'] .' AND yestoday.day = '.$bre_data['day'] .'
GROUP BY table.uid
ORDER BY sum DESC</code>比較快的寫法是什麼
我現在用left join 這個表本身，但結果好像不對
SELECT
<code> table.id ,SUM(table.s-yestoday.a) as sum
FROM table
LEFT JOIN table yestoday
ON yestoday.uid = wy_appdata.uid
WHERE table.year = '.$year.' AND table.month = '.$month.' AND table.day = '.$day.' AND yestoday.year = '.$bre_data['year'] .' AND yestoday.month ='.$bre_data['month'] .' AND yestoday.day = '.$bre_data['day'] .'
GROUP BY table.uid
ORDER BY sum DESC</code>
假設有下列資料表tbl
| uid | s | date |
|---|---|---|
| 1 | 5 | 2016-08-31 |
| 2 | 3 | 2016-08-31 |
| 3 | 7 | 2016-08-31 |
| 1 | 2 | 2016-08-30 |
| 2 | 5 | 2016-08-30 |
| 4 | 4 | 2016-08-30 |
運行
<code class="sql">SELECT today.uid, today.s - IFNULL(yesterday.s, 0) AS diff FROM (SELECT uid, SUM(s) AS s FROM tbl WHERE date='2016-08-31' GROUP BY uid) AS today LEFT OUTER JOIN (SELECT uid, SUM(s) AS s FROM tbl WHERE date='2016-08-30' GROUP BY uid) AS yesterday USING (uid) ORDER BY diff DESC;</code>
結果
| uid | diff |
|---|---|
| 3 | 7 |
| 1 | 3 |
| 2 | -2 |
