我有一张桌子:
id firstval secondval
1 4 5
2 5 4
3 3 3
4 6 6
5 7 8
6 9 8
7 3 3
8 3 3
我要做的第一件事是计算
secondval > firstval
的次数。这显然没问题。但是,我正在努力的事情是如何计算下一行满足
secondval > firstval
条件的次数(对于secondval < firstval
的每个实例)因此,在此示例中,有两行满足第一个规则id 1和5,第二行满足两个规则,接下来的行id 2和6。
最佳答案
SELECT id, @prevGreater AND secondval < firstval AS discrepancy,
@prevGreater := secondval > firstval AS secondGreater
FROM (SELECT * FROM YourTable ORDER BY id) AS x
CROSS JOIN (SELECT @prevGreater := false) AS init
DEMO