CREATE TABLE [dbo].[table1](
[ItemCode] [nvarchar](50) NULL,
[pz] [int] NULL,
[RK] [int] NULL,
[SG] [int] NULL,
[XG] [int] NULL,
[dateAndTime] [datetime] NULL,
[date_rk] [datetime] NULL,
[date_sg] [datetime] NULL,
[date_xg] [datetime] NULL,
)insert into table1
select '30861000182',1,0,0,0,'2011-08-15', '2011-08-15', '2011-08-15','2011-08-17'
union all
select '30861900142',0,1,0,0,'2011-08-17', '2011-08-15', '2011-08-16','2011-08-16'
union all
select '30861900257',0,1,0,0,'2011-08-14', '2011-08-15', '2011-08-16','2011-08-16'
union all
select '30861900257',0,1,0,0,'2011-08-15', '2011-08-15', '2011-08-16','2011-08-16'
select * FROM table1表设计原先需求没做好。现在只有这样对应字段
pz 对应 dateAndTime
rk 对应 date_pz
sg 对应 date_sg
xg 对应 date_xg就是这4个字段分别有自己对应的时间。然后分别按天数来统计这4个字段的对应的数据。
对表table1 ,现在想要统计出的效果为
itemcode pz(08-14) rk(08-14) sg(08-14) xg(08-14) pz(08-15) rk(08-15) sg(08-15) xg(08-15).... (08-17)30861000182 0 0 0 0 1 0 0 0
30861900142 0 0 0 0 0 1 0 0
30861900257 0 0 0 0 0 2 0 0
这个太复杂了。不懂咋实现。
[ItemCode] [nvarchar](50) NULL,
[pz] [int] NULL,
[RK] [int] NULL,
[SG] [int] NULL,
[XG] [int] NULL,
[dateAndTime] [datetime] NULL,
[date_rk] [datetime] NULL,
[date_sg] [datetime] NULL,
[date_xg] [datetime] NULL,
)insert into table1
select '30861000182',1,0,0,0,'2011-08-15', '2011-08-15', '2011-08-15','2011-08-17'
union all
select '30861900142',0,1,0,0,'2011-08-17', '2011-08-15', '2011-08-16','2011-08-16'
union all
select '30861900257',0,1,0,0,'2011-08-14', '2011-08-15', '2011-08-16','2011-08-16'
union all
select '30861900257',0,1,0,0,'2011-08-15', '2011-08-15', '2011-08-16','2011-08-16'
select * FROM table1表设计原先需求没做好。现在只有这样对应字段
pz 对应 dateAndTime
rk 对应 date_pz
sg 对应 date_sg
xg 对应 date_xg就是这4个字段分别有自己对应的时间。然后分别按天数来统计这4个字段的对应的数据。
对表table1 ,现在想要统计出的效果为
itemcode pz(08-14) rk(08-14) sg(08-14) xg(08-14) pz(08-15) rk(08-15) sg(08-15) xg(08-15).... (08-17)30861000182 0 0 0 0 1 0 0 0
30861900142 0 0 0 0 0 1 0 0
30861900257 0 0 0 0 0 2 0 0
这个太复杂了。不懂咋实现。
, @tblTmprk varchar(10)
, @tblTmpsg varchar(10)
, @tblTmpxg varchar(10)
if object_id('dbo.TmpPZ', 'U') is not null
drop table dbo.TmpPZ
if object_id('dbo.Tmprk', 'U') is not null
drop table dbo.Tmprk
if object_id('dbo.Tmpsg', 'U') is not null
drop table dbo.Tmpsg
if object_id('dbo.Tmpxg', 'U') is not null
drop table dbo.Tmpxg declare @Cols as nvarchar(max),
@y as int,
@sql as nvarchar(max);-- process pz
set @Cols = stuff(
(select N',' + QUOTENAME([pzDate]) as [text()]
from (select distinct 'pz' + convert(varchar, [dateAndTime] , 101) as [pzDate] from dbo.table1 ) as d
order by [pzDate]
for xml path(''))
,1,1,N'');
set @sql = N'select *
into dbo.TmpPZ
from (select [ItemCode], ''pz'' + convert(varchar, [dateAndTime] , 101) as [pzDate],[pz]
from dbo.table1) as D
pivot (sum([pz]) FOR pzDate IN (' + @cols + N')) as p;';
exec sp_executesql @sql ;
-- process rk
set @Cols = stuff(
(select N',' + QUOTENAME([rkDate]) as [text()]
from (select distinct 'rk' + convert(varchar, [date_rk] , 101) as [rkDate] from dbo.table1 ) as d
order by [rkDate]
for xml path(''))
,1,1,N'');
set @sql = N'select *
into dbo.Tmprk
from (select [ItemCode], ''rk'' + convert(varchar, [date_rk] , 101) as [rkDate],[rk]
from dbo.table1) as D
pivot (sum([rk]) FOR rkDate IN (' + @cols + N')) as p;';
exec sp_executesql @sql ;-- process sg
set @Cols = stuff(
(select N',' + QUOTENAME([sgDate]) as [text()]
from (select distinct 'sg' + convert(varchar, [date_sg] , 101) as [sgDate] from dbo.table1 ) as d
order by [sgDate]
for xml path(''))
,1,1,N'');
set @sql = N'select *
into dbo.Tmpsg
from (select [ItemCode], ''sg'' + convert(varchar, [date_sg] , 101) as [sgDate],[sg]
from dbo.table1) as D
pivot (sum([sg]) FOR sgDate IN (' + @cols + N')) as p;';
exec sp_executesql @sql ;-- process xg
set @Cols = stuff(
(select N',' + QUOTENAME([xgDate]) as [text()]
from (select distinct 'xg' + convert(varchar, [date_xg] , 101) as [xgDate] from dbo.table1 ) as d
order by [xgDate]
for xml path(''))
,1,1,N'');
set @sql = N'select *
into dbo.Tmpxg
from (select [ItemCode], ''xg'' + convert(varchar, [date_xg] , 101) as [xgDate],[xg]
from dbo.table1) as D
pivot (sum([xg]) FOR xgDate IN (' + @cols + N')) as p;';
exec sp_executesql @sql ;select *
from dbo.Tmppz pz
join dbo.Tmprk rk
on rk.ItemCode = pz.ItemCode
join dbo.Tmpsg sg
on sg.ItemCode = rk.ItemCode
join dbo.Tmpxg xg
on xg.ItemCode = sg.ItemCode
OPTION (MERGE JOIN);
,max(case when convert(varchar(10),dateAndTime,120) = '2011-08-14' then [pz] end) as [pz(08-14)]
,max(case when convert(varchar(10),[date_rk],120) = '2011-08-14' then [rk] end) as [rk(08-14)]
,max(case when convert(varchar(10),[date_sg],120) = '2011-08-14' then [sg] end) as [sg(08-14)]
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-14' then [xg] end) as [xg(08-14)]
...
,max(case when convert(varchar(10),[date_xg],120) = '2011-08-17' then [xg] end) as [xg(08-17)]
from tab
group by itemCode
在程序或者存储过程拼接出你要查询的时间范围的语句