经典笛卡尔积SQL：
下面的SQL会造成笛卡尔积：
insert into tydic.temp_0731 select a.user_id,a.province_code,b.attr_code from tydic.pline_user_tmp a,tydic.pline_attr_tmp b
where a.user_id = b.user_id
and (a.service_id = ‘80000009’ and b.attr_code in (‘10000500’,
‘10000501’,
‘10000502’,
‘10000503’
)) or (a.service_id=’80000026′ and b.attr_code in (‘10000105’,
‘10000112’,
‘10000128’,
‘10000129’
)) or (a.service_id=’80000014′ and b.attr_code in (‘10000500’,
‘10000501’,
‘10000502’,
‘10001302’
)) or (a.service_id=’80000015′ and b.attr_code in (‘10000501’,
‘10000502’,
‘10001302’,
‘10001303’
)) or (a.service_id=’80000029′ and b.attr_code in (‘10000500’,
‘10000507’,
‘10000508’,
‘10000501’
)) or (a.service_id=’80000032′ and b.attr_code in (
‘10000500’,
‘10001300’,
‘10000507’)) or (a.service_id=’80000030′ and b.attr_code in (
‘10001025’,
‘10001026’,
‘10001022’
)) or (a.service_id=’80000033′ and b.attr_code in (
‘10001020’,
‘10000502’,
‘10000505’)) or (a.service_id=’80000016′ and b.attr_code in (
‘10001010’,
‘10001011’,
‘10001013’
)) or (a.service_id=’80000007′ and b.attr_code in (
‘10001007’,
‘10001008’,
‘10001009’
)) or (a.service_id=’80000023′ and b.attr_code in (
‘10001001’,
‘10001005’,
‘10001006’
)) or (a.service_id=’80000034′ and b.attr_code in (
‘10001001’,
‘10001002’,
‘10000100’
)) or (a.service_id=’80000017′ and b.attr_code in (
‘10001101’,
‘10001102’,
‘10001103’
));
commit;
经过下面改造，不会出现笛卡尔积：
insert into tydic.temp_0731 select a.user_id,a.province_code,b.attr_code from tydic.pline_user_tmp a,tydic.pline_attr_tmp b
where a.user_id = b.user_id
and( (a.service_id = ‘80000009’ and b.attr_code in (‘10000500’,
‘10000501’,
‘10000502’,
‘10000503’
)) or (a.service_id=’80000026′ and b.attr_code in (‘10000105’,
‘10000112’,
‘10000128’,
‘10000129’
)) or (a.service_id=’80000014′ and b.attr_code in (‘10000500’,
‘10000501’,
‘10000502’,
‘10001302’
)) or (a.service_id=’80000015′ and b.attr_code in (‘10000501’,
‘10000502’,
‘10001302’,
‘10001303’
)) or (a.service_id=’80000029′ and b.attr_code in (‘10000500’,
‘10000507’,
‘10000508’,
‘10000501’
)) or (a.service_id=’80000032′ and b.attr_code in (
‘10000500’,
‘10001300’,
‘10000507’)) or (a.service_id=’80000030′ and b.attr_code in (
‘10001025’,
‘10001026’,
‘10001022’
)) or (a.service_id=’80000033′ and b.attr_code in (
‘10001020’,
‘10000502’,
‘10000505’)) or (a.service_id=’80000016′ and b.attr_code in (
‘10001010’,
‘10001011’,
‘10001013’
)) or (a.service_id=’80000007′ and b.attr_code in (
‘10001007’,
‘10001008’,
‘10001009’
)) or (a.service_id=’80000023′ and b.attr_code in (
‘10001001’,
‘10001005’,
‘10001006’
)) or (a.service_id=’80000034′ and b.attr_code in (
‘10001001’,
‘10001002’,
‘10000100’
)) or (a.service_id=’80000017′ and b.attr_code in (
‘10001101’,
‘10001102’,
‘10001103’
)));
commit;