易错一
SELECT parent_item_id,CASE parent_item_id WHEN parent_item_id=45 THEN 1 ELSE 0 END
FROM ih_record_table_item WHERE ih_rt_id=2;
SELECT parent_item_id,CASE WHEN parent_item_id=45 THEN 1 ELSE 0 END
FROM ih_record_table_item WHERE ih_rt_id=2;
SELECT parent_item_id,CASE parent_item_id WHEN 45 THEN 1 ELSE 0 END
FROM ih_record_table_item WHERE ih_rt_id=2;
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
19 | 0 |
19 | 0 |
19 | 0 |
NULL | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
NULL | 0 |
NULL | 0 |
45 | 0 |
45 | 0 |
45 | 0 |
NULL | 0 |
45 | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
19 | 0 |
19 | 0 |
19 | 0 |
NULL | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
NULL | 0 |
NULL | 0 |
45 | 1 |
45 | 1 |
45 | 1 |
NULL | 0 |
45 | 1 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
19 | 0 |
19 | 0 |
19 | 0 |
NULL | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
24 | 0 |
NULL | 0 |
NULL | 0 |
45 | 1 |
45 | 1 |
45 | 1 |
NULL | 0 |
45 | 1 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
NULL | 0 |
- 结论
根据前面的三条语句和结果可看出第二个和第三个的结果一致的,对于第一个查询其实是有问题的,case里面其实对比的是一个true 和 false,此处如果写查询不够谨慎,就会容易出现这样的bug。