表A,有列id,name
表B,有列id,action,date
A千万级数据量,表A对表B一对多,B记录A表人某天的动作
情景一:
语句1:select name, date from A join B on a.id=B.id where A.id='110' and B.action='吃饭';
语句2:select name,(select date from B where B.id=A.id and B.action='吃饭') from A where A.id='110';
语句3:select name,date from A join (select id,date from B where B.action='吃饭') B on A.id=B.id where A.id='110';
情景二:
语句1:select name,B.date date1,C.date date2 from A join (select * from B where B.action='吃饭') B on A.id=B.id join (select * from B where B.action='睡觉') C on A.id=C.id where A.id='110';
语句2: select name,case when B.action = '吃饭' then B.date end date1,case when B.action='睡觉' then B.date end date2 from A join B on A.id = B.id where A.id='110';
全部列已加索引,之前优化语句遇到的问题。
求问两种情景下的哪种语句更快?为啥? 修改