如果把下面的颜色取出来插入表tb,字段也要新增加的?
declare @color varchar(1000)
set @color='#a66edd,#f984a1,#cccc00,#999999,#0099cc,#ff0000,#006f00,#0099ff,#f6bd0f,#afd8f8'
declare @color varchar(1000)
set @color='#a66edd,#f984a1,#cccc00,#999999,#0099cc,#ff0000,#006f00,#0099ff,#f6bd0f,#afd8f8'
解决方案 »
- 一个让我困惑的sql查询,求高手
- 子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种
- 求问 存储过程传参问题
- 如何将记录查询成字段
- windows server 2003+SQL SERVER 2005,性能监视中缺少SQL相关的性能对象
- 如何通过循环倒数据
- 簡單的視圖連接問題 !
- 求助:关于SQL SERVER 2000 中特殊字段存储的问题
- 我的问题难就难在需要有专业有经验(在线打滚哭求高手指教,等于拯救小弟!!!)
- 请教高手,多表关联查询问题!!!
- Isnull 怎么不起作用呢?应该怎么改 高手看看(在线马上结贴)
- !!reporting service 怎么实现用+号点击显示、隐藏该行所属信息????
declare @tb table(col1 nvarchar(10),col2 nvarchar(10),col3 nvarchar(10),col4 nvarchar(10),col5 nvarchar(10),col6 nvarchar(10),col7 nvarchar(10),col8 nvarchar(10),col9 nvarchar(10),col10 nvarchar(10))
set @color='#a66edd,#f984a1,#cccc00,#999999,#0099cc,#ff0000,#006f00,#0099ff,#f6bd0f,#afd8f8'
exec ( 'insert into @tb values('+replace(''''+@color+'''',',',''',''')+')' )
[color] nvarchar(200) NULL
然后写入就行了.
-----------------
select *,FundVal/Total as per from
(
select a.FundVal,b.JCode,c.Title from BM_FCombWinTbl a inner join BM_FCombDetailTbl b
on a.DetailId=b.DetailId inner join BM_Fund c on c.JCode=b.JCode where b.CombId=1
) t1,
(
select sum(a.FundVal) as Total from BM_FCombWinTbl a inner join BM_FCombDetailTbl b
on a.DetailId=b.DetailId where b.CombId=1
)
t2
1.在你需要写入的表里面先添加存储栏位的字段,可以使用图形化界面完成或者SQL语句完成.
ALTER TABLE dbo.A ADD [color] nvarchar(200) NULL
2.通过你写的语句,写入数据,
INSERT INTO A
select *,FundVal/Total as per from
(
select a.FundVal,b.JCode,c.Title from BM_FCombWinTbl a inner join BM_FCombDetailTbl b
on a.DetailId=b.DetailId inner join BM_Fund c on c.JCode=b.JCode where b.CombId=1
) t1,
(
select sum(a.FundVal) as Total from BM_FCombWinTbl a inner join BM_FCombDetailTbl b
on a.DetailId=b.DetailId where b.CombId=1
)
t2
go
create table tb(col varchar(1000))
declare @i int , @color varchar(2000) , @sql varchar(8000)
set @color='#a66edd,#f984a1,#cccc00,#999999,#0099cc,#ff0000,#006f00,#0099ff,#f6bd0f,#afd8f8'
set @i=1
insert into tb values(@color)while charindex(',',@color)>0
begin
set @sql='alter table tb add col'+cast(@i as varchar)+' varchar(20)'
print @sql
exec(@sql)
set @sql='update tb set col'+cast(@i as varchar)+'=left('''+@color+''',charindex('','','''+@color+''')-1)'
print @sql
exec (@sql)
set @color=stuff(@color,1,charindex(',',@color+','),'')
set @i=@i+1
end
set @sql='alter table tb add col'+cast(@i+1 as varchar)+' varchar(20)'
print @sql
exec(@sql)
set @sql='update tb set col'+cast(@i+1 as varchar)+'='''+@color+''''
print @sql
exec (@sql)
select * from tb结果就不贴另外,太长了。楼主可以自己试试。
declare @color varchar(1000)
set @color='#a66edd,#f984a1,#cccc00,#999999,#0099cc,#ff0000,#006f00,#0099ff,#f6bd0f,#afd8f8'set @color=@color+','while(charindex(',',@color)>0)
begin
insert into @t select substring(@color,1,charindex(',',@color)-1)
set @color=stuff(@color,1,charindex(',',@color),'')
endselect * from @t