反思SQL的or连接词
下午在调试程序的时候一个诡异的现象，删除单据后被删除的单据依然显示在列表中，查看后台数据库，删除标记已经被置为Y。心里一阵纳闷，于是打断点跟踪，将程序加载时拼出来的语句放在SQL里执行了一把，结果还是把已经删除的记录给查出来了；然后就对着那个查询语句做修改，改来改去就是改不正确，明明在查询条件中对删除的字段做了过滤，但是还是能查出来。后来再将过滤条件一个一个添加，然后查询的土方法中终于发现了猫腻，嘿嘿 犯错误很低级 就是在过滤条件中添加了or关键字，但是没有加括号...
犯错的语句：
select * from aaa where Delete!='Y' and Date between '2010-11-17 00:00:00' and '2010-12-17 23:59:59' and To in (select Stock from StockCtrl where User='106') or From in (select Stock from StockCtrl where User='106') order by Date desc
select * from aaa where Delete!='Y' and Date between '2010-11-17 00:00:00' and '2010-12-17 23:59:59' and (To in (select Stock from StockCtrl where User='106') or From in (select Stock from StockCtrl where User='106')) order by Date desc
