实际应用问题

[jianshao810]

表结构
Id userIds scanTime

userIds 存储格式是这样的。
只有一个用户的情况 userid1
如果有多个用户的情况 userid1,userid2,userid3,userid4

如果 我想 将多个用户的情况 根据 相同的 内容 插入一条新的数据，这个如何做？

例子如下啊：

Id userIds scanTime
1 jsh 2000-1-1
2 jsh,jsh4 2000-1-1

变成
Id userIds scanTime
1 jsh 2000-1-1
2 jsh 2000-1-1
2 jsh4 2000-1-1

我这个需要在查询的时候用，不是处理数据。
我要用 userIds 去关联 一个部门表。确实想不出办法，请教大家。
谢谢

[yyoinge]

你又没有一个专门存储userid的表呢？有的话就好办了

select b.id, a.userid, b.scanTime
from t_user a, t_上面那个表 b
where charindex(',' + a.userid + ',', ',' + b.userids + ',') > 0

没有的话，自己搜索一下，CSDN上好多这种问题的

[qgqch2008]

CREATE TABLE TB(Id INT, userIds VARCHAR(50), scanTime VARCHAR(20))
INSERT dbo.TB
SELECT 1, 'jsh', '2000-1-1' UNION ALL
SELECT 2, 'jsh,jsh4', '2000-1-1'

SELECT  a.Id ,
        SUBSTRING(a.userIds + ',', b.number,
                  CHARINDEX(',', a.userIds + ',', b.number) - b.number) AS userIds ,
        a.scanTime
FROM    dbo.TB a ,
        master.dbo.spt_values b
WHERE   b.type = 'p'
        AND SUBSTRING(',' + a.userIds, b.number, 1) = ','
        
DROP TABLE dbo.TB
/*
Id          userIds                                             scanTime
----------- --------------------------------------------------- --------------------
1           jsh                                                 2000-1-1
2           jsh                                                 2000-1-1
2           jsh4                                                2000-1-1

(3 行受影响)

*/

[yaxiaochong]

表示不懂

[yyoinge]

http://blog.csdn.net/wufeng4552/article/details/4534365

[zs621]

create table test070301
(
Id int ,userIds nvarchar(200), scanTime datetime
)
insert into test070301
select 
1 ,'jsh', '2000-1-1'
union select 
2 ,'jsh,jsh4' ,'2000-1-1'

select a.id,SUBSTRING(a.userIds+',',number+1,CHARINDEX(',',a.userIds)-1) userIds ,
CONVERT(varchar,a.scanTime,23) scanTime from test070301 a
inner join master..spt_values b
on b.type='p' 
and LEN(SUBSTRING(a.userIds,number+1,CHARINDEX(',',a.userIds)))>0
and CHARINDEX(',',SUBSTRING(a.userIds+',',number+1,CHARINDEX(',',a.userIds)-1))=0
and SUBSTRING(a.userIds,number+1,1)<>'' and SUBSTRING(a.userIds,number+1,1)<>','
id          userIds                                                                                                                                                                                                   scanTime
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
2           jsh                                                                                                                                                                                                       2000-01-01
2           jsh                                                                                                                                                                                                       2000-01-01
2           sh4                                                                                                                                                                                                       2000-01-01

(3 行受影响)

[cd731107]

[Quote=引用 2 楼 qgqch2008 的回复:]
SQL code
CREATE TABLE TB(Id INT, userIds VARCHAR(50), scanTime VARCHAR(20))
INSERT dbo.TB
SELECT 1, 'jsh', '2000-1-1' UNION ALL
SELECT 2, 'jsh,jsh4', '2000-1-1'

SELECT a.Id ,
SUBSTRIN……
[/Quote]
标准答案

[liang145]

create table tb
(Id int,userIds nvarchar(400), scanTime datetime)
insert tb
select 1 ,'jsh','2000-1-1' union all
select 2 ,'jsh,jsh4','2000-1-1' 

select tb.id,
case when number is null then userIds else substring(','+userIds+',',number+1,charindex(',',substring(','+userIds+',',number+1,len(userIds)))-1) end as userIds, scanTime from tb
left join master.dbo.spt_values on type='p' and charindex(',',userIds)>0 and number between 1 and len(','+userIds+',')
and substring(','+userIds,number,1)=','