首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

关于并行输入的请问

2012-06-23 
关于并行输入的请教create table tb(BillNO varchar(10) , ProdName varchar(10) , PordID varchar(10),P_

关于并行输入的请教

create table tb(BillNO varchar(10) , ProdName varchar(10) , PordID varchar(10),P_num int, other varchar(20))
Insert tb 
Select '21210','螺丝','luosi10',60,'2012-12-12 12:20' union all
Select '21210','螺丝','luosi10',12,'2012-12-20 13:20' union all
Select '21211','螺丝','luosi11',50,'2012-11-12 11:20' union all
Select '21210','螺丝','luosi10',80,'2012-12-18 14:20' union all
Select '21211','螺丝','luosi11',23,'2012-12-21 11:60'union all
Select '21211','螺丝','luosi11',60,'2012-12-22 12:40'
go


期望的输出结果:
编码 名称 型号 数量(sum) 第一次入库 第二次入库 第三次入库 OR 第四次 ……………………
21210 螺丝 luosi10 152 2012-12-12 12:20 2012-12-20 13:20 2012-12-18 14:20
21211 螺丝 luosi11 XX 2012-11-12 11:20 ……………………

[解决办法]
行列转换问题!!!
[解决办法]
参考下 ...

SQL code
--行列互转/******************************************************************************************************************************************************以学生成绩为例子,比较形象易懂整理人:中国风(Roy)日期:2008.06.06******************************************************************************************************************************************************/--1、行互列--> --> (Roy)生成測試數據 if not object_id('Class') is null    drop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'张三',N'语文',78 union allselect N'张三',N'数学',87 union allselect N'张三',N'英语',82 union allselect N'张三',N'物理',90 union allselect N'李四',N'语文',65 union allselect N'李四',N'数学',77 union allselect N'李四',N'英语',65 union allselect N'李四',N'物理',85 Go--2000方法:动态:declare @s nvarchar(4000)set @s=''Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+' from Class group by [Student]')生成静态:select     [Student],    [数学]=max(case when [Course]='数学' then [Score] else 0 end),    [物理]=max(case when [Course]='物理' then [Score] else 0 end),    [英语]=max(case when [Course]='英语' then [Score] else 0 end),    [语文]=max(case when [Course]='语文' then [Score] else 0 end) from     Class group by [Student]GO动态:declare @s nvarchar(4000)Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:select * from     Class pivot     (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student 数学          物理          英语          语文------- ----------- ----------- ----------- -----------李四      77          85          65          65张三      87          90          82          78(2 行受影响)*/------------------------------------------------------go--加上总成绩(学科平均分)--2000方法:动态:declare @s nvarchar(4000)set @s=''Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select     [Student],    [数学]=max(case when [Course]='数学' then [Score] else 0 end),    [物理]=max(case when [Course]='物理' then [Score] else 0 end),    [英语]=max(case when [Course]='英语' then [Score] else 0 end),    [语文]=max(case when [Course]='语文' then [Score] else 0 end),    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))from     Class group by [Student]go--2005方法:动态:declare @s nvarchar(4000)Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select     [Student],[数学],[物理],[英语],[语文],[总成绩] from     (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])pivot     (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*Student 数学          物理          英语          语文          总成绩------- ----------- ----------- ----------- ----------- -----------李四      77          85          65          65          292张三      87          90          82          78          337(2 行受影响)*/go--2、列转行--> --> (Roy)生成測試數據 if not object_id('Class') is null    drop table ClassGoCreate table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)Insert Classselect N'李四',77,85,65,65 union allselect N'张三',87,90,82,78Go--2000:动态:declare @s nvarchar(4000)select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all+',[Score]='+quotename(Name)+' from Class'from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列order by Colidexec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:select * from (select [Student],[Course]='数学',[Score]=[数学] from Class union all select [Student],[Course]='物理',[Score]=[物理] from Class union all select [Student],[Course]='英语',[Score]=[英语] from Class union all select [Student],[Course]='语文',[Score]=[语文] from Class)t order by [Student],[Course]go--2005:动态:declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('Class') and Name not in('Student') order by Colidexec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')goselect     Student,[Course],[Score] from     Class unpivot     ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student Course Score------- ------- -----------李四      数学      77李四      物理      85李四      英语      65李四      语文      65张三      数学      87张三      物理      90张三      英语      82张三      语文      78(8 行受影响)*/ 


