php - 同一个表,用sql查询今天和昨天的差值,然后排序,要怎么做
本文介绍了php - 同一个表,用sql查询今天和昨天的差值,然后排序,要怎么做的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
问 题
比较快的写法是什么
我现在用left join 这个表本身,但是结果好像不对
SELECT
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
解决方案
假设有如下数据表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 |
运行
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;
结果
uid | diff |
---|---|
3 | 7 |
1 | 3 |
2 | -2 |
这篇关于php - 同一个表,用sql查询今天和昨天的差值,然后排序,要怎么做的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文