27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT TOP 20
a.ServiceNo ,
a.Name ,
b.Seq_Points ,
c.Points
FROM TR_Driver a
LEFT JOIN T21_DriverOptInfoDaily_Points b ON a.ServiceNo = b.ServiceNo
AND b.CompanyID = a.CompanyID
LEFT JOIN TR_DriverPointInfo c ON a.ServiceNo = c.ServiceNo
AND c.CompanyID = b.CompanyID
WHERE a.IsDelete = 0
AND b.Data_Dt = '2014/2/25 10:00:00'
AND c.Data_Dt = '2014/2/25 10:00:00'
AND b.CompanyID = ( SELECT CompanyID
FROM TR_Driver
WHERE ServiceNo = 843391
AND IsDelete = 0
)
ORDER BY Seq_Points
select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points
from TR_Driver a
left join
(select u.Seq_Points,u.ServiceNo,u.CompanyID from T21_DriverOptInfoDaily_Points u
inner join
(select CompanyID from TR_Driver where ServiceNo=843391 and IsDelete=0) v on u.CompanyID=v.CompanyID
)b on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID
left join TR_DriverPointInfo c on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0
order by Seq_Points
left join TR_DriverPointInfo c on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 这段中 是
c.CompanyID=b.CompanyID 还是 c.CompanyID=a.CompanyID ?根据你需求而定!SELECT TOP 20
a.ServiceNo ,
a.Name ,
b.Seq_Points ,
c.Points
FROM ( SELECT *
FROM TR_Driver
WHERE ServiceNo = 843391
AND IsDelete = 0
) a
LEFT JOIN T21_DriverOptInfoDaily_Points b ON a.ServiceNo = b.ServiceNo
AND b.CompanyID = a.CompanyID
LEFT JOIN TR_DriverPointInfo c ON a.ServiceNo = c.ServiceNo
AND c.CompanyID = b.CompanyID
WHERE a.IsDelete = 0
AND b.Data_Dt = DATEADD(SECOND, 0, '2014/2/25 10:00:00')
AND c.Data_Dt = DATEADD(SECOND, 0, '2014/2/25 10:00:00')
AND b.CompanyID = a.companyid
ORDER BY Seq_Points
select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points
from TR_Driver a
left join T21_DriverOptInfoDaily_Points b
on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID
and a.ServiceNo=843391 and a.IsDelete=0
left join TR_DriverPointInfo c
on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID
where a.IsDelete=0 and b.Data_Dt='2014/2/25 10:00:00'
and c.Data_Dt='2014/2/25 10:00:00'
order by Seq_Points
T21_DriverOptInfoDaily_Points的Data_Dt上创建聚集索引(没有的话)
TR_Driver的ServiceNo上创建索引(没有的话)
TR_Driver的CompanyID上创建索引(没有的话)
TR_DriverPointInfo的TR_DriverPointInfo创建索引(没有的话)
改写sql
select top 20 a.ServiceNo, a.Name, b.Seq_Points, c.Points
from TR_Driver a
left join T21_DriverOptInfoDaily_Points b on a.ServiceNo = b.ServiceNo
and b.CompanyID = a.CompanyID
left join TR_DriverPointInfo c on a.ServiceNo = c.ServiceNo
and c.CompanyID = b.CompanyID
where a.IsDelete = 0
and b.Data_Dt= '2014/2/25 10:00:00'
and c.Data_Dt= '2014/2/25 10:00:00'
and b.CompanyID = a.CompanyID
and a.ServiceNo = 843391
order by Seq_Points
select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points from
(select * from TR_Driver where ServiceNo=843391 and IsDelete=0) a left join T21_DriverOptInfoDaily_Points b
on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID
left join TR_DriverPointInfo c
on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0
and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0
order by Seq_Points