分组后找到某成员及其前面的成员
【问题】
We have a query like this:
SELECT u.email, st.value as score,
date_format(FROM_UNIXTIME(st.timemodified),'%d-%m-%Y %H:%i:%s' ) as fecha,
IF(st.value >= 70, 'YES', 'NO') as result
FROM user u
LEFT JOIN scorm_scoes_track st ON st.userid = u.id
LEFT JOIN scorm_scoes as ss ON ss.scorm = st.scormid
WHERE (st.element='cmi.score.raw' OR st.element='cmi.core.score.raw')
ORDER BY u.id, st.timemodified
+------------------+------------+---------------------+----------+
| email | score | fecha | result |
+------------------+------------+---------------------+----------+
| test@test.es | 45| 14-03-2015 17:10:18 | NO |
| test@test.es | 65| 14-03-2015 17:12:42 | NO |
| test@test.es | 70| 14-03-2015 17:15:04 | YES |
| test@test.es | 60| 14-03-2015 17:17:16 | NO |
| test@test.es | 65| 14-03-2015 17:17:16 | NO |
User passes the test if result is greater/equal than 70. We need that only results until 70 (when test is passed) will be shown.
In this case, we need this result:
| test@test.es | 45| 14-03-2015 17:10:18 | NO |
| test@test.es | 65| 14-03-2015 17:12:42 | NO |
| test@test.es | 70| 14-03-2015 17:15:04 | YES |
Because results after score is >= 70 have to be discarted
【回答】
SQL 不擅长有序运算，用窗口函数写出来的语句很难懂。这种问题用 SPL 来做，代码直观易懂：
A |
|
1 |
$select * from tb1 order by score |
2 |
=A1.group(email).conj(~.to(~.pselect(score==70))) |
A2: 对 email 分组，使用 conj 做成员合并，获取满足条件的记录（用 pselect 选出第 1 条符合条件的记录序号，to 做区间取数，~ 表示每个组）。
复杂的组内计算和有序计算大都可以用集算器来简化，并且集算器还提供了 JDBC 接口，可以像数据库一样嵌入到应用程序中，用起来很简单，可参考Java 如何调用 SPL 脚本。
