我需要将A表中的几列求和放在B表中
A表
Date
value1
value2B表
Month
sum1
sum2我写成insert B (Month,sum1,sum2) select Date, sum(value1), sum(value2) where Date between ...肯定不对
这个日期应该咋办呢?
A表
Date
value1
value2B表
Month
sum1
sum2我写成insert B (Month,sum1,sum2) select Date, sum(value1), sum(value2) where Date between ...肯定不对
这个日期应该咋办呢?
select month(Date), sum(value1), sum(value2) where Date between 'xx' and 'xx'
group by month(Date)
insert B (Month,sum1,sum2)
select ‘2011-5-1’, sum(value1), sum(value2) where Date between 'xx' and 'xx'
呵呵。
select
SUBSTRING(CONVERT(varchar(10),GETDATE(),120),1,7)+"_01" as MonthFristDay ,
Sum(value1) , Sum(value2)
From A表
Group By SUBSTRING(CONVERT(varchar(10),GETDATE(),120),1,7)先计算出月 SUBSTRING(CONVERT(varchar(10),GETDATE(),120),1,7)
再按月汇总
再月上加1 SUBSTRING(CONVERT(varchar(10),GETDATE(),120),1,7) + '_01'
insert B (Month,sum1,sum2)
select convert(varchar(7),date,120)+'-01', sum(value1), sum(value2) where Date between 'xx' and 'xx'
group by convert(varchar(7),date,120)
zheyanhg是你的要求了吧
消息 164,级别 15,状态 1,第 1 行
Each GROUP BY expression must contain at least one column that is not an outer reference.
消息 207,级别 16,状态 1,第 17 行
Invalid column name '_01'.
select convert(varchar(8),Date,120)+'01' as date, sum(value1), sum(value2)
from table1
where Date between ..... and ...
group by convert(varchar(8),Date,120)
--不需要這樣反復轉換
SUBSTRING(CONVERT(varchar(10),GETDATE(),120),1,7)
Convert( varchar(7) , Date , 120 ) + '-01'这样第一天,比较简单