SELECT SUM(TotalPrice) FROM NarukoEC.dbo.Orders AS o
WHERE CreateTime BETWEEN '20110101' AND '20110131' AND o.TotalGoodsPrice>0
AND
NOT EXISTS(
SELECT 1 FROM NarukoEC.dbo.Orders WHERE state not in(0,5,7) AND CreateTime < '20110101' AND CustomerID = o.CustomerID
)SELECT SUM(TotalPrice) FROM NarukoEC.dbo.Orders AS o
WHERE CreateTime BETWEEN '20110201' AND '20110228' AND o.TotalGoodsPrice>0
AND
NOT EXISTS(
SELECT 1 FROM NarukoEC.dbo.Orders WHERE state not in(0,5,7) AND CreateTime < '20110201' AND CustomerID = o.CustomerID
)
我要依次查询 二零零一年一月份 到 十二月份每个月的数据,这样写的话就每次都要改动月份!
如何让它一次性就全部查询出来?
WHERE CreateTime BETWEEN '20110101' AND '20110131' AND o.TotalGoodsPrice>0
AND
NOT EXISTS(
SELECT 1 FROM NarukoEC.dbo.Orders WHERE state not in(0,5,7) AND CreateTime < '20110101' AND CustomerID = o.CustomerID
)SELECT SUM(TotalPrice) FROM NarukoEC.dbo.Orders AS o
WHERE CreateTime BETWEEN '20110201' AND '20110228' AND o.TotalGoodsPrice>0
AND
NOT EXISTS(
SELECT 1 FROM NarukoEC.dbo.Orders WHERE state not in(0,5,7) AND CreateTime < '20110201' AND CustomerID = o.CustomerID
)
我要依次查询 二零零一年一月份 到 十二月份每个月的数据,这样写的话就每次都要改动月份!
如何让它一次性就全部查询出来?
FROM NarukoEC.dbo.Orders AS o
WHERE o.TotalGoodsPrice > 0 AND [state] IN ( 0, 5, 7 )
GROUP BY SUBSTRING(CreateTime,5,2)把月份带出来就可以拉
FROM NarukoEC.dbo.Orders AS o
WHERE CreateTime BETWEEN '20110101' AND '20111231'
AND o.TotalGoodsPrice > 0
AND state NOT IN (0 , 5 , 7)
GROUP BY DATEPART(mm , CreateTime)