22,209
社区成员
发帖
与我相关
我的任务
分享
select result = convert(float ,'901206161310000040271400',0)
/*
result
-----------------------------------------------------
9.0120616131000006E+23
(所影响的行数为 1 行)
*/
select result = cast(9.0120616131000006E+23 as decimal(24,0))
/*
result
--------------------------
901206161310000060000000
(所影响的行数为 1 行)
*/
create table tb(encapsule int,type varchar(10),style varchar(10))
go
create proc insert_data
@encapsule int,
@type varchar(100),
@style varchar(100)
as
begin
insert tb
select @encapsule as [encapsule] ,
right(left(a,charindex(',',tb.a,aa.num)+1),1) as [type],
right(left(b,charindex(',',tc.b,aa.num)+1),1) as [style]
from
(select ','+@type as a)tb,
(select ','+@style as b)tc,
(select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 as num union select 8 union select 9 union select 10 union select 11 union select 12
) aa
where substring(tb.a,aa.num,1) = ',' and substring(tc.b,aa.num,1) = ','
end
go
declare @s varchar(200)
declare @c varchar(200)
declare @encapsule int
set @s = '1,2,3,4,5,6'
set @c = 'a,b,c,d,e,f'
set @encapsule = 1
exec insert_data @encapsule,@c,@s
select * from tb
/*
encapsule type style
----------- ---------- ----------
1 a 1
1 b 2
1 c 3
1 d 4
1 e 5
1 f 6
*/
drop proc insert_data
drop table tb
create table tb(id int identity(1,1), col varchar(64))
insert tb values('901206161310000040271400')
insert tb values(901206161310000040271400)
select * from tb
drop table tb
/*
id col
----------- ----------------------------------------------------------------
1 901206161310000040271400
2 901206161310000040271400
(2 row(s) affected)
*/
1.想利用asp在数据库中插入多条记录
asp提交的数据是如:
encapsule=1;
type=a,b,c,d,e,f;
style=1,2,3,4,5,6;
想利用存储过程插入记录:
encapsule type style
-------- ---- ------
1 a 1
1 b 2
1 c 3
----------------------------------------------------
表:
ID(自动增加的) 作者 机构 刊名
1 A,B,C D,E,F XX
2 H,J,F,I Z,Y,W,V YY
依次类推,作者跟机构可能是2个,或者是3个,没规律
我想要实现的功能是:
作者 机构 刊名
A D XX
B E XX
C F XX
H Z YY
J Y YY
F W YY
I V YY
数据量大概是20W 条左右,我该怎么实现呢?
谢谢您!
CREATE TABLE tb(ID int,作者 varchar(10),机构 varchar(20),刊名 varchar(10))
INSERT into tb values(1,'A,B,C', 'D,E,F' ,'XX')
INSERT into tb values(2,'H,J,F,I','ZZ,YY,WWW,VVVV','YY')
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b
select t1.作者,t2.机构,t1.刊名 from
(
select px=(select count(1) from
(
SELECT A.ID, b.id bid,作者 = SUBSTRING(A.作者, B.ID, CHARINDEX(',', A.作者 + ',', B.ID) - B.ID),A.刊名 FROM tb A, tmp B WHERE SUBSTRING(',' + A.作者, B.id, 1) = ','
) m where id=t.id and bid<t.bid)+1 , * from
(
SELECT A.ID, b.id bid,作者 = SUBSTRING(A.作者, B.ID, CHARINDEX(',', A.作者 + ',', B.ID) - B.ID),A.刊名 FROM tb A, tmp B WHERE SUBSTRING(',' + A.作者, B.id, 1) = ','
) t
) t1,
(
select px=(select count(1) from
(
SELECT A.ID, b.id bid,机构 = SUBSTRING(A.机构, B.ID, CHARINDEX(',', A.机构 + ',', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING(',' + A.机构, B.id, 1) = ','
) m where id=t.id and bid<t.bid)+1 , * from
(
SELECT A.ID, b.id bid,机构 = SUBSTRING(A.机构, B.ID, CHARINDEX(',', A.机构 + ',', B.ID) - B.ID) FROM tb A, tmp B WHERE SUBSTRING(',' + A.机构, B.id, 1) = ','
) t
) t2
where t1.id = t2.id and t1.px = t2.px
order by t1.id , t1.px
DROP TABLE tb,tmp
/*
作者 机构 刊名
---------- -------------------- ----------
A D XX
B E XX
C F XX
H ZZ YY
J YY YY
F WWW YY
I VVVV YY
(所影响的行数为 7 行)
*/