我要查询如下的数据
Select Sum(TimeSlice) as Time,ID From
(
Select DateDiff(ss,BeginDate,EndDate) as TimeSlice,BeginDate,EndDate,ID From
(
Select a.Date as BeginDate,b.Date as EndDate,a.ID as ID
From Table1 as a inner join Table2 as b on a.ID = b.ID
Where b.Date >='20080301' and b.Date<'20080401'
Group by a.ID,a.Date,b.Date
)aa
) bb
Group by ID是一个嵌套2层的子查询,效率不是很高,想转化为连接查询试试看,但不知道改怎么转,请大家帮帮忙。
Select Sum(TimeSlice) as Time,ID From
(
Select DateDiff(ss,BeginDate,EndDate) as TimeSlice,BeginDate,EndDate,ID From
(
Select a.Date as BeginDate,b.Date as EndDate,a.ID as ID
From Table1 as a inner join Table2 as b on a.ID = b.ID
Where b.Date >='20080301' and b.Date<'20080401'
Group by a.ID,a.Date,b.Date
)aa
) bb
Group by ID是一个嵌套2层的子查询,效率不是很高,想转化为连接查询试试看,但不知道改怎么转,请大家帮帮忙。
From Table1 as a inner join Table2 as b on a.ID = b.ID
Where b.Date >='20080301' and b.Date<'20080401'
Group by a.ID
From Table1 as a inner join Table2 as b on a.ID = b.ID
Where b.Date >='20080301' and b.Date<'20080401'
Group by a.ID
Select Sum(TimeSlice) as Time,ID From
(
Select DateDiff(ss,a.Date,b.Date) as TimeSlice,a.ID as ID
From Table1 as a inner join Table2 as b on a.ID = b.ID
Where b.Date >='20080301' and b.Date<'20080401'
Group by a.ID,a.Date,b.Date
) bb
Group by ID
Select sum(DateDiff(ss,a.Date,b.Date)) as Time,a.ID
From Table1 as a inner join Table2 as b on a.ID = b.ID
Where b.Date >='20080301' and b.Date<'20080401'
Group by a.ID
From Table1 as a inner join Table2 as b on a.ID = b.ID
Where b.Date >='20080301' and b.Date <'20080401'
Group by a.ID
从语意上来说应该是最优了。
如果数据量大,速度慢,检查一下:
1. A、B表是否在ID上是主键索引?
2. B表 Date字段是否有索引?
无它了。