测试数据如下:
select 1 indi_id, 31 typ from dual union all
select 1 indi_id, 32 typ from dual union all
select 1 indi_id, 15 typ from dual union all
select 1 indi_id, 16 typ from dual union all
select 2 indi_id, 31 typ from dual union all
select 2 indi_id, 15 typ from dual union all
select 2 indi_id, 16 typ from dual union all
select 3 indi_id, 31 typ from dual union all
select 3 indi_id, 32 typ from dual union all
select 3 indi_id, 16 typ from dual
测试数据里的indi_id表示个人ID,TYP表示一个保险的险种,我想要查询出一个人同时买了险种为31和32类型的人数总数,如以上测试数据的结果应该是2,请问高手怎么实现?在线等待
select 1 indi_id, 31 typ from dual union all
select 1 indi_id, 32 typ from dual union all
select 1 indi_id, 15 typ from dual union all
select 1 indi_id, 16 typ from dual union all
select 2 indi_id, 31 typ from dual union all
select 2 indi_id, 15 typ from dual union all
select 2 indi_id, 16 typ from dual union all
select 3 indi_id, 31 typ from dual union all
select 3 indi_id, 32 typ from dual union all
select 3 indi_id, 16 typ from dual
测试数据里的indi_id表示个人ID,TYP表示一个保险的险种,我想要查询出一个人同时买了险种为31和32类型的人数总数,如以上测试数据的结果应该是2,请问高手怎么实现?在线等待
select 1 indi_id, 31 typ from dual union all
select 1 indi_id, 32 typ from dual union all
select 1 indi_id, 15 typ from dual union all
select 1 indi_id, 16 typ from dual union all
select 2 indi_id, 31 typ from dual union all
select 2 indi_id, 15 typ from dual union all
select 2 indi_id, 16 typ from dual union all
select 3 indi_id, 31 typ from dual union all
select 3 indi_id, 32 typ from dual union all
select 3 indi_id, 16 typ from dual)
SELECT COUNT(*)
FROM (SELECT indi_id FROM tt WHERE typ IN (31, 32) GROUP BY indi_id HAVING COUNT(1) > 1);