sql语句优化

[jxf_yx]

下面的sql语句是否可以优化？

SELECT Sum([TradeNum]), Sum([Payment]), [TradeTime],
(select sum([Payment]) from [Order] as o where [TradeStatus] =2 and o.ID=[Order].ID) as NoPayment
FROM [Order]
GROUP BY date(tradetime)

[bancxc]

[TradeStatus] =2 and o.ID=[Order].ID ，tradetime加索引

[playwarcraft]

--SQL 没这种写法吧
 date(tradetime)

 

[--小F--]

语句已经够简单了
现在可以做的是在连接字段加索引
还有group by 后面的字段也可以
需要注意的是 加索引的字段不要用函数
否则会导致索引失效

[SQL77]

SELECT Sum([TradeNum]), Sum([Payment]), [TradeTime],O.NoPayment

FROM [Order] O,
(select ID,sum([Payment])NoPayment  from [Order] as o where [TradeStatus] =2 GROUP BY ID) T WHERE O.ID=T.ID
GROUP BY date(tradetime) 

[SQL77]

SELECT Sum([TradeNum]), Sum([Payment]), [TradeTime],O.NoPayment

FROM [Order] O,
(select ID,sum([Payment])NoPayment  from [Order] as o where [TradeStatus] =2 GROUP BY ID) T WHERE O.ID=T.ID
GROUP BY DATEPART(DD,tradetime) ,O.NoPayment

[bancxc]

--试试这个效果一样吗  id是不是唯一的
SELECT Sum([TradeNum]), Sum([Payment]), date([TradeTime]),
 sum(o.[Payment]) as NoPayment
FROM [Order] 
left join Order o on o.[TradeStatus] =2 and o.ID=[Order].ID
GROUP BY date(order.tradetime) 

[bancxc]

最好别用子查询 试试6楼的 联查

[bancxc]

77的也行