create function F_test(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
select @s=isnull(@s+',','')+web2 from T where web1=@ID
return @s
end
go
select
distinct
web1
dbo.f_test(web1)as web2
from
t
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
select @s=isnull(@s+',','')+web2 from T where web1=@ID
return @s
end
go
select
distinct
web1
dbo.f_test(web1)as web2
from
t
解决方案 »
- mysql的安装程序有100多兆,discuz里的mysql才10几兆,差别咋这么大?
- 我已经设置了混合认证,为什么每次远程用查询分析器连接前,都要windows验证?
- 设置联合主键,插入数据时出现违反主键唯一性的错误?
- sql子查询返回多行
- 如何把从表的内容作为列显示在主表中
- MySQL触发器转SQL Server2005触发器怎么实现
- 新手问题
- sql 问题 高手指点一下
- 一个关于CONVERT的问题!
- Win2000的管理员密码更改以后,SQL Server7.0 就"登陆失败,无法启动服务"??请高手援助!谢谢.
- sql 2000 中有没有这样的语句可以同时删除多张表中的一个数据?
- 如何不使用游标将用代码表替换字符串中的编码,关键要执行效率高,来抢分啊
insert @t select
1, '12,23,35,36'
union all select
2, '25' declare @sql varchar(8000)
select @sql=isnull(@sql+' Union all ','')+' select '+rtrim(web1)+' as web1,'''+replace(web2,',',''' as web2 union all select '+rtrim(web1)+' as web1,''')+''''
from @t
exec(@sql)--结果
eb1 web2
----------- ----
1 12
1 23
1 35
1 36
2 25
用辅助表go
create table T(web1 int, web2 varchar(20))
insert T select
1, '12,23,35,36'
union all select
2, '25' goselect
top 100
id=identity(int,1,1)
into #
from
syscolumns a,syscolumns bgo
select
web1,web2=substring(web2,b.ID,charindex(',',web2+',',b.ID)-b.ID)
from
T, # b
where
charindex(',',','+web2,b.ID)=b.ID
/*
web1 web2
----------- --------------------
1 12
1 23
1 35
1 36
2 25(所影响的行数为 5 行)
*/DECLARE @t TABLE(id int, [values] varchar(100))INSERT @t SELECT 1, 'aa,bb'UNION ALL SELECT 2, 'aaa,bbb,ccc'
-- 查询处理SELECT A.id, B.valueFROM( SELECT id, [values] = CONVERT(xml, '<root><v>' + REPLACE([values], ',', '</v><v>') + '</v></root>') FROM @t)AOUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[values].nodes('/root/v') N(v))B
web1 web2 web3
1 12,23,35,36, 001
2 25 002要查到的结果
1 12 001
1 23 001
1 35 001
1 36 001
2 25 002
````````等
insert T select
1, '12,23,35,36'
union all select
2, '25' go select
top 100
id=identity(int,1,1)
into #
from
syscolumns a,syscolumns b go
select
web1,web2=substring(web2,b.ID,charindex(',',web2+',',b.ID)-b.ID)
from
T, # b
where
charindex(',',','+web2,b.ID)=b.ID
insert @t select
1, '12,23,35,36'
union all select
2, '25' declare @t1 table(id int,w1 int,w2 varchar(20))
insert @t1 select 0,web1,web2+',' from @t
while exists(select 1 from @t1 where id=0 and w2<>'')
begin
insert @t1 select 1,w1,left(w2,charindex(',',w2)-1)
from @t1 where charindex(',',w2)>0 and id = 0
update @t1 set w2=right(w2,len(w2)-charindex(',',w2))
end
select w1,w2 from @t1 where id = 1 order by w1
/*
w1 w2
----------- --------------------
1 12
1 23
1 35
1 36
2 25
*/
declare @t table (web1 int, web2 varchar(20))
insert @t select
1, '12,23,35,36' --数据量比较大啊```不单只是 '12,23,35,36' 后面还 3 32,37 等等
union all select
2, '25'
web1 web2 web3
1 12,23,35,36, 001
2 25 002
3 26,37 003
Create table T(web1 int,web2 varchar(20),web3 varchar(20))
insert into t(web1,web2,web3)
select 1,'12,23,35,36','001'
union all
select 2,'25','002'
union all
select 3,'26,27','003'--建立临时表#
select top 100 id=Identity(int,1,1) into #t from sysobjects a,syscolumns bselect a.web1,web2=substring(web2,b.id,charindex(',',web2+',',b.id)-b.id),a.web3 from T a,#t b
where substring(','+web2,b.id,1)=','/*
web1 web2 web3
----------- -------------------- --------------------
1 12 001
1 23 001
1 35 001
1 36 001
2 25 002
3 26 003
3 27 003(7 行受影响)
*/
drop table t
web1 web2 web3
1 12,23,35,36, 001
2 25 002
3 26,89 003
4 37 004
```等等```还5 6 一直下的要查到的结果
1 12 001
1 23 001
1 35 001
1 36 001
2 25 002
````````等数据量比较大的,大家明白了吗``/*
insert into t(web1,web2,web3)
select 1,'12,23,35,36','001'
union all
select 2,'25','002'
union all
select 3,'26,27','003'
*/不能这么定的,那例如我到了第1000行呢??
insert T select
1, '12,23,35,36'
--这里为什么要定上固定的值呢,那如果我这里列的值,那样怎么写呢??? 那insert T select 1 是什么意思呢??
union all select
2, '25'
insert T select
1, '12,23,35,36'
--这里为什么要定上固定的值呢,那如果我这里列的值,那样怎么写呢??? 那insert T select 1 是什么意思呢??
union all select
2, '25'