select 姓名,职业,住址 from A
where 事业所 = (select 总事业所1 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所2 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所3 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所4 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所5 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所6 from A where 姓名 = 'xiao')
怎样将这个语句写的更加简洁?
where 事业所 = (select 总事业所1 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所2 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所3 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所4 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所5 from A where 姓名 = 'xiao')
or 事业所 = (select 总事业所6 from A where 姓名 = 'xiao')
怎样将这个语句写的更加简洁?
inner join (select * from A where 姓名 = 'xiao') b
on (a.事业所=b.总事业所1 or a.事业所=b.总事业所2 or a.事业所=b.总事业所3
or a.事业所=b.总事业所4 or a.事业所=b.总事业所5 or a.事业所=b.总事业所6)既简洁,用inner join效率又比嵌套查询高啊[/code]
inner join (select * from A where 姓名 = 'xiao') b
on (a.事业所=b.总事业所1 or a.事业所=b.总事业所2 or a.事业所=b.总事业所3
or a.事业所=b.总事业所4 or a.事业所=b.总事业所5 or a.事业所=b.总事业所6)
select 姓名,职业,住址 from A
where 姓名 = 'xiao' and 事业所 in ('总事业所1','总事业所2','总事业所3','总事业所4','总事业所5')
select 姓名,职业,住址 from A
where 姓名 = 'xiao' and 事业所 in ('总事业所1','总事业所2','总事业所3','总事业所4','总事业所5')执行效率如何?
select a.姓名,a.职业,a.住址 from A a
inner join (select distinct 该事务所字段名字 from A where 姓名 = 'xiao') b
on (a.事业所=b.总事业所1 or a.事业所=b.总事业所2 or a.事业所=b.总事业所3
or a.事业所=b.总事业所4 or a.事业所=b.总事业所5 or a.事业所=b.总事业所6);
FROM A,
(SELECT 总事业所1 || 总事业所2 || 总事业所3 || 总事业所4 || 总事业所5 ||
总事业所6 AS "总事业所"
FROM A
WHERE 姓名 = 'xiao') B
WHERE INSTR(总事业所, 事业所) > 0;
where 姓名 = 'xiao' and (事业所 in (总事业所1,总事业所2,总事业所3,总事业所4,总事业所5,总事业所6));