22,209
社区成员
发帖
与我相关
我的任务
分享求一SQL语句
create table #tab
(
col1 char(10),
col2 char(10),
item char(10),
num int,
[Date] varchar(10))
insert #tab values('AAA','BBB','A',50,'2013-06-10')
insert #tab values('ABB','BGG','B',30,'2013-06-10')
insert #tab values('AAA','BBB','C',80,'2013-06-13')
...全文
请发表友善的回复…
发表回复
LongRui888 2013-06-22
- 打赏
- 举报
上面由于是动态生成语句，所以不能用局部的临时表，所以建了一个表。
下面是动态生成的sql语句，经过了格式化：
select case when rownum = 1 then col1 else '' end as col1,
case when rownum = 1 then col2 else '' end as col2,
item,
v_0601 as '6/1',v_0602 as '6/2',v_0603 as '6/3',
v_0604 as '6/4',v_0605 as '6/5',
v_0606 as '6/6',v_0607 as '6/7',
v_0608 as '6/8',v_0609 as '6/9',
v_0610 as '6/10',v_0611 as '6/11',
v_0612 as '6/12',v_0613 as '6/13',
v_0614 as '6/14',v_0615 as '6/15',
v_0616 as '6/16',v_0617 as '6/17',
v_0618 as '6/18',v_0619 as '6/19',
v_0620 as '6/20',v_0621 as '6/21',
v_0622 as '6/22',v_0623 as '6/23',
v_0624 as '6/24',v_0625 as '6/25',
v_0626 as '6/26',v_0627 as '6/27',
v_0628 as '6/28',v_0629 as '6/29',
v_0630 as '6/30'
from
(
select col1,col2,item,
row_number() over(partition by col1,col2 order by item) as rownum,
SUM(case when date ='2013-06-01' then num else 0 end) as v_0601,
SUM(case when date ='2013-06-02' then num else 0 end) as v_0602,
SUM(case when date ='2013-06-03' then num else 0 end) as v_0603,
SUM(case when date ='2013-06-04' then num else 0 end) as v_0604,
SUM(case when date ='2013-06-05' then num else 0 end) as v_0605,
SUM(case when date ='2013-06-06' then num else 0 end) as v_0606,
SUM(case when date ='2013-06-07' then num else 0 end) as v_0607,
SUM(case when date ='2013-06-08' then num else 0 end) as v_0608,
SUM(case when date ='2013-06-09' then num else 0 end) as v_0609,
SUM(case when date ='2013-06-10' then num else 0 end) as v_0610,
SUM(case when date ='2013-06-11' then num else 0 end) as v_0611,
SUM(case when date ='2013-06-12' then num else 0 end) as v_0612,
SUM(case when date ='2013-06-13' then num else 0 end) as v_0613,
SUM(case when date ='2013-06-14' then num else 0 end) as v_0614,
SUM(case when date ='2013-06-15' then num else 0 end) as v_0615,
SUM(case when date ='2013-06-16' then num else 0 end) as v_0616,
SUM(case when date ='2013-06-17' then num else 0 end) as v_0617,
SUM(case when date ='2013-06-18' then num else 0 end) as v_0618,
SUM(case when date ='2013-06-19' then num else 0 end) as v_0619,
SUM(case when date ='2013-06-20' then num else 0 end) as v_0620,
SUM(case when date ='2013-06-21' then num else 0 end) as v_0621,
SUM(case when date ='2013-06-22' then num else 0 end) as v_0622,
SUM(case when date ='2013-06-23' then num else 0 end) as v_0623,
SUM(case when date ='2013-06-24' then num else 0 end) as v_0624,
SUM(case when date ='2013-06-25' then num else 0 end) as v_0625,
SUM(case when date ='2013-06-26' then num else 0 end) as v_0626,
SUM(case when date ='2013-06-27' then num else 0 end) as v_0627,
SUM(case when date ='2013-06-28' then num else 0 end) as v_0628,
SUM(case when date ='2013-06-29' then num else 0 end) as v_0629,
SUM(case when date ='2013-06-30' then num else 0 end) as v_0630
from tab
group by col1,col2,item
) v
LongRui888 2013-06-22
- 打赏
- 举报
create table tab
(
col1 char(10),
col2 char(10),
item char(10),
num int,
[Date] varchar(10)
)
insert tab values('AAA','BBB','A',50,'2013-06-10')
insert tab values('ABB','BGG','B',30,'2013-06-10')
insert tab values('AAA','BBB','C',80,'2013-06-13')
--动态生成sql语句
declare @start_date varchar(10) = '2013-06-01',
@end_date varchar(10) = '2013-06-30';
declare @date varchar(10),
@sql varchar(max) = '',
@sql1 varchar(8000),
@sql2 varchar(8000);
set @date = @start_date;
set @sql1 = 'select case when rownum = 1 then col1 else '''' end as col1,
case when rownum = 1 then col2 else '''' end as col2,
item'
set @sql2 = 'select col1,col2,item,row_number() over(partition by col1,col2
order by item) as rownum'
while @date <= @end_date
begin
set @sql1 = @sql1 + ',v_' + REPLACE( right(@date,5),'-','') +
' as ''' + CAST(DATEPART(month,@date) as varchar) + '/' +
CAST(DATEPART(day,@date) as varchar) +'''';
set @sql2 = @sql2 + ',SUM(case when date =''' + @date +
''' then num else 0 end) as v_' +
REPLACE( right(@date,5),'-','')
set @date = CONVERT(varchar(10),dateadd(day,1,@date),120)
end
set @sql = @sql1 + ' from (' +
@sql2 + ' from tab
group by col1,col2,item' +
') v'
--生产的动态sql语句
select @sql
exec(@sql)
绿豆丫 2013-06-22
- 打赏
- 举报
求你推荐的那本 SQL实战书？？是哪个出版社的，不能给你私信，没办法只能这样跟帖子了
我也很想提高我的SQL[/quote]
呵呵，不好意思啊，刚看到你的回复，
这个是书的链接地址，是人民邮电出版社的，不过看着好像没货了：
http://book.jd.com/10064215.html#none
下面这个是电子版的，csdn上的资源链接地址，你可以看看电子版，你可以下载：
http://download.csdn.net/detail/fksec/5056144[/quote]
非常谢谢，我已经下载电子版了，先看着。有实物书在手上最好了
LongRui888 2013-06-22
- 打赏
- 举报
求你推荐的那本 SQL实战书？？是哪个出版社的，不能给你私信，没办法只能这样跟帖子了
我也很想提高我的SQL[/quote]
呵呵，不好意思啊，刚看到你的回复，
这个是书的链接地址，是人民邮电出版社的，不过看着好像没货了：
http://book.jd.com/10064215.html#none
下面这个是电子版的，csdn上的资源链接地址，你可以看看电子版，你可以下载：
http://download.csdn.net/detail/fksec/5056144
绿豆丫 2013-06-21
- 打赏
- 举报
求你推荐的那本 SQL实战书？？是哪个出版社的，不能给你私信，没办法只能这样跟帖子了
我也很想提高我的SQL
LongRui888 2013-06-21
- 打赏
- 举报
楼上写的，虽然看着有点麻烦，但确实是正确的，而且要写成动态列，也只能这么
写，很难简化。
写，很难简化。
dengixnyu 2013-06-21
- 打赏
- 举报
总共就3步啊。哪里不懂啊？
custom1234 2013-06-21
- 打赏
- 举报
还有没有人再写一个通俗易懂的呀！！！等等等！！！！
dengixnyu 2013-06-21
- 打赏
- 举报
100分有木有?
dengixnyu 2013-06-21
- 打赏
- 举报
---1先编写日期函数把日期段生成
alter function [dbo].[generateTime]
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select maco.date+1 as date from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
end
-----2.调用函数关联tab 放在临时表##a044
go
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##a044'))
drop table ##a044
;with tb as (
select convert(nvarchar(10),date,23) as date1 from dbo.generateTime('2013-06-1','2013-06-30')
)
select tb.date1,tab.* into ##a044 from tb left join tab on tab.date = tb.date1
go
-------3用动态sql列转行
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##tb_5'))
drop table ##tb_5
DECLARE @s varchar(4000)
SET @s='SELECT col1=case when row_number() over (partition by col1,col2 order by col1) <>1 then '''' else col1 end
,col2 = case when row_number() over (partition by col1,col2 order by col1) <>1 then '''' else col2 end
,item '
SELECT @s=@s
+','+QUOTENAME(date1)
+N'=sum(CASE date1 WHEN '+QUOTENAME(date1,N'''')
+N' THEN isnull(num,0) else 0 END)'
FROM ##a044
GROUP BY date1
exec(@s+N'into ##tb_5 FROM ##a044 group by col1,col2,item')
select * from ##tb_5 where col1 is not null
--(31 行受影响)
--
--(4 行受影响)
--
--(3 行受影响)
--AAA BBB A 0 0 0 0 0 0 0 0 0 50 0 0 0 0...
-- C 0 0 0 0 0 0 0 0 0 0 0 0 80 0...
--ABB BGG B 0 0 0 0 0 0 0 0 0 30 0 0 0 0...
这下跟楼主的结果一样了。
不过要注意动态sql
定义这个的varchar长度的时候。 DECLARE @s varchar(4000)
如果日期间隔过长，类似1年，即拼出来的sql太长 ，像这里超过4000 ，就会报错
custom1234 2013-06-21
- 打赏
- 举报
上面截图有一点点错误
重新截图以下：
重新截图以下：
dengixnyu 2013-06-21
- 打赏
- 举报
---1先编写日期函数把日期段生成
alter function [dbo].[generateTime]
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select maco.date+1 as date from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
end
-----2.调用函数关联tab 放在临时表##a044
go
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##tb_5'))
drop table ##a044
;with tb as (
select convert(nvarchar(10),date,23) as date1 from dbo.generateTime('2013-6-1','2013-6-30')
)
select tb.date1,tab.* into ##a044 from tb left join tab on tab.date = tb.date1
go
-------3用动态sql列转行
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##tb_5'))
drop table ##tb_5
DECLARE @s varchar(2000)
SET @s='SELECT col1,col2,item'
SELECT @s=@s
+','+QUOTENAME(date1)
+N'=sum(CASE date1 WHEN '+QUOTENAME(date1,N'''')
+N' THEN num END)'
FROM ##a044
GROUP BY date1
exec(@s+N'into ##tb_5 FROM ##a044 group by col1,col2,item')
select * from ##tb_5 where col1 is not null
--
--(31 行受影响)
--警告: 聚合或其他 SET 操作消除了空值。
--
--(4 行受影响)
--
--(3 行受影响)
--AAA BBB A NULL NULL NULL NULL NULL NULL NULL NULL NULL 50 NULL NULL NULL NULL NULL...
--AAA BBB C NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 80 NULL NULL...
--ABB BGG B NULL NULL NULL NULL NULL NULL NULL NULL NULL 30 NULL NULL NULL NULL NULL...
