select table1.field1,table1.field2,
(select field3 from table2 where table2.field1=table1.field4) as field3,
(select field4 from table2 where table2.field1=table1.field4) as field4
from table1
现在是通过两个子查询来嵌入两个字段,现在这两个字段是来自同一个表的,能否使用一个子查询就能嵌入这两个字段,这样查询效率可以提高很多
(select field3 from table2 where table2.field1=table1.field4) as field3,
(select field4 from table2 where table2.field1=table1.field4) as field4
from table1
现在是通过两个子查询来嵌入两个字段,现在这两个字段是来自同一个表的,能否使用一个子查询就能嵌入这两个字段,这样查询效率可以提高很多
from table1
join table2
on table1.field1=table2.field4
(select field3 from table2 where table2.field1=table1.field4) as field3,
(select field4 from table2 where table2.field1=table1.field4) as field4
from table1 ---->select table1.field1,table1.field2,table2.field3, table2.field4
from table1 as a
left join table2 where table2.field1=table1.field4
table1.field1,
table1.field2,
b.field3,
b.field4
from table1 a,table2 b
where table2.field1=table1.field4
table2.field3,table2.field4
--(select field3 from table2 where table2.field1=table1.field4) as field3,
--(select field4 from table2 where table2.field1=table1.field4) as field4
from table1 inner join table2 on table2.field1=table1.field4
from t1
left join t2 on t2.f1=t1.f4
from table1 as T1 inner join table2 as T2
on T1.f1=T2.f4
a.field1,a.field2,b.field3,b.field4
from
table1 as a
join
table2 as b on a.field4=b.field1
from table1 left join table2 on table2.field1=table1.field4
from table1 a left join
(select field1,field3,filed4 from table2) b --你的子查询
on b.field1=a.field4
from table1 a
left join table2 b on b.field1=a.field4用where限制或inner join查询得到的结果是不完整的
select
T1.field1,
T1.field2,
T2.field3,
T2.field4
from table1 T1,
(select filed1,field3,field4 from table2 where 所谓的复杂条件 ) T2
where T1.filed1=T2.filed1
(select count(Cust_Active_List_ID) from Cust_Active_List where Cust_Active_List.Cust_ID=tempcustomers.Cust_ID) as Keep_Count,
(select min(Cust_Active_Start) from Cust_Active
left join Cust_Active_List on Cust_Active_List.Cust_Active_ID=Cust_Active.Cust_Active_ID
where Cust_Active_List.Cust_ID=tempcustomers.Cust_ID) as Keep_First_Time,
(select max(Cust_Active_Start) from Cust_Active
left join Cust_Active_List on Cust_Active_List.Cust_Active_ID=Cust_Active.Cust_Active_ID
where Cust_Active_List.Cust_ID=tempcustomers.Cust_ID) as Keep_Last_Time,
(select User_Name from Users where user_id=tempcustomers.user_id) as Keep_User_Name,
(select min(SalesForecast_CompletedDate) from SalesForecast where cust_id=tempcustomers.cust_id and SalesForecast_Completed<>0) as Keep_FirstSale,
(select max(SalesForecast_CompletedDate) from SalesForecast where cust_id=tempcustomers.cust_id and SalesForecast_Completed<>0) as Keep_LastSale
from
(
select DISTINCT cust_id,cust_name,cust_regtime,User_ID from
(
一个复杂的查询
) as temp
where ...
)
as tempcustomers
where (user_id in
(
select User_ID from Right_QueryRight where Right_Module_ID='DA6FBB87-0026-407D-AA4F-D591D00E3162'
) or User_ID='96A598CD-3CAA-45D6-8A39-4D898BDA3F54') and
((Cust_ID in (select Cust_ID from Cust_Active_List))
or
(Cust_ID in (select Cust_ID from SalesForecast)))
实际的一个查询,由于效率不佳,现在需要改进
采用13楼的做法的话,会减少返回的记录数,不知道有没有解决方法
select cust_id,cust_name,cust_regtime,
(select count(Cust_Active_List_ID) from Cust_Active_List where Cust_Active_List.Cust_ID=tempcustomers.Cust_ID) as Keep_Count,
b.Keep_First_Time,b.Keep_Last_Time,
(select User_Name from Users where user_id=tempcustomers.user_id) as Keep_User_Name,
(select min(SalesForecast_CompletedDate) from SalesForecast where cust_id=tempcustomers.cust_id and SalesForecast_Completed <>0) as Keep_FirstSale,
(select max(SalesForecast_CompletedDate) from SalesForecast where cust_id=tempcustomers.cust_id and SalesForecast_Completed <>0) as Keep_LastSale
from
(
select DISTINCT cust_id,cust_name,cust_regtime,User_ID from
(
一个复杂的查询
) as temp
where ...
)
as tempcustomers left join
(select cust_id=Cust_Active_List.Cust_ID,Keep_First_Time=min(Cust_Active_Start),Keep_Last_Time=max(Cust_Active_Start) from Cust_Active left join Cust_Active_List on Cust_Active_List.Cust_Active_ID=Cust_Active.Cust_Active_ID group by Cust_Active_List.Cust_ID) b
on tempcustomers.Cust_ID = b.cust_id where (user_id in
(
select User_ID from Right_QueryRight where Right_Module_ID='DA6FBB87-0026-407D-AA4F-D591D00E3162'
) or User_ID='96A598CD-3CAA-45D6-8A39-4D898BDA3F54') and
((Cust_ID in (select Cust_ID from Cust_Active_List))
or
(Cust_ID in (select Cust_ID from SalesForecast)))
table2.field3,table2.field4
from table1 , table2
where table2.field1=table1.field4