按季度统计的
Select
Convert(Varchar(7),Date1,120) As 季度,
SUM(Val1) As Val1_Sum,
AVG(Val2) As Val2_Avg
from t
Group By Convert(Varchar(7),Date1,120)
Order By 季度按年统计的
Select
Convert(Varchar(4),Date1,120) As 年,
SUM(Val1) As Val1_Sum,
AVG(Val2) As Val2_Avg
from t
Group By Convert(Varchar(4),Date1,120)
Order By 年
Select
Convert(Varchar(7),Date1,120) As 季度,
SUM(Val1) As Val1_Sum,
AVG(Val2) As Val2_Avg
from t
Group By Convert(Varchar(7),Date1,120)
Order By 季度按年统计的
Select
Convert(Varchar(4),Date1,120) As 年,
SUM(Val1) As Val1_Sum,
AVG(Val2) As Val2_Avg
from t
Group By Convert(Varchar(4),Date1,120)
Order By 年
Select
Convert(Varchar(7),Date1,120) As 季度,
SUM(Val1) As Val1_Sum,
AVG(Val2) As Val2_Avg
from t
Group By Convert(Varchar(7),Date1,120)
Order By Convert(Varchar(7),Date1,120)
Select
Convert(Varchar(4),Date1,120) As 年,
SUM(Val1) As Val1_Sum,
AVG(Val2) As Val2_Avg
from t
Group By Convert(Varchar(4),Date1,120)
Order By Convert(Varchar(4),Date1,120)
SELECT
convert(char(4),Date1,120) as 年 ,SUM(Val1) as Val1_Sum , AVG(Val2) as Val2_Avg
FROM
t
WHERE
Date1>='2001-11-5' AND Date1<'2003-2-1'
GROUP BY
convert(char(4),Date1,120)
Select
Convert(char(7),Date1,120) As 季度,
SUM(Val1) As Val1_Sum,
AVG(Val2) As Val2_Avg
from t
Group By Convert(Varchar(7),Date1,120)
SUM(Val1), AVG(Val2)
FROM
t
WHERE
Date1>='2001-11-5' AND Date1<'2003-2-1'
GROUP BY year(datel),datepart(quarter,datel)
SELECT
SUM(case when convert(char(7),Date1,120) between '2001-01' and '2001-03' then Val1
else 0 end) as '第一季度總值',
AVG(case when convert(char(7),Date1,120) between '2001-01' and '2001-03' then Val1
else 0 end) as '第一季度平均值',
SUM(case when convert(char(7),Date1,120) between '2001-04' and '2001-06' then Val1
else 0 end) as '第二季度總值',
AVG(case when convert(char(7),Date1,120) between '2001-04' and '2001-06' then Val1
else 0 end) as '第二季度平均值',
SUM(case when convert(char(7),Date1,120) between '2001-07' and '2001-09' then Val1
else 0 end) as '第三季度總值',
AVG(case when convert(char(7),Date1,120) between '2001-07' and '2001-09' then Val1
else 0 end) as '第三季度平均值',
SUM(case when convert(char(7),Date1,120) between '2001-10' and '2001-12' then Val1
else 0 end) as '第四季度總值',
AVG(case when convert(char(7),Date1,120) between '2001-10' and '2001-12' then Val1
else 0 end) as '第四季度平均值'
FROM t
--但是有更容易的方法就是用動態語句實現,以上的是笨方法哦
取到季度
select date1,val1,val2,季度=cast(year(date1) as char(4)+'-'+cast(datepart(quarter,date1) as char(1)) into #temp from t where Date1>='2001-11-5' AND Date1<'2003-2-1'select 季度,sum(val1),avg(val2) from #temp group by 季度
SELECT 季度, SUM(val1) AS sumval, AVG(val2) asavgval
FROM (SELECT (CASE WHEN datepart(mm, Data1) BETWEEN '1' AND
'3' THEN CONVERT(Varchar(4), Data1, 120) + '-1' WHEN datepart(mm, Data1)
BETWEEN '4' AND '6' THEN CONVERT(Varchar(4), Data1, 120)
+ '-2' WHEN datepart(mm, Data1) BETWEEN '7' AND
'9' THEN CONVERT(Varchar(4), Data1, 120) + '-3' WHEN datepart(mm, Data1)
BETWEEN '10' AND '12' THEN CONVERT(Varchar(4), Data1, 120) + '-4' END)
AS 季度, val1, val2
FROM t1) AS m
GROUP BY 季度
sql server测试通过
FROM (SELECT (CASE WHEN datepart(mm, Date1) BETWEEN '1' AND
'3' THEN CONVERT(Varchar(4), Date1, 120) + '-1' WHEN datepart(mm, Date1)
BETWEEN '4' AND '6' THEN CONVERT(Varchar(4), Date1, 120)
+ '-2' WHEN datepart(mm, Date1) BETWEEN '7' AND
'9' THEN CONVERT(Varchar(4), Date1, 120) + '-3' WHEN datepart(mm, Date1)
BETWEEN '10' AND '12' THEN CONVERT(Varchar(4), Date1, 120) + '-4' END)
AS 季度, val1, val2
FROM t1) AS m
GROUP BY 季度
sql server测试通过.
刚才写错了,date写成data了
FROM t
WHERE Date1>='2001-11-5' AND Date1<'2003-2-1'
GROUP BY cast(year(datel) as varchar(5))+'-'+cast(datepart(quarter,datel) as varchar(5)) Order By 季度
SELECT cast(year(datel) as varchar(5))+'-'+cast(datepart(quarter,datel) as varchar(5)) as 季度, SUM(Val1) Val1_Sum , AVG(Val2) Val2_Avg
FROM t
WHERE Date1>='2001-11-5' AND Date1<'2003-2-1'
GROUP BY cast(year(datel) as varchar(5))+'-'+cast(datepart(quarter,datel) as varchar(5)) Order By 季度
--按月
SELECT convert(varchar(7),Date1,120) as 月, SUM(Val1) Val1_Sum , AVG(Val2) Val2_Avg
FROM t
WHERE Date1>='2001-11-5' AND Date1<'2003-2-1'
GROUP BY convert(varchar(7),Date1,120) Order By 月
--按年
SELECT year(date1) as 年, SUM(Val1) Val1_Sum , AVG(Val2) Val2_Avg
FROM t
WHERE Date1>='2001-11-5' AND Date1<'2003-2-1'
GROUP BY year(date1) Order By 年
月 Sum
1 10
2 0
3 11
4 0
请问120是什么意思
declare @month varchar(10)
declare @year varchar(10)
set @year='2005'
set @year='2005-04'
--按年
SELECT
convert(char(4),Date1,120) as 年 ,SUM(Val1) as Val1_Sum , AVG(Val2) as Val2_Avg
FROM
t
WHERE convert(char(4),Date1,120) =@year
GROUP BY
convert(char(4),Date1,120)
--按月
SELECT
convert(char(7),Date1,120) as 月 ,SUM(Val1) as Val1_Sum , AVG(Val2) as Val2_Avg
FROM
t
WHERE convert(char(7),Date1,120) =@month
GROUP BY
convert(char(7),Date1,120)
--至于按季度
還不太清楚樓主的意思
如果 (日期>2003-5-3 AND 2004-1-2)这样的话应该是:季度 统计
2003-2 100
2003-3 100
2003-4 NULL (如果该季度没有记录, 则为NULL, 但必须有此统计记录)
2004-1 103
2003-2 100
2003-3 100
2003-4 NULL (如果该季度没有记录, 则为NULL, 但必须有此统计记录)
2004-1 103
好像和按月沒有什么區別哦
我對季度的理解是﹕
如﹕
季度 统计
第一季度(200401-200403) 200
第二季度(200404-200406) 1000
................................
即使是该季度没有数据, 也要有统计记录, 只是统计值为null.
放在一個SQL是里實現
create procedure p_TotalAvg
@year varchar(4)
as
SELECT
'第一季度' as 季度 ,isnull(SUM(Val1) as Val1_Sum,0) , isnull(AVG(Val2) as Val2_Avg,0)
FROM t
WHERE convert(char(7),Date1,120) between @year+'-01' and @year+'-03'
GROUP BY
convert(char(7),Date1,120)
union all
SELECT
'第二季度' as 季度 ,isnull(SUM(Val1) as Val1_Sum,0) , isnull(AVG(Val2) as Val2_Avg,0)
FROM t
WHERE convert(char(7),Date1,120) between @year+'-04' and @year+'-06'
GROUP BY
convert(char(7),Date1,120)
union all
SELECT
'第三季度' as 季度 ,isnull(SUM(Val1) as Val1_Sum,0) , isnull(AVG(Val2) as Val2_Avg,0)
FROM t
WHERE convert(char(7),Date1,120) between @year+'-07' and @year+'-09'
GROUP BY
convert(char(7),Date1,120)
union all
SELECT
'第四季度' as 季度 ,isnull(SUM(Val1) as Val1_Sum,0) , isnull(AVG(Val2) as Val2_Avg,0)
FROM t
WHERE convert(char(7),Date1,120) between @year+'-10' and @year+'-12'
GROUP BY
convert(char(7),Date1,120)
go
--測試
exec p_TotalAvg '2004'
3Q, 我在一个专门给分的帖子里给你30分, 表示对你的劳动的认同.;)
虽然不是我要的结果, 如果可以的话, 请继续解答...
select sum(Val1) val1,avg(Val2) val2,convert(varchar(10),datepart(yy,date1))+'-'+
convert(varchar(10),datepart(quarter,date1)) from t
group by convert(varchar(10),datepart(yy,date1))+'-'+
convert(varchar(10),datepart(quarter,date1))
我需要即使没有数据,也要显示,如:
2001-1 1000
2001-2 1001
2001-3 NULL
2001-4 NULL
2002-1 2000
convert(char(4),date1,120)
请问120是什么意思convert(data_type,expression[,style])
style指的是输出时间的格式,120对应yy-mm-d
102对应的是yy-mm-d
我需要即使没有数据,也要显示,如:
2001-1 1000
2001-2 1001
2001-3 NULL
2001-4 NULL
2002-1 2000
我需要即使没有数据,也要显示,如:
2001-1 1000
2001-2 1001
2001-3 NULL
2001-4 NULL
2002-1 2000
FROM
t
WHERE
Date1>='2001-11-5' AND Date1<'2003-2-1'
GROUP BY yeal(datel),datepart(quarter,datel)