[解决办法]

SQL code
create table #tb(    BillNO varchar(10) ,    ProdName varchar(10) ,    PordID varchar(10),    P_num int,     other varchar(20))Insert #tb  Select '21210','螺丝','luosi10',60,'2012-12-12 12:20' union allSelect '21210','螺丝','luosi10',12,'2012-12-20 13:20' union allSelect '21211','螺丝','luosi11',50,'2012-11-12 11:20' union allSelect '21210','螺丝','luosi10',80,'2012-12-18 14:20' union allSelect '21211','螺丝','luosi11',23,'2012-12-21 11:60' union allSelect '21211','螺丝','luosi11',60,'2012-12-22 12:40'go;with T AS(    SELECT BillNO,ProdName,PordID,P_num,other,ROW_NUMBER() OVER (PARTITION BY BillNO,PordID ORDER BY other) as num    from #tb    )    select BillNO,ProdName,PordID,sum(P_num),            '第一次入库'=max(case when num=1 then other else NULL end),            '第二次入库'=max(case when num=2 then other else NULL end),            '第三次入库'=max(case when num=1 then other else NULL end)    from T    group by BillNO,ProdName,PordID/*BillNO     ProdName   PordID                 第一次入库                第二次入库                第三次入库---------- ---------- ---------- ----------- -------------------- -------------------- --------------------21210      螺丝         luosi10    152         2012-12-12 12:20     2012-12-18 14:20     2012-12-12 12:2021211      螺丝         luosi11    133         2012-11-12 11:20     2012-12-21 11:60     2012-11-12 11:20
[解决办法]
SQL2000的写法,
SQL code
create table tb(BillNO varchar(10), ProdName varchar(10), PordID varchar(10), P_num int, other varchar(20))Insert tb  Select '21210','螺丝','luosi10',60,'2012-12-12 12:20' union allSelect '21210','螺丝','luosi10',12,'2012-12-20 13:20' union allSelect '21211','螺丝','luosi11',50,'2012-11-12 11:20' union allSelect '21210','螺丝','luosi10',80,'2012-12-18 14:20' union allSelect '21211','螺丝','luosi11',23,'2012-12-21 11:60'union allSelect '21211','螺丝','luosi11',60,'2012-12-22 12:40'select * from tb/*BillNO     ProdName   PordID     P_num       other---------- ---------- ---------- ----------- --------------------21210      螺丝         luosi10    60          2012-12-12 12:2021210      螺丝         luosi10    12          2012-12-20 13:2021211      螺丝         luosi11    50          2012-11-12 11:2021210      螺丝         luosi10    80          2012-12-18 14:2021211      螺丝         luosi11    23          2012-12-21 11:6021211      螺丝         luosi11    60          2012-12-22 12:40(6 row(s) affected)*/select BillNO,other,identity(int,1,1) rn into #t from tbselect e.编码,e.名称,e.型号,e.数量,f.第一次入库,f.第二次入库,f.第三次入库from(select BillNO '编码',ProdName '名称',PordID '型号',sum(P_num) '数量'from tbgroup by BillNO,ProdName,PordID) einner join(select c.BillNO,max(case when rn=1 then c.other else '' end) '第一次入库',max(case when rn=2 then c.other else '' end) '第二次入库',max(case when rn=3 then c.other else '' end) '第三次入库'from(select a.BillNO,a.other,(select count(1) from #t b  where b.BillNO=a.BillNO and b.rn<=a.rn) rnfrom #t a) cgroup by c.BillNO) f on e.编码=f.BillNO/*编码         名称         型号         数量          第一次入库                第二次入库                第三次入库---------- ---------- ---------- ----------- -------------------- -------------------- --------------------21210      螺丝         luosi10    152         2012-12-12 12:20     2012-12-20 13:20     2012-12-18 14:2021211      螺丝         luosi11    133         2012-11-12 11:20     2012-12-21 11:60     2012-12-22 12:40(2 row(s) affected)*/ 

热点排行