select count(*) from ods_hx_ln_mst
where (select count(*) from ods_hx_ln_reg
where ods_hx_ln_reg.ac_id = ods_hx_ln_mst.ac_id
group by ods_hx_ln_mst.ac_id)>1以上语句是测试有多少条数据属于ods_hx_ln_mst对ods_hx_ln_reg是一对多的关系,但是能解释一下流程么?我仿照上面的,写了单个表的类似功能,想查某个表的某个字段存在2个以上数据的个数:select count(*) from ods_hx_cif_basic_inf
where (select count(*) from ods_hx_cif_basic_inf group by cif_id)>1;但是报错,说我不能插入多行的值…… 求解
where (select count(*) from ods_hx_ln_reg
where ods_hx_ln_reg.ac_id = ods_hx_ln_mst.ac_id
group by ods_hx_ln_mst.ac_id)>1以上语句是测试有多少条数据属于ods_hx_ln_mst对ods_hx_ln_reg是一对多的关系,但是能解释一下流程么?我仿照上面的,写了单个表的类似功能,想查某个表的某个字段存在2个以上数据的个数:select count(*) from ods_hx_cif_basic_inf
where (select count(*) from ods_hx_cif_basic_inf group by cif_id)>1;但是报错,说我不能插入多行的值…… 求解
select cif_id,count(*) from ods_hx_cif_basic_inf group by cif_id having(Count(*)>1)
or
select count(*) from ods_hx_cif_basic_inf
where (select count(*) from ods_hx_cif_basic_inf
where ods_hx_cif_basic_inf.cif_id=cif_id
group by cif_id)>1;
--你少了外层连接条件cif_id
select cif_id,count(*) from ods_hx_cif_basic_inf group by cif_id having(Count(*)>1)
or
select count(*) from ods_hx_cif_basic_inf a
where (select count(*) from ods_hx_cif_basic_inf
where a.cif_id=cif_id
group by cif_id)>1;
---第一个修改
select count(*) from ods_hx_cif_basic_inf
where (select count(*) from ods_hx_cif_basic_inf where ods_hx_ln_reg.ac_id = ods_hx_ln_mst.ac_id )>1;---第二个select count(*) from ods_hx_cif_basic_inf
group by cif_id having count(*)>1
SELECT ac_id FROM ods_hx_ln_mst GROUP BY ac_id HAVING COUNT(*)>1
select count(*) from ods_hx_cif_basic_inf
where (select count(*) from ods_hx_cif_basic_inf group by cif_id)>1;