sql server 按年、季度、月份、周统计订单销量

约定不等于承诺〃 2024-02-17 14:09 75阅读 0赞

\----------------------------------------------------------------  
\--按周统计  
SELECT TOP 10  
DATENAME(year,AddDate) 年,  
DATENAME(week,AddDate) 周,  
COUNT(1) 单量,  
SUM(total) 总金额,  
AVG(total) 客单价  
FROM orderinfo t(NOLOCK)  
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'  
GROUP BY DATENAME(year,AddDate),DATENAME(week,AddDate)  
ORDER BY CONVERT(INT,DATENAME(week,AddDate));  
  
  
\----------------------------------------------------------------  
\--按月统计  
SELECT TOP 10  
DATENAME(year,AddDate) 年,  
DATENAME(month,AddDate) 月,  
COUNT(1) 单量,  
SUM(total) 总金额,  
AVG(total) 客单价  
FROM orderinfo t(NOLOCK)  
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'  
GROUP BY DATENAME(year,AddDate),DATENAME(month,AddDate)  
ORDER BY CONVERT(INT,DATENAME(month,AddDate));  
  
  
\----------------------------------------------------------------  
\--按季度统计  
SELECT TOP 10  
DATENAME(year,AddDate) 年,  
DATENAME(quarter,AddDate) 季度,  
COUNT(1) 单量,  
SUM(total) 总金额,  
AVG(total) 客单价  
FROM orderinfo t(NOLOCK)  
WHERE AddDate BETWEEN '2013-1-1' AND '2014-1-1'  
GROUP BY DATENAME(year,AddDate),DATENAME(quarter,AddDate)  
ORDER BY CONVERT(INT,DATENAME(quarter,AddDate));  
  
  
\----------------------------------------------------------------  
\--按年统计  
SELECT TOP 10  
DATENAME(year,AddDate) 年,  
COUNT(1) 单量,  
SUM(total) 总金额,  
AVG(total) 客单价  
FROM orderinfo t(NOLOCK)  
WHERE AddDate BETWEEN '2012-1-1' AND '2014-1-1'  
GROUP BY DATENAME(year,AddDate)  
ORDER BY CONVERT(INT,DATENAME(year,AddDate));