我有大约500行与下面4行相似的数据,从这个例子中,我只希望使用以下逻辑返回两行:
如果emp No
817 sal_id
= 2 is_active
0则返回817 sal_id
1或如果emp No
820 sal_id
= 2 is_active
1则返回820 sal_2
因此sal_id
1默认情况下始终处于活动状态,如果is_active
为1,则应返回sal_id
2而不是sal_id
1,希望这有意义!
给我一个结果:
<>之前+--------+--------+-----------+----------------+-----------+| emp否| sal_id | value | effecve_date | is_active |+--------+--------+-----------+----------------+-----------+| 817 | 1 | ded914e3b | 01/04/2013 | 1 || 820 | 2 | ea42574e4 | 02/04/2013 | 1 |+--------+--------+-----------+----------------+-----------+之前不确定这是否可以做到,请建议和感谢。
这个解决方案比Mark的更冗长,但应该更有效,因为它避免使用临时表…
SELECT
es1.emp_no,
IF(es2.is_active=1, es2.sal_id, es1.sal_id) AS sal_id,
IF(es2.is_active=1, es2.value, es1.value) AS value,
IF(es2.is_active=1, es2.effective_date, es1.effective_date) AS effective_date,
IF(es2.is_active=1, es2.is_active, es1.is_active) AS is_active
FROM employee_salary es1
LEFT JOIN employee_salary es2 ON (es2.emp_no=es1.emp_no AND es2.sal_id=2)
WHERE es1.sal_id=1;
…的收益率…
+--------+--------+-----------+----------------+-----------+
| emp_no | sal_id | value | effective_date | is_active |
+--------+--------+-----------+----------------+-----------+
| 817 | 1 | DED914E3B | 01/04/2013 | 1 |
| 820 | 2 | EA42574E4 | 02/04/2013 | 1 |
+--------+--------+-----------+----------------+-----------+
…
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | es1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 1 | SIMPLE | es2 | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
与马克的…相比
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | es | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer |
| 2 | DERIVED | employee_salary | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+
您可以去掉任何实际上不需要这些值的IF
子句。
假设数据存储在employee_salary
中,这是一个肮脏的hack;
select es.emp_no, es.sal_id, value, effective_date, is_active from employee_salary es
join (
select emp_no, max(sal_id) as sal_id from employee_salary
where is_active<>0
group by emp_no
) selector
where es.emp_no=selector.emp_no and es.sal_id=selector.sal_id
限制:
- 如果不重写SQL,将默认值从2更改为1是不容易的。
- 我不清楚如果数据不在表中,而是来自SELECT的结果,而不是临时表,这种方法将如何应用。
- 可能无法处理
is_active=0
为sal_id=1
和sal_id=2
的情况,因为你需要,目前没有结果返回emp_no,这是你想要的吗? - 正如Aya指出的那样,这会生成一个临时表,然后在不使用索引的情况下对其进行排序,预计这会很慢。