今天看到项目代码里有这条语句,不懂select 1 from XXXXXXX里的1是何意,查了一番才知道:
1、select 1 from mytable;与select anycol(目的表集合中的任意一行) from mytable;与select * from mytable 作用上来说是没有差别的,都是查看是否有记录,一般是作条件用的。select 1 from 中的1是一常量,查到的所有行的值都是它,但从效率上来说,1>anycol>*,因为不用查字典表。
2、查看记录条数可以用select sum(1) from mytable;等价于select sum(*) from mytable;
3、实际项目SQL:
- select c_insrnc_cde, c_nme_cn
- from t_prd_ins a
- where a.c_prod_no = '01'
- and not exists
- (select 1
- from (select d.c_insrnc_cde, a.c_nme_cn
- from t_prd_ins a,
- tb_bas_ply_policy_rdr c,
- tb_bas_ply_policy_rdr_list d
- where a.c_insrnc_cde = d.c_insrnc_cde
- and d.c_policy_id = c.c_policy_id
- and d.c_seq_no = c.c_seq_no
- and a.c_kind_no = '01'
- and c.c_dpt_cde = '101' ----机构树中取出机构
- and c.c_prod_no = '01'
- and c.t_effc_tm <= TO_DATE('2007-9-5', 'YYYY-MM-DD')
- and c.t_expd_tm >= TO_DATE('2007-9-5', 'YYYY-MM-DD')
- and c.c_work_mode = '00501'
- and c.c_reach_area = '00701'
- ) t
- where t.c_insrnc_cde = a.c_insrnc_cde);