NO下面是产品的部分编号,只有加上任意一个type才是产品编号 type下面是数量
如下部分数据应该是10 * 5 就50个产品
F01001type1,F01001type2…………
………………
…………………………F02008type5应该在数据库里面怎么建表存储产品,另外从数据库取出产品后怎么成开始的Excel的样子!??以下是部分excel的数据
NO type1 type2 type3 type4 type5
F01001 40 90 90 60 20
F01002 150 270 280 200 100
F02001 50 100 110 90 50
F02002 50 100 110 90 50
F02003 35 70 90 70 35
F02004 50 100 110 90 50
F02005 100 130 150 130 90
F02006 50 100 110 90 50
F02007 100 200 220 180 100
F02008 70 140 180 140 70
如下部分数据应该是10 * 5 就50个产品
F01001type1,F01001type2…………
………………
…………………………F02008type5应该在数据库里面怎么建表存储产品,另外从数据库取出产品后怎么成开始的Excel的样子!??以下是部分excel的数据
NO type1 type2 type3 type4 type5
F01001 40 90 90 60 20
F01002 150 270 280 200 100
F02001 50 100 110 90 50
F02002 50 100 110 90 50
F02003 35 70 90 70 35
F02004 50 100 110 90 50
F02005 100 130 150 130 90
F02006 50 100 110 90 50
F02007 100 200 220 180 100
F02008 70 140 180 140 70
解决方案 »
- 性别和年龄字段一般怎么处理?
- sql server 2005,只能用sql native client连接,而不能用ole db provider for sql server连接
- 请教一个问题:如果数据量上百万,但是我每次读取的就10条,会不会影响速度?
- 求一条sql语句
- xp_cmdshell的使用问题。
- 数据迁移
- 安装时候的两个小问题!
- 2. The Selected file is not a Microsoft Management Console document,是什么意思?
- 索引到底有多大作用?
- 再次请教关于一个Select into in 的问题,在线等!谢谢
- 直接在表中复制一行,然后在新行那里改下数据,结果怎么改都好,都提示键入列信息不足或不正确,更新影响到多行
- 有什么好SQL代码生成工具吗?
以下是部分excel的数据
NO type1 type2 type3 type4 type5
F01001 40 90 90 60 20
F01002 150 270 280 200 100
F02001 50 100 110 90 50
F02002 50 100 110 90 50
F02003 35 70 90 70 35
F02004 50 100 110 90 50
F02005 100 130 150 130 90
F02006 50 100 110 90 50
F02007 100 200 220 180 100
F02008 70 140 180 140 70
NO nvarchar(10),
type1 int,
type2 int,
type3 int,
type4 int,
type5 int)sql="select '<tr><td>'+no+'</td><td>+cast(type1 as nvarchar(10))+</td><td>cast(type2 as nvarchar(10))+</td><td>cast(type3 as nvarchar(10))+</td><td>cast(type4 as nvarchar(10))+</td><td>cast(type5 as nvarchar(10))+</td>"
rs.open sql,conn
set str=rs.getrows
rs.close
'dim fso
'dim filename
'createfile filename
file.write "<table>"+str+"</table>"
'close fso
create table product(bh varchar(6),type int,productid as bh+cast(type as varchar) )
insert into product(bh,type)values('F01001',40)
insert into product(bh,type)values('F01001',90)insert into product(bh,type)values('F01003',150)
insert into product(bh,type)values('F01003',270)
insert into product(bh,type)values('F01003',280)
insert into product(bh,type)values('F01004',200)
insert into product(bh,type)values('F01004',100)insert into product(bh,type)values('F01001',90)
insert into product(bh,type)values('F01002',80)
insert into product(bh,type)values('F01002',50)
insert into product(bh,type)values('F01001',60)
insert into product(bh,type)values('F01001',20)
insert into product(bh,type)values('F01002',150)insert into product(bh,type)values('F01002',270)
insert into product(bh,type)values('F01002',280)
insert into product(bh,type)values('F01002',200)
insert into product(bh,type)values('F01002',100)
--select * from product order by bh,typedeclare @bh varchar(6),@type int,@oldbh varchar(6)
declare @i int,@maxid int,@sql varchar(4000)
create table tb(id int identity,bh varchar(6),type1 int,type2 int,type3 int,type4 int,type5 int)
declare cur cursor for select bh,type from product order by bh,type
open cur
set @i=0
fetch next from cur into @bh,@type
while @@fetch_status=0
begin
set @i=@i+1
if @i%5=1 or @bh<>@oldbh begin select @i=1,@oldbh=@bh insert into tb(bh,type1) values(@bh,@type) end
else
begin
select @maxid=max(id) from tb
set @sql='update tb set type'+cast(@i as varchar)+'='+cast(@type as varchar)+' where id='+cast(@maxid as varchar)
exec(@sql)
end
fetch next from cur into @bh,@type
end
deallocate cur
select * from tb
drop table tb
drop table product
我要的最终产品结果就是
Product qty
F01001type1 40
F01001type2 90
F01001type3 90
F01001type4 60
F01001type5 20
………………
F02008type1 70
F02008type2 140
F02008type3 180
F02008type4 140
F02008type5 70
NO type1 type2 type3 type4 type5
F01001 40 90 90 60 20
F01002 150 270 280 200 100
F02001 50 100 110 90 50
F02002 50 100 110 90 50
F02003 35 70 90 70 35
F02004 50 100 110 90 50
F02005 100 130 150 130 90
F02006 50 100 110 90 50
F02007 100 200 220 180 100
F02008 70 140 180 140 70我想要的最终产品结果就是
Product qty
F01001type1 40
F01001type2 90
F01001type3 90
F01001type4 60
F01001type5 20
………………
F02008type1 70
F02008type2 140
F02008type3 180
F02008type4 140
F02008type5 70
declare @tb table(no varchar(6),type1 int,type2 int,type3 int,type4 int,type5 int)
insert into @tb
select 'F01001',40 , 90 , 90 , 60,20 union all
select 'F01002' , 150 , 270 , 280 , 200 , 100 union all
select 'F02001', 50 , 100 , 110 , 90 , 50 union all
select 'F02002', 50 , 100 , 110 , 90 , 50
select product,qty from (
select product=[no]+'type1',qty=type1 from @tb
union all
select [no]+'type2',qty=type2 from @tb
union all
select [no]+'type3',qty=type3 from @tb
union all
select [no]+'type4',qty=type4 from @tb
union all
select [no]+'type5',qty=type5 from @tb
)a order by product
(no varchar(20) , type1 int ,
type2 int,type3 int ,type4 int , type5 int
)
insert into #
select 'F01001', 40 , 90 , 90 , 60 , 20 union
select 'F01002', 150, 270, 280, 200, 100 union
select 'F02001', 50 , 100, 110, 90 , 50 union
select 'F02002', 50 , 100, 110, 90 , 50 union
select 'F02003', 35 , 70 , 90 , 70 , 35 union
select 'F02004', 50 , 100, 110, 90 , 50 union
select 'F02005', 100, 130, 150, 130, 90 union
select 'F02006', 50 , 100 , 110 , 90 , 50 union
select 'F02007', 100, 200, 220, 180, 100 union
select 'F02008', 70 , 140, 180, 140, 70select * into t_temp from #select a.no + b.name as Product ,
case when b.name = 'type1' then type1 when b.name = 'type2' then type2
when b.name = 'type3' then type3 when b.name = 'type4' then type4
when b.name = 'type5' then type5 end as qty
from t_temp a,syscolumns b where b.name <> 'no' and b.id = object_id('t_temp')
order by substring(a.no + b.name , 1 , 6) asc , substring(a.no + b.name , 7 , 5) ascdrop table #
drop table t_temp
when b.name = 'type3' then type3 when b.name = 'type4' then type4
when b.name = 'type5' then type5 end as qty这一段可以这样实现
delcare @s varhcar(8000)
set @s = ''
slect @s = @s + ' when b.name = ''' +name + ''' then ' + name from syscolumns where name <> 'no' and id = object_id('t_temp')select @S = ' case ' + @s + ' end as qty '==
然后 继续