insert into b select 学院,year(时间字段), sum(case when month(时间字段) = 1 then 1 else 0 end), sum(case when month(时间字段) = 2 then 1 else 0 end), sum(case when month(时间字段) = 3 then 1 else 0 end), sum(case when month(时间字段) = 4 then 1 else 0 end), sum(case when month(时间字段) = 5 then 1 else 0 end), sum(case when month(时间字段) = 6 then 1 else 0 end), sum(case when month(时间字段) = 7 then 1 else 0 end), sum(case when month(时间字段) = 8 then 1 else 0 end), sum(case when month(时间字段) = 9 then 1 else 0 end), sum(case when month(时间字段) = 10 then 1 else 0 end), sum(case when month(时间字段) = 11 then 1 else 0 end), sum(case when month(时间字段) = 12 then 1 else 0 end) from a group by 学院,year(时间字段)
insert into b 学院,年份, select 学院,year(时间字段), sum(case when month(时间字段) = 1 then 1 else 0 end), sum(case when month(时间字段) = 2 then 1 else 0 end), sum(case when month(时间字段) = 3 then 1 else 0 end), sum(case when month(时间字段) = 4 then 1 else 0 end), sum(case when month(时间字段) = 5 then 1 else 0 end), sum(case when month(时间字段) = 6 then 1 else 0 end), sum(case when month(时间字段) = 7 then 1 else 0 end), sum(case when month(时间字段) = 8 then 1 else 0 end), sum(case when month(时间字段) = 9 then 1 else 0 end), sum(case when month(时间字段) = 10 then 1 else 0 end), sum(case when month(时间字段) = 11 then 1 else 0 end), sum(case when month(时间字段) = 12 then 1 else 0 end) from a group by 学院,year(时间字段) 要是把学院和年份两项也插入到b表中,是这样吗?谢谢。
insert into b select 学院 , year(时间字段), sum(case month(时间字段) when 1 then 1 else 0 end) [1], sum(case month(时间字段) when 2 then 1 else 0 end) [2], sum(case month(时间字段) when 3 then 1 else 0 end) [3], sum(case month(时间字段) when 4 then 1 else 0 end) [4], sum(case month(时间字段) when 5 then 1 else 0 end) [5], sum(case month(时间字段) when 6 then 1 else 0 end) [6], sum(case month(时间字段) when 7 then 1 else 0 end) [7], sum(case month(时间字段) when 8 then 1 else 0 end) [8], sum(case month(时间字段) when 9 then 1 else 0 end) [9], sum(case month(时间字段) when 10 then 1 else 0 end) [10], sum(case month(时间字段) when 11 then 1 else 0 end) [11], sum(case month(时间字段) when 12 then 1 else 0 end) [12] from a where 学院 = '...' and year(时间字段) = ... group by 学院 , year(时间字段)
应该有where语句吧,学院和年份是特定的呀?呵呵。谢谢。
在from后加就可以了,和查询条件一样!
insert into b select 学院,date(year,年份), sum(case date(month,时间字段) when '1' then 1 else 0 end) '1月', sum(case date(month,时间字段) when '2' then 1 else 0 end) '2月', sum(case date(month,时间字段) when '3' then 1 else 0 end) '3月', sum(case date(month,时间字段) when '4' then 1 else 0 end) '4月', sum(case date(month,时间字段) when '5' then 1 else 0 end) '5月', sum(case date(month,时间字段) when '6' then 1 else 0 end) '6月', sum(case date(month,时间字段) when '7' then 1 else 0 end) '7月', sum(case date(month,时间字段) when '8' then 1 else 0 end) '8月', sum(case date(month,时间字段) when '9' then 1 else 0 end) '9月', sum(case date(month,时间字段) when '10' then 1 else 0 end) '10月', sum(case date(month,时间字段) when '11' then 1 else 0 end) '11月', sum(case date(month,时间字段) when '12' then 1 else 0 end) '12月' from a group by 学院,year(时间字段)
sql=insert into mRecordCount UnitName, (select year(edittime), sum(case when month(edittime) = 1 then 1 else 0 end), sum(case when month(edittime) = 2 then 1 else 0 end), sum(case when month(edittime) = 3 then 1 else 0 end), sum(case when month(edittime) = 4 then 1 else 0 end), sum(case when month(edittime) = 5 then 1 else 0 end), sum(case when month(edittime) = 6 then 1 else 0 end), sum(case when month(edittime) = 7 then 1 else 0 end), sum(case when month(edittime) = 8 then 1 else 0 end), sum(case when month(edittime) = 9 then 1 else 0 end), sum(case when month(edittime) = 10 then 1 else 0 end), sum(case when month(edittime) = 11 then 1 else 0 end), sum(case when month(edittime) = 12 then 1 else 0 end)) from (select * from (select 10000+id,edittime from news union all select 20000+id,edittime from gdnews union all select 30000+id,edittime from dtnews)) where UnitName="UnitName" and Year=year(edittime) group by UnitName,year(edittime) 语句未结束,请教。谢谢。
insert into b
select 学院,year(时间字段),
sum(case when month(时间字段) = 1 then 1 else 0 end),
sum(case when month(时间字段) = 2 then 1 else 0 end),
sum(case when month(时间字段) = 3 then 1 else 0 end),
sum(case when month(时间字段) = 4 then 1 else 0 end),
sum(case when month(时间字段) = 5 then 1 else 0 end),
sum(case when month(时间字段) = 6 then 1 else 0 end),
sum(case when month(时间字段) = 7 then 1 else 0 end),
sum(case when month(时间字段) = 8 then 1 else 0 end),
sum(case when month(时间字段) = 9 then 1 else 0 end),
sum(case when month(时间字段) = 10 then 1 else 0 end),
sum(case when month(时间字段) = 11 then 1 else 0 end),
sum(case when month(时间字段) = 12 then 1 else 0 end)
from a
group by 学院,year(时间字段)
select 学院,year(时间字段),
sum(case when month(时间字段) = 1 then 1 else 0 end),
sum(case when month(时间字段) = 2 then 1 else 0 end),
sum(case when month(时间字段) = 3 then 1 else 0 end),
sum(case when month(时间字段) = 4 then 1 else 0 end),
sum(case when month(时间字段) = 5 then 1 else 0 end),
sum(case when month(时间字段) = 6 then 1 else 0 end),
sum(case when month(时间字段) = 7 then 1 else 0 end),
sum(case when month(时间字段) = 8 then 1 else 0 end),
sum(case when month(时间字段) = 9 then 1 else 0 end),
sum(case when month(时间字段) = 10 then 1 else 0 end),
sum(case when month(时间字段) = 11 then 1 else 0 end),
sum(case when month(时间字段) = 12 then 1 else 0 end)
from a
group by 学院,year(时间字段)
要是把学院和年份两项也插入到b表中,是这样吗?谢谢。
select 学院 , year(时间字段),
sum(case month(时间字段) when 1 then 1 else 0 end) [1],
sum(case month(时间字段) when 2 then 1 else 0 end) [2],
sum(case month(时间字段) when 3 then 1 else 0 end) [3],
sum(case month(时间字段) when 4 then 1 else 0 end) [4],
sum(case month(时间字段) when 5 then 1 else 0 end) [5],
sum(case month(时间字段) when 6 then 1 else 0 end) [6],
sum(case month(时间字段) when 7 then 1 else 0 end) [7],
sum(case month(时间字段) when 8 then 1 else 0 end) [8],
sum(case month(时间字段) when 9 then 1 else 0 end) [9],
sum(case month(时间字段) when 10 then 1 else 0 end) [10],
sum(case month(时间字段) when 11 then 1 else 0 end) [11],
sum(case month(时间字段) when 12 then 1 else 0 end) [12]
from a where 学院 = '...' and year(时间字段) = ...
group by 学院 , year(时间字段)
在from后加就可以了,和查询条件一样!
select 学院,date(year,年份),
sum(case date(month,时间字段) when '1' then 1 else 0 end) '1月',
sum(case date(month,时间字段) when '2' then 1 else 0 end) '2月',
sum(case date(month,时间字段) when '3' then 1 else 0 end) '3月',
sum(case date(month,时间字段) when '4' then 1 else 0 end) '4月',
sum(case date(month,时间字段) when '5' then 1 else 0 end) '5月',
sum(case date(month,时间字段) when '6' then 1 else 0 end) '6月',
sum(case date(month,时间字段) when '7' then 1 else 0 end) '7月',
sum(case date(month,时间字段) when '8' then 1 else 0 end) '8月',
sum(case date(month,时间字段) when '9' then 1 else 0 end) '9月',
sum(case date(month,时间字段) when '10' then 1 else 0 end) '10月',
sum(case date(month,时间字段) when '11' then 1 else 0 end) '11月',
sum(case date(month,时间字段) when '12' then 1 else 0 end) '12月'
from a
group by 学院,year(时间字段)
(select year(edittime),
sum(case when month(edittime) = 1 then 1 else 0 end),
sum(case when month(edittime) = 2 then 1 else 0 end),
sum(case when month(edittime) = 3 then 1 else 0 end),
sum(case when month(edittime) = 4 then 1 else 0 end),
sum(case when month(edittime) = 5 then 1 else 0 end),
sum(case when month(edittime) = 6 then 1 else 0 end),
sum(case when month(edittime) = 7 then 1 else 0 end),
sum(case when month(edittime) = 8 then 1 else 0 end),
sum(case when month(edittime) = 9 then 1 else 0 end),
sum(case when month(edittime) = 10 then 1 else 0 end),
sum(case when month(edittime) = 11 then 1 else 0 end),
sum(case when month(edittime) = 12 then 1 else 0 end))
from (select * from (select 10000+id,edittime from news union all select 20000+id,edittime from gdnews union all select 30000+id,edittime from dtnews)) where UnitName="UnitName" and Year=year(edittime)
group by UnitName,year(edittime)
语句未结束,请教。谢谢。