示例数据如下:
ennmcd time qsl
Q1 2007-08-08 54.400
Q1 2007-08-09 185.600
Q1 2007-08-10 154.400
Q1 2007-08-11 96.000
Q1 2007-08-12 128.400
Q1 2007-08-13 105.600
Q2 2007-08-14 100.800
Q2 2007-08-15 129.200
Q2 2007-08-16 145.600
Q2 2007-08-17 95.200
Q2 2007-08-18 162.800
Q2 2007-08-19 161.200
Q2 2007-08-20 89.600
Q2 2007-08-21 110.400
Q3 2007-08-22 143.200
Q3 2007-08-23 177.600
Q3 2007-08-08 11
Q3 2007-08-09 18
Q3 2007-08-10 154
Q3 2007-08-11 96.000
Q3 2007-08-12 128.400
Q3 2007-08-13 105.600
…… …… ……
问题一:想生成某一月的数据表(按这个月的天数来,比如说8月份,有31天,则显示31行),格式如下,其中ennmcd个数不定的,可能会有多个;如果当日对应的ennmcd不存在qsl时,自动在该单元格写入“--”
Q1 Q2 Q3 ……
1日
2日
3日
4日
5日
6日
…… 问题二:
同时生成下面这样的表:
1日 2日 3日 4日 ……
Q1
Q2
Q3
Q4
……
这样的表(上面的日期也是按这个月的天数来),数据不存在时,自动补“--”
哪位高手会啊,帮帮忙!谢谢了!
ennmcd time qsl
Q1 2007-08-08 54.400
Q1 2007-08-09 185.600
Q1 2007-08-10 154.400
Q1 2007-08-11 96.000
Q1 2007-08-12 128.400
Q1 2007-08-13 105.600
Q2 2007-08-14 100.800
Q2 2007-08-15 129.200
Q2 2007-08-16 145.600
Q2 2007-08-17 95.200
Q2 2007-08-18 162.800
Q2 2007-08-19 161.200
Q2 2007-08-20 89.600
Q2 2007-08-21 110.400
Q3 2007-08-22 143.200
Q3 2007-08-23 177.600
Q3 2007-08-08 11
Q3 2007-08-09 18
Q3 2007-08-10 154
Q3 2007-08-11 96.000
Q3 2007-08-12 128.400
Q3 2007-08-13 105.600
…… …… ……
问题一:想生成某一月的数据表(按这个月的天数来,比如说8月份,有31天,则显示31行),格式如下,其中ennmcd个数不定的,可能会有多个;如果当日对应的ennmcd不存在qsl时,自动在该单元格写入“--”
Q1 Q2 Q3 ……
1日
2日
3日
4日
5日
6日
…… 问题二:
同时生成下面这样的表:
1日 2日 3日 4日 ……
Q1
Q2
Q3
Q4
……
这样的表(上面的日期也是按这个月的天数来),数据不存在时,自动补“--”
哪位高手会啊,帮帮忙!谢谢了!
/*
按月进行行列转换并加合计(2007-11-19于海南三亚)例如有表tb某些人每月消费数据如下:
id data month
001 11 1
001 12 2
001 13 3
001 14 4
001 15 5
001 16 6
001 17 7
001 18 8
001 19 9
001 110 10
001 111 11
001 112 12
002 21 1
002 22 2
002 23 3
002 24 4
002 25 5
002 26 6
002 27 7
002 28 8
002 29 9
002 210 10
002 211 11
002 212 12
要实现如下结果:
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212
*/create table tb
(
id char(3),
data int,
month int
)
insert into tb(id,data,month) values('001',11,1)
insert into tb(id,data,month) values('001',12,2)
insert into tb(id,data,month) values('001',13,3)
insert into tb(id,data,month) values('001',14,4)
insert into tb(id,data,month) values('001',15,5)
insert into tb(id,data,month) values('001',16,6)
insert into tb(id,data,month) values('001',17,7)
insert into tb(id,data,month) values('001',18,8)
insert into tb(id,data,month) values('001',19,9)
insert into tb(id,data,month) values('001',110,10)
insert into tb(id,data,month) values('001',111,11)
insert into tb(id,data,month) values('001',112,12)
insert into tb(id,data,month) values('002',21,1)
insert into tb(id,data,month) values('002',22,2)
insert into tb(id,data,month) values('002',23,3)
insert into tb(id,data,month) values('002',24,4)
insert into tb(id,data,month) values('002',25,5)
insert into tb(id,data,month) values('002',26,6)
insert into tb(id,data,month) values('002',27,7)
insert into tb(id,data,month) values('002',28,8)
insert into tb(id,data,month) values('002',29,9)
insert into tb(id,data,month) values('002',210,10)
insert into tb(id,data,month) values('002',211,11)
insert into tb(id,data,month) values('002',212,12)
goSELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月'
FROM tb
GROUP BY ID drop table tb/*
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
---- --- --- --- --- --- --- --- --- --- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212(所影响的行数为 2 行)
*/--------------------------------------------------------------
/*
合计每个人每年的数据
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
*/SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' ,
SUM(data) as '合计'
FROM tb
GROUP BY ID /*
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
---- --- --- --- --- --- --- --- --- --- ---- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
*/
谢谢了@
insert into tb values('Q1', '2007-08-08', 54.400 )
insert into tb values('Q1', '2007-08-09', 185.600)
insert into tb values('Q1', '2007-08-10', 154.400)
insert into tb values('Q1', '2007-08-11', 96.000 )
insert into tb values('Q1', '2007-08-12', 128.400)
insert into tb values('Q1', '2007-08-13', 105.600)
insert into tb values('Q2', '2007-08-14', 100.800)
insert into tb values('Q2', '2007-08-15', 129.200)
insert into tb values('Q2', '2007-08-16', 145.600)
insert into tb values('Q2', '2007-08-17', 95.200 )
insert into tb values('Q2', '2007-08-18', 162.800)
insert into tb values('Q2', '2007-08-19', 161.200)
insert into tb values('Q2', '2007-08-20', 89.600 )
insert into tb values('Q2', '2007-08-21', 110.400)
insert into tb values('Q3', '2007-08-22', 143.200)
insert into tb values('Q3', '2007-08-23', 177.600)
insert into tb values('Q3', '2007-08-08', 11 )
insert into tb values('Q3', '2007-08-09', 18 )
insert into tb values('Q3', '2007-08-10', 154 )
insert into tb values('Q3', '2007-08-11', 96.000 )
insert into tb values('Q3', '2007-08-12', 128.400)
insert into tb values('Q3', '2007-08-13', 105.600)
godeclare @dt as varchar(7)
set @dt = '2007-08'select isnull(b.ennmcd,'--') ennmcd , a.time , isnull(cast(b.qsl as varchar),'--') qsl from
(
select time = dateadd(day , t.id , cast(@dt + '-01' as datetime)) from
(
select 0 as id
union select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 10
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
) t
) a
left join
(
select ennmcd , time = convert(varchar(10),time,120) , qsl = sum(qsl) from tb where time >= cast(@dt+'-01' as datetime) and time < dateadd(month , 1 , cast(@dt+'-01' as datetime)) group by ennmcd , convert(varchar(10),time,120)
) b
on a.time = b.time and a.time < dateadd(month , 1 , cast(@dt + '-01' as datetime))
order by time , b.ennmcddrop table tb/*
ennmcd time qsl
---------- ----------------------- ------------------------------
-- 2007-08-01 00:00:00.000 --
-- 2007-08-02 00:00:00.000 --
-- 2007-08-03 00:00:00.000 --
-- 2007-08-04 00:00:00.000 --
-- 2007-08-05 00:00:00.000 --
-- 2007-08-06 00:00:00.000 --
-- 2007-08-07 00:00:00.000 --
Q1 2007-08-08 00:00:00.000 54.400
Q3 2007-08-08 00:00:00.000 11.000
Q1 2007-08-09 00:00:00.000 185.600
Q3 2007-08-09 00:00:00.000 18.000
Q1 2007-08-10 00:00:00.000 154.400
Q3 2007-08-10 00:00:00.000 154.000
Q1 2007-08-11 00:00:00.000 96.000
Q3 2007-08-11 00:00:00.000 96.000
Q1 2007-08-12 00:00:00.000 128.400
Q3 2007-08-12 00:00:00.000 128.400
Q1 2007-08-13 00:00:00.000 105.600
Q3 2007-08-13 00:00:00.000 105.600
Q2 2007-08-14 00:00:00.000 100.800
Q2 2007-08-15 00:00:00.000 129.200
Q2 2007-08-16 00:00:00.000 145.600
Q2 2007-08-17 00:00:00.000 95.200
Q2 2007-08-18 00:00:00.000 162.800
Q2 2007-08-19 00:00:00.000 161.200
Q2 2007-08-20 00:00:00.000 89.600
Q3 2007-08-22 00:00:00.000 143.200
Q3 2007-08-23 00:00:00.000 177.600
-- 2007-08-24 00:00:00.000 --
-- 2007-08-25 00:00:00.000 --
-- 2007-08-26 00:00:00.000 --
-- 2007-08-27 00:00:00.000 --
-- 2007-08-28 00:00:00.000 --
-- 2007-08-29 00:00:00.000 --
-- 2007-08-30 00:00:00.000 --
-- 2007-08-31 00:00:00.000 --(36 行受影响)
*/
declare @dt datetime,@d int
set @dt='2007-02-01'
select @d=datediff(dd,@dt,dateadd(mm,1,@dt))
delete # where id>@d
select * from #先取到月的天,然后行转列,
insert into tb values('Q1', '2007-08-08', 54.400 )
insert into tb values('Q1', '2007-08-09', 185.600)
insert into tb values('Q1', '2007-08-10', 154.400)
insert into tb values('Q1', '2007-08-11', 96.000 )
insert into tb values('Q1', '2007-08-12', 128.400)
insert into tb values('Q1', '2007-08-13', 105.600)
insert into tb values('Q2', '2007-08-14', 100.800)
insert into tb values('Q2', '2007-08-15', 129.200)
insert into tb values('Q2', '2007-08-16', 145.600)
insert into tb values('Q2', '2007-08-17', 95.200 )
insert into tb values('Q2', '2007-08-18', 162.800)
insert into tb values('Q2', '2007-08-19', 161.200)
insert into tb values('Q2', '2007-08-20', 89.600 )
insert into tb values('Q2', '2007-08-21', 110.400)
insert into tb values('Q3', '2007-08-22', 143.200)
insert into tb values('Q3', '2007-08-23', 177.600)
insert into tb values('Q3', '2007-08-08', 11 )
insert into tb values('Q3', '2007-08-09', 18 )
insert into tb values('Q3', '2007-08-10', 154 )
insert into tb values('Q3', '2007-08-11', 96.000 )
insert into tb values('Q3', '2007-08-12', 128.400)
insert into tb values('Q3', '2007-08-13', 105.600)
godeclare @dt as varchar(7)
set @dt = '2007-08'select 天 = right('00' + datename(day , a.time),2) ,
max(case b.ennmcd when 'Q1' then isnull(cast(b.qsl as varchar),'--') else '--' end ) 'Q1',
max(case b.ennmcd when 'Q2' then isnull(cast(b.qsl as varchar),'--') else '--' end ) 'Q2',
max(case b.ennmcd when 'Q3' then isnull(cast(b.qsl as varchar),'--') else '--' end ) 'Q3'
from
(
select time = dateadd(day , t.id , cast(@dt + '-01' as datetime)) from
(
select 0 as id
union select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 10
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
) t
) a
left join
(
select ennmcd , time = convert(varchar(10),time,120) , qsl = sum(qsl) from tb where time >= cast(@dt+'-01' as datetime) and time < dateadd(month , 1 , cast(@dt+'-01' as datetime)) group by ennmcd , convert(varchar(10),time,120)
) b
on a.time = b.time and a.time < dateadd(month , 1 , cast(@dt + '-01' as datetime))
group by right('00' + datename(day , a.time),2)drop table tb/*
天 Q1 Q2 Q3
---- ------------------------------ ------------------------------ ------------------------------
01 -- -- --
02 -- -- --
03 -- -- --
04 -- -- --
05 -- -- --
06 -- -- --
07 -- -- --
08 54.400 -- 11.000
09 185.600 -- 18.000
10 154.400 -- 154.000
11 96.000 -- 96.000
12 128.400 -- 128.400
13 105.600 -- 105.600
14 -- 100.800 --
15 -- 129.200 --
16 -- 145.600 --
17 -- 95.200 --
18 -- 162.800 --
19 -- 161.200 --
20 -- 89.600 --
22 -- -- 143.200
23 -- -- 177.600
24 -- -- --
25 -- -- --
26 -- -- --
27 -- -- --
28 -- -- --
29 -- -- --
30 -- -- --
31 -- -- --(30 行受影响)
*/
insert into tb values('Q1', '2007-08-08', 54.400 )
insert into tb values('Q1', '2007-08-09', 185.600)
insert into tb values('Q1', '2007-08-10', 154.400)
insert into tb values('Q1', '2007-08-11', 96.000 )
insert into tb values('Q1', '2007-08-12', 128.400)
insert into tb values('Q1', '2007-08-13', 105.600)
insert into tb values('Q2', '2007-08-14', 100.800)
insert into tb values('Q2', '2007-08-15', 129.200)
insert into tb values('Q2', '2007-08-16', 145.600)
insert into tb values('Q2', '2007-08-17', 95.200 )
insert into tb values('Q2', '2007-08-18', 162.800)
insert into tb values('Q2', '2007-08-19', 161.200)
insert into tb values('Q2', '2007-08-20', 89.600 )
insert into tb values('Q2', '2007-08-21', 110.400)
insert into tb values('Q3', '2007-08-22', 143.200)
insert into tb values('Q3', '2007-08-23', 177.600)
insert into tb values('Q3', '2007-08-08', 11 )
insert into tb values('Q3', '2007-08-09', 18 )
insert into tb values('Q3', '2007-08-10', 154 )
insert into tb values('Q3', '2007-08-11', 96.000 )
insert into tb values('Q3', '2007-08-12', 128.400)
insert into tb values('Q3', '2007-08-13', 105.600)
godeclare @dt as varchar(7)
set @dt = '2007-08'--第一个
select 天 = right('00' + datename(day , a.time),2) ,
max(case b.ennmcd when 'Q1' then isnull(cast(b.qsl as varchar),'--') else '--' end ) 'Q1',
max(case b.ennmcd when 'Q2' then isnull(cast(b.qsl as varchar),'--') else '--' end ) 'Q2',
max(case b.ennmcd when 'Q3' then isnull(cast(b.qsl as varchar),'--') else '--' end ) 'Q3'
from
(
select time = dateadd(day , t.id , cast(@dt + '-01' as datetime)) from
(
select 0 as id
union select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 20
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
) t
) a
left join
(
select ennmcd , time = convert(varchar(10),time,120) , qsl = sum(qsl) from tb where time >= cast(@dt+'-01' as datetime) and time < dateadd(month , 1 , cast(@dt+'-01' as datetime)) group by ennmcd , convert(varchar(10),time,120)
) b
on a.time = b.time and a.time < dateadd(month , 1 , cast(@dt + '-01' as datetime))
group by right('00' + datename(day , a.time),2)
/*
天 Q1 Q2 Q3
---- ------------------------------ ------------------------------ ------------------------------
01 -- -- --
02 -- -- --
03 -- -- --
04 -- -- --
05 -- -- --
06 -- -- --
07 -- -- --
08 54.400 -- 11.000
09 185.600 -- 18.000
10 154.400 -- 154.000
11 96.000 -- 96.000
12 128.400 -- 128.400
13 105.600 -- 105.600
14 -- 100.800 --
15 -- 129.200 --
16 -- 145.600 --
17 -- 95.200 --
18 -- 162.800 --
19 -- 161.200 --
20 -- 89.600 --
21 -- 110.400 --
22 -- -- 143.200
23 -- -- 177.600
24 -- -- --
25 -- -- --
26 -- -- --
27 -- -- --
28 -- -- --
29 -- -- --
30 -- -- --
31 -- -- --(31 行受影响)
*/
select b.ennmcd ,
max(case day(a.time) when 1 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '01',
max(case day(a.time) when 2 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '02',
max(case day(a.time) when 3 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '03',
max(case day(a.time) when 4 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '04',
max(case day(a.time) when 5 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '05',
max(case day(a.time) when 6 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '06',
max(case day(a.time) when 7 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '07',
max(case day(a.time) when 8 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '08',
max(case day(a.time) when 9 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '09',
max(case day(a.time) when 10 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '10',
max(case day(a.time) when 11 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '11',
max(case day(a.time) when 12 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '12',
max(case day(a.time) when 13 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '13',
max(case day(a.time) when 14 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '14',
max(case day(a.time) when 15 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '15',
max(case day(a.time) when 16 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '16',
max(case day(a.time) when 17 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '17',
max(case day(a.time) when 18 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '18',
max(case day(a.time) when 19 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '19',
max(case day(a.time) when 20 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '20',
max(case day(a.time) when 21 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '21',
max(case day(a.time) when 22 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '22',
max(case day(a.time) when 23 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '23',
max(case day(a.time) when 24 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '24',
max(case day(a.time) when 25 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '25',
max(case day(a.time) when 26 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '26',
max(case day(a.time) when 27 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '27',
max(case day(a.time) when 28 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '28',
max(case day(a.time) when 29 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '29',
max(case day(a.time) when 30 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '30',
max(case day(a.time) when 31 then isnull(cast(b.qsl as varchar),'--') else '--' end ) '31'
from
(
select time = dateadd(day , t.id , cast(@dt + '-01' as datetime)) from
(
select 0 as id
union select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 10
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
) t
) a
left join
(
select ennmcd , time = convert(varchar(10),time,120) , qsl = sum(qsl) from tb where time >= cast(@dt+'-01' as datetime) and time < dateadd(month , 1 , cast(@dt+'-01' as datetime)) group by ennmcd , convert(varchar(10),time,120)
) b
on a.time = b.time and a.time < dateadd(month , 1 , cast(@dt + '-01' as datetime))
group by b.ennmcd
order by b.ennmcd
/*
ennmcd 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
Q1 -- -- -- -- -- -- -- 54.400 185.600 154.400 96.000 128.400 105.600 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Q2 -- -- -- -- -- -- -- -- -- -- -- -- -- 100.800 129.200 145.600 95.200 162.800 161.200 89.600 -- -- -- -- -- -- -- -- -- -- --
Q3 -- -- -- -- -- -- -- 11.000 18.000 154.000 96.000 128.400 105.600 -- -- -- -- -- -- -- -- 143.200 177.600 -- -- -- -- -- -- -- --(3 行受影响)
*/
偶水平比较差!
还有一个疑问哦,如果是二月的话,那么天数只有28天了,这个能不能也是动态的变化的啊?
我刚刚在试的 那个 动态SQL,指subject不止语文、数学、物理这三门课程。
这里的时候发现老是会跑错哦,我的表名为[2210_qsy]
我代码是这样写的,不知道错哪里
declare @sql varchar(8000)
set @sql = 'select sssj as ' + '名称'
select @sql = @sql + ' , max(case ennmcd when ''' + ennmcd + ''' then qsl else 0 end) [' + ennmcd + ']'
from (select distinct ennmcd from [2210_dayqsl]) as a
set @sql = @sql + ' from 2210_dayqsl group by ennmcd'
exec(@sql)
错误:
服务器: 消息 105,级别 15,状态 1,行 1
字符串 'Qnbyz0 from [2210_dayqsl] group by ennmcd' 之前有未闭合的引号。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'Qnbyz0 from [2210_dayqsl] group by ennmcd' 附近有语法错误。
我去掉[]也是不行的
日期要动态,那个ennmcd也是要动态的,好像是比较麻烦
create table tb(ennmcd varchar(10), time datetime, qsl decimal(18,3))
insert into tb values('Q1', '2007-08-08', 54.400 )
insert into tb values('Q1', '2007-08-09', 185.600)
insert into tb values('Q1', '2007-08-10', 154.400)
insert into tb values('Q1', '2007-08-11', 96.000 )
insert into tb values('Q1', '2007-08-12', 128.400)
insert into tb values('Q1', '2007-08-13', 105.600)
insert into tb values('Q2', '2007-08-14', 100.800)
insert into tb values('Q2', '2007-08-15', 129.200)
insert into tb values('Q2', '2007-08-16', 145.600)
insert into tb values('Q2', '2007-08-17', 95.200 )
insert into tb values('Q2', '2007-08-18', 162.800)
insert into tb values('Q2', '2007-08-19', 161.200)
insert into tb values('Q2', '2007-08-20', 89.600 )
insert into tb values('Q2', '2007-08-21', 110.400)
insert into tb values('Q3', '2007-08-22', 143.200)
insert into tb values('Q3', '2007-08-23', 177.600)
insert into tb values('Q3', '2007-08-08', 11 )
insert into tb values('Q3', '2007-08-09', 18 )
insert into tb values('Q3', '2007-08-10', 154 )
insert into tb values('Q3', '2007-08-11', 96.000 )
insert into tb values('Q3', '2007-08-12', 128.400)
insert into tb values('Q3', '2007-08-13', 105.600)
go
declare @dt as varchar(7)
set @dt = '2007-08' select time = dateadd(day , t.id , cast(@dt + '-01' as datetime)) into temp from
(
select 0 as id
union select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 20
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
) t --第一个
declare @sql varchar(8000)
set @sql = 'select 天 = right(''00'' + datename(day , a.time),2)'
select @sql = @sql + ' , max(case b.ennmcd when ''' + ennmcd + ''' then isnull(cast(b.qsl as varchar),''--'') else ''--'' end) [' + ennmcd + ']'
from (select distinct ennmcd from tb) as a
set @sql = @sql + ' from temp a
left join
(
select ennmcd , time = convert(varchar(10),time,120) , qsl = sum(qsl) from tb where time >= cast(''' + @dt + '''+''-01'' as datetime) and time < dateadd(month , 1 , cast(''' + @dt + '''+''-01'' as datetime)) group by ennmcd , convert(varchar(10),time,120)
) b
on a.time = b.time and a.time < dateadd(month , 1 , cast('''+@dt +'''+ ''-01'' as datetime)) group by right(''00'' + datename(day , a.time),2)'
exec(@sql) drop table tb,temp/*
天 Q1 Q2 Q3
---- ------------------------------ ------------------------------ ------------------------------
01 -- -- --
02 -- -- --
03 -- -- --
04 -- -- --
05 -- -- --
06 -- -- --
07 -- -- --
08 54.400 -- 11.000
09 185.600 -- 18.000
10 154.400 -- 154.000
11 96.000 -- 96.000
12 128.400 -- 128.400
13 105.600 -- 105.600
14 -- 100.800 --
15 -- 129.200 --
16 -- 145.600 --
17 -- 95.200 --
18 -- 162.800 --
19 -- 161.200 --
20 -- 89.600 --
21 -- 110.400 --
22 -- -- 143.200
23 -- -- 177.600
24 -- -- --
25 -- -- --
26 -- -- --
27 -- -- --
28 -- -- --
29 -- -- --
30 -- -- --
31 -- -- --(31 行受影响)*/
create table a(ennmcd char(2),time varchar(10),qsl decimal(20,3))
insert a select 'Q1', '2007-08-08', 54.400
union all select 'Q1', '2007-08-09', 185.600
union all select 'Q1', '2007-08-10', 154.400
union all select 'Q1', '2007-08-11', 96.000
union all select 'Q1', '2007-08-12', 128.400
union all select 'Q1', '2007-08-13', 105.600
union all select 'Q2', '2007-08-14', 100.800
union all select 'Q2', '2007-08-15', 129.200
union all select 'Q2', '2007-08-16', 145.600
union all select 'Q2', '2007-08-17', 95.200
union all select 'Q2', '2007-08-18', 162.800
union all select 'Q2', '2007-08-19', 161.200
union all select 'Q2', '2007-08-20', 89.600
union all select 'Q2', '2007-08-21', 110.400
union all select 'Q3', '2007-08-22', 143.200
union all select 'Q3', '2007-08-23', 177.600
union all select 'Q3', '2007-08-08', 11
union all select 'Q3', '2007-08-09', 18
union all select 'Q3', '2007-08-10', 154
union all select 'Q3', '2007-08-11', 96.000
union all select 'Q3', '2007-08-12', 128.400
union all select 'Q3', '2007-08-13', 105.600
union all select 'Q3', '2009-08-13', 999.600 create table #t (id int identity(0,1),a int)
create table #s(r varchar(20),rq varchar(10))
insert #t select top 31 0 from syscolumns
declare @date int,@month int
select @date=2007,@month=8insert #s select right(100+id+1,2)+'日' r,convert(varchar(10),dateadd(day,id,ltrim(@date)+'-'+ltrim(@month)+'-01'),120) rq from #tdeclare @sql varchar(8000)
set @sql='select r'
select @sql=@sql+', sum(case when ennmcd='''+ennmcd+''' then qsl else 0 end) ['+ennmcd+']' from a group by ennmcdset @sql=@sql+' from a right join #s b on a.time=b.rq group by r'
exec(@sql)set @sql='select ennmcd'
select @sql=@sql+', sum(case when day(time)='+replace(r,'日','')+' then qsl else 0 end) ['+r+']' from a right join #s b on a.time=b.rq group by rselect @sql=@sql+' from a where left(time,7)='''+ltrim(@date)+'-'+right(100+@month,2)+''' group by ennmcd'exec(@sql)drop table #t,#s
/*
r Q1 Q2 Q3
-------------------- ------------------------------ ------------------------------ ------------------------------
01日 .000 .000 .000
02日 .000 .000 .000
03日 .000 .000 .000
04日 .000 .000 .000
05日 .000 .000 .000
06日 .000 .000 .000
07日 .000 .000 .000
08日 54.400 .000 11.000
09日 185.600 .000 18.000
10日 154.400 .000 154.000
11日 96.000 .000 96.000
12日 128.400 .000 128.400
13日 105.600 .000 105.600
14日 .000 100.800 .000
15日 .000 129.200 .000
16日 .000 145.600 .000
17日 .000 95.200 .000
18日 .000 162.800 .000
19日 .000 161.200 .000
20日 .000 89.600 .000
21日 .000 110.400 .000
22日 .000 .000 143.200
23日 .000 .000 177.600
24日 .000 .000 .000
25日 .000 .000 .000
26日 .000 .000 .000
27日 .000 .000 .000
28日 .000 .000 .000
29日 .000 .000 .000
30日 .000 .000 .000
31日 .000 .000 .000
ennmcd 01日 02日 03日 04日 05日 06日 07日 08日 09日 10日 11日 12日 13日 14日 15日 16日 17日 18日 19日 20日 21日 22日 23日 24日 25日 26日 27日 28日 29日 30日 31日
------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
Q1 .000 .000 .000 .000 .000 .000 .000 54.400 185.600 154.400 96.000 128.400 105.600 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000
Q2 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000 100.800 129.200 145.600 95.200 162.800 161.200 89.600 110.400 .000 .000 .000 .000 .000 .000 .000 .000 .000 .000
Q3 .000 .000 .000 .000 .000 .000 .000 11.000 18.000 154.000 96.000 128.400 105.600 .000 .000 .000 .000 .000 .000 .000 .000 143.200 177.600 .000 .000 .000 .000 .000 .000 .000 .000*/
create table tb(ennmcd varchar(10), time datetime, qsl decimal(18,3))
insert into tb values('Q1', '2007-08-08', 54.400 )
insert into tb values('Q1', '2007-08-09', 185.600)
insert into tb values('Q1', '2007-08-10', 154.400)
insert into tb values('Q1', '2007-08-11', 96.000 )
insert into tb values('Q1', '2007-08-12', 128.400)
insert into tb values('Q1', '2007-08-13', 105.600)
insert into tb values('Q2', '2007-08-14', 100.800)
insert into tb values('Q2', '2007-08-15', 129.200)
insert into tb values('Q2', '2007-08-16', 145.600)
insert into tb values('Q2', '2007-08-17', 95.200 )
insert into tb values('Q2', '2007-08-18', 162.800)
insert into tb values('Q2', '2007-08-19', 161.200)
insert into tb values('Q2', '2007-08-20', 89.600 )
insert into tb values('Q2', '2007-08-21', 110.400)
insert into tb values('Q3', '2007-08-22', 143.200)
insert into tb values('Q3', '2007-08-23', 177.600)
insert into tb values('Q3', '2007-08-08', 11 )
insert into tb values('Q3', '2007-08-09', 18 )
insert into tb values('Q3', '2007-08-10', 154 )
insert into tb values('Q3', '2007-08-11', 96.000 )
insert into tb values('Q3', '2007-08-12', 128.400)
insert into tb values('Q3', '2007-08-13', 105.600)
go
declare @dt as varchar(7)
set @dt = '2007-08'--第一个
declare @sql varchar(8000)
set @sql = 'select 天 = right(''00'' + datename(day , a.time),2)'
select @sql = @sql + ' , max(case b.ennmcd when ''' + ennmcd + ''' then isnull(cast(b.qsl as varchar),''--'') else ''--'' end) [' + ennmcd + ']'
from (select distinct ennmcd from tb) as a
set @sql = @sql + ' from (
select time = dateadd(day , t.id , cast('''+@dt+''' + ''-01'' as datetime)) from
(
select 0 as id
union select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 20
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
) t
) a
left join
(
select ennmcd , time = convert(varchar(10),time,120) , qsl = sum(qsl) from tb where time >= cast('''+@dt+'''+''-01'' as datetime) and time < dateadd(month , 1 , cast('''+@dt+'''+''-01'' as datetime)) group by ennmcd , convert(varchar(10),time,120)
) b
on a.time = b.time and a.time < dateadd(month , 1 , cast('''+@dt+''' + ''-01'' as datetime))
group by right(''00'' + datename(day , a.time),2)'exec(@sql) drop table tb/*
天 Q1 Q2 Q3
---- ------------------------------ ------------------------------ ------------------------------
01 -- -- --
02 -- -- --
03 -- -- --
04 -- -- --
05 -- -- --
06 -- -- --
07 -- -- --
08 54.400 -- 11.000
09 185.600 -- 18.000
10 154.400 -- 154.000
11 96.000 -- 96.000
12 128.400 -- 128.400
13 105.600 -- 105.600
14 -- 100.800 --
15 -- 129.200 --
16 -- 145.600 --
17 -- 95.200 --
18 -- 162.800 --
19 -- 161.200 --
20 -- 89.600 --
21 -- 110.400 --
22 -- -- 143.200
23 -- -- 177.600
24 -- -- --
25 -- -- --
26 -- -- --
27 -- -- --
28 -- -- --
29 -- -- --
30 -- -- --
31 -- -- --(31 行受影响)*/
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
create table tb(ennmcd varchar(10), time datetime, qsl decimal(18,3))
insert into tb values('Q1', '2007-08-08', 54.400 )
insert into tb values('Q1', '2007-08-09', 185.600)
insert into tb values('Q1', '2007-08-10', 154.400)
insert into tb values('Q1', '2007-08-11', 96.000 )
insert into tb values('Q1', '2007-08-12', 128.400)
insert into tb values('Q1', '2007-08-13', 105.600)
insert into tb values('Q2', '2007-08-14', 100.800)
insert into tb values('Q2', '2007-08-15', 129.200)
insert into tb values('Q2', '2007-08-16', 145.600)
insert into tb values('Q2', '2007-08-17', 95.200 )
insert into tb values('Q2', '2007-08-18', 162.800)
insert into tb values('Q2', '2007-08-19', 161.200)
insert into tb values('Q2', '2007-08-20', 89.600 )
insert into tb values('Q2', '2007-08-21', 110.400)
insert into tb values('Q3', '2007-08-22', 143.200)
insert into tb values('Q3', '2007-08-23', 177.600)
insert into tb values('Q3', '2007-08-08', 11 )
insert into tb values('Q3', '2007-08-09', 18 )
insert into tb values('Q3', '2007-08-10', 154 )
insert into tb values('Q3', '2007-08-11', 96.000 )
insert into tb values('Q3', '2007-08-12', 128.400)
insert into tb values('Q3', '2007-08-13', 105.600)
go
--第一题
create proc ssss
@month int --生成某月的数据
as
if @month<1 or @month >12
begin
raiserror('输入月份无效',16,1)
return
end
else
begin
create table #temp(date datetime)
declare @count int--得出该月有多少天
declare @num int
set @num=1
set @count=datediff(dd,convert(varchar(10),'2007-'+cast(@month as varchar)+'-01',120),convert(varchar(10),'2007-'+cast((@month+1) as varchar)+'-01',120))
while(@num<=@count)
begin
insert into #temp select convert(varchar(10),'2007-'+cast(@month as varchar)+'-'+cast(@num as varchar),120)
set @num=@num+1
end
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+ennmcd+']=max(case ennmcd when '''+ennmcd+''' then qsl else 0 end)' from (select distinct ennmcd from tb)a
set @sql='select datename(dd,b.date)+''日'' [日]'+@sql+' from tb a right join #temp b on a.time=b.date group by b.date'
exec(@sql)
end--生成8月份的数据:
exec ssss 8
--第二题:
create proc pppp
@month int --生成某月的数据
as
if @month<1 or @month >12
begin
raiserror('输入月份无效',16,1)
return
end
else
begin
create table #temp1(date datetime)
declare @count int--得出该月有多少天
declare @num int
set @num=1
set @count=datediff(dd,convert(varchar(10),'2007-'+cast(@month as varchar)+'-01',120),convert(varchar(10),'2007-'+cast((@month+1) as varchar)+'-01',120))
while(@num<=@count)
begin
insert into #temp1 select convert(varchar(10),'2007-'+cast(@month as varchar)+'-'+cast(@num as varchar),120)
set @num=@num+1
end
declare @sql varchar(8000)
set @sql='select distinct ennmcd'
select @sql=@sql+',['+datename(dd,date)+'日]=max(case datename(dd,time) when '''+datename(dd,date)+''' then qsl else 0 end)' from #temp1
set @sql=@sql+' from tb group by ennmcd '
exec(@sql)
end--生成8月份的数据:
exec pppp 8
但是放到我实际的库里,执行的时候老是出错
我的表名为:2210_dayqsl
内容:
"ennmcd","sssj","qsl","fdl","bjzt","gdzt","kxzt","mtzt","ssqsl","gdztmc","bjztmc","mtztmc","kxztmc","bjlx"
"Qsxsqcej0160B",2007-11-10 00:00:00,14784.000,14784.000,1,1,0,1,,"交流","工作","正常","打开",2
"Qsxsqcej0103B",2007-11-10 00:00:00,.000,.000,1,0,0,1,,"直流","工作","正常","打开",1
"Qsxsqcej0093B",2007-11-10 00:00:00,12.500,500.000,1,0,0,1,,"直流","工作","正常","打开",1
"Qsxsqcej0082B",2007-11-10 00:00:00,6.050,242.000,1,0,0,1,,"直流","工作","正常","打开",1
"Qsxsqcej0062B",2007-11-10 00:00:00,2.930,117.200,0,0,0,1,,"直流","停止","正常","打开",1
"Qsxsqcej0033B",2007-11-10 00:00:00,4.980,199.200,1,0,0,1,,"直流","工作","正常","打开",1
"Qsxsqcej0126B",2007-11-10 00:00:00,4.380,175.200,1,0,0,1,,"直流","工作","正常","打开",1
"Qsxsqcej0119B",2007-11-10 00:00:00,4.170,166.800,1,0,0,1,,"直流","工作","正常","打开",1
"Qsxsqcej0135B",2007-11-10 00:00:00,1164.000,1164.000,1,1,0,1,,"交流","工作","正常","打开",2
"Qsxsqcej0169B",2007-11-10 00:00:00,1422.000,1422.000,1,0,0,1,,"直流","工作","正常","打开",2
"Qsxsqcej0049B",2007-11-10 00:00:00,3655.000,3655.000,1,1,0,1,,"交流","工作","正常","打开",2
"Qsxsqcej0154B",2007-11-10 00:00:00,3682.000,3682.000,0,0,0,1,,"直流","停止","正常","打开",2
"Qsxsqcej0155B",2007-11-10 00:00:00,437.000,437.000,0,0,0,1,,"直流","停止","正常","打开",2
"QSxsxcej0325B",2007-11-10 00:00:00,1082.000,1082.000,1,1,0,1,,"交流","工作","正常","打开",2
哪位高手能帮我再测试下,谢谢!如果可以的话加我msn:[email protected]或者QQ:156653988
提供更详细的数据,再次感谢
QQ上说吧,这里说不清楚`
--第一题
CREATE proc ssss
@year int,
@month int --生成某月的数据
as
if @month<1 or @month >12
begin
raiserror('输入月份无效',16,1)
return
end
else
begin
create table #temp(date datetime)
declare @count int--得出该月有多少天
declare @num int
set @num=1
set @count=datediff(dd,convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-01',120),convert(varchar(10),cast(@year as varchar)+'-'+cast((@month+1) as varchar)+'-01',120))
while(@num<=@count)
begin
insert into #temp select convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@num as varchar),120)
set @num=@num+1
end
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+ennmcd+']=max(case ennmcd when '''+ennmcd+''' then isnull(cast(nullif(qsl,0) as varchar),''--'') else ''--'' end)' from
(select distinct ennmcd from [2210_dayqsl] where datepart(yy,sssj)=@year and datepart(mm,sssj)=@month)a
set @sql='select datename(dd,b.date)+''日'' [日]'+@sql+' from [2210_dayqsl] a right join #temp b on a.sssj=b.date group by b.date order by datepart(dd,b.date)'
exec(@sql)
end--生成8月份的数据:
exec ssss 2007,8
--第二题:
CREATE proc pppp
@year int,
@month int --生成某月的数据
as
if @month<1 or @month >12
begin
raiserror('输入月份无效',16,1)
return
end
else
begin
create table #temp1(date datetime)
declare @count int--得出该月有多少天
declare @num int
set @num=1
set @count=datediff(dd,convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-01',120),convert(varchar(10),cast(@year as varchar)+'-'+cast((@month+1) as varchar)+'-01',120))
while(@num<=@count)
begin
insert into #temp1 select convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@num as varchar),120)
set @num=@num+1
end
declare @sql varchar(8000)
set @sql='select distinct ennmcd'
select @sql=@sql+',['+datename(dd,date)+'日]=max(case datename(dd,sssj) when '''+datename(dd,date)+''' then isnull(cast(nullif(qsl,0) as varchar),''--'') else ''--'' end)' from #temp1
set @sql=@sql+' from [2210_dayqsl] where datepart(yy,sssj)='+ cast(@year as varchar) +' and datepart(mm,sssj)='+cast(@month as varchar)+' group by ennmcd '
exec(@sql)
end--生成8月份的数据:
exec pppp 2007,8
create proc mon
@year int--生成某月的数据
as
create table #temp(date int)
declare @num int
set @num=1
while(@num<=12)
begin
insert into #temp select @num
set @num=@num+1
end
declare @sql varchar(8000)
set @sql='select distinct ennmcd'
select @sql=@sql+',['+cast(date as varchar)+'月]=max(case datename(mm,cast(sssj+''-01'' as datetime)) when '''+right('00'+cast(date as varchar),2)+''' then ma-mi else 0 end)' from #temp
set @sql=@sql+' from (select ennmcd,convert(varchar(7),sssj,120) sssj,max(qsl) ma,min(qsl) mi from [2210_dayqsl]
where datepart(yy,sssj)='+cast(@year as varchar)+' group by ennmcd,convert(varchar(7),sssj,120))a
where datename(yy,cast(sssj+''-01'' as datetime))='+ cast(@year as varchar) +' group by ennmcd '
exec(@sql)