有数据表 T1,其中有字段 c1 和 u1假设表中原来有数据为 c1 u1
1 2
1 3
2 2
1 5第一个问题:检索,如果检索c1 = 1 的那么得到c1 u1
1 2
1 3
1 5但希望返回的结果不是这种结果集,而是由 u1 的值组成的中间用逗号分割的字符串,结果返回为:“2,3,5”第二个问题是:插入新数据集通过传入 u1 对应的字符串和某个 c1 的值同时插入多条,且能够避免重复比如传入参数 c1 = 1 ,u1对应的字符串为 “2,3,7,9”那么时间插入数据位两条:
c1 u1
1 7
1 9因为数据
c1 u1
1 2
1 3表中原来已经存在,不能重复添加希望用动态sql 语句,不用存储过程和方法谢谢
本帖将和
http://topic.csdn.net/u/20111228/21/f518561d-26ac-46e3-9a8a-df35488e9985.html?seed=85076579&r=77110821#r_77110821合并结贴
1 2
1 3
2 2
1 5第一个问题:检索,如果检索c1 = 1 的那么得到c1 u1
1 2
1 3
1 5但希望返回的结果不是这种结果集,而是由 u1 的值组成的中间用逗号分割的字符串,结果返回为:“2,3,5”第二个问题是:插入新数据集通过传入 u1 对应的字符串和某个 c1 的值同时插入多条,且能够避免重复比如传入参数 c1 = 1 ,u1对应的字符串为 “2,3,7,9”那么时间插入数据位两条:
c1 u1
1 7
1 9因为数据
c1 u1
1 2
1 3表中原来已经存在,不能重复添加希望用动态sql 语句,不用存储过程和方法谢谢
本帖将和
http://topic.csdn.net/u/20111228/21/f518561d-26ac-46e3-9a8a-df35488e9985.html?seed=85076579&r=77110821#r_77110821合并结贴
declare @t table (c1 int,u1 int)
insert into @t
select 1,2 union all
select 1,3 union all
select 2,2 union all
select 1,5declare @param int set @param=1declare @sql varchar(20) set @sql=''
select @sql=@sql+ltrim(u1)+',' from @t where c1=@param
select left(@sql,len(@sql)-1)
/*
2,3,5
*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
enddeclare @t table (c1 int,u1 int)
insert into @t
select 1,2 union all
select 1,3 union all
select 2,2 union all
select 1,5declare @c1 int set @c1= 1
declare @u1 varchar(30) set @u1='2,3,7,9'insert into @t
select a.* from (select @c1 as c1,* from dbo.m_split(@u1,',')) a left join @t t on
a.c1=t.c1 and a.col=t.u1 where t.u1 is nullselect * from @t
/*
c1 u1
----------- -----------
1 2
1 3
2 2
1 5
1 7
1 9
*/
create table T1
(
c1 int not null,
u1 int null
)insert t1(c1,u1)
select 1,2
union all
select 1,3
union all
select 2,2
union all
select 1,5select c1,stuff((select ','+rtrim(u1) from t1 where c1=1 for xml path('')),1,1,'') from t1 where c1=1 group by c1
结果
==========
c1 x
1 2,3,5
create table t1(c1 int, u1 int)insert into t1
select 1, 2 union all
select 1, 3 union all
select 2, 2 union all
select 1, 5
-- 第一个问题:检索
select t.c1,
left(
cast((select cast(u1 as varchar(1))+',' from t1 where c1=t.c1 for xml path('')) as varchar(10)),
len(cast((select cast(u1 as varchar(1))+',' from t1 where c1=t.c1 for xml path('')) as varchar(10)))-1) u1
from t1 t
where t.c1=1
group by t.c1c1 u1
----------- ----------
1 2,3,5(1 row(s) affected)
-- 第二个问题是:插入新数据集
declare @c int,@u varchar(10)-- 传入的参数
select @c=1,@u='2,3,7,9'insert into t1
select t2.*
from
(select a.c0,
substring(a.u0,b.number,charindex(',',a.u0+',',b.number)-b.number) u0
from (select @c c0,@u u0) a
inner join master.dbo.spt_values b
on b.[type]='p'
and substring(','+a.u0,b.number,1) = ',') t2
left join t1 on t2.c0=t1.c1 and t2.u0=t1.u1
where t1.u1 is null select * from t1 where c1=1c1 u1
----------- -----------
1 2
1 3
1 5
1 7
1 9(5 row(s) affected)