棘手问题,不知如何表达,请看例子,附代码.大力等高手帮我,谢谢.

[stwx]

--源表格
create table #tmp(sID int,iname varchar(10), times varchar(10), t tinyint)
insert into #tmp(sID,iname, times,t) values(1,'a','am/pm',2)
insert into #tmp(sID,iname, times,t) values(1,'b','am/pm',2)
insert into #tmp(sID,iname, times,t) values(1,'c','am/pm',2)
insert into #tmp(sID,iname, times,t) values(2,'d','am/pm',2)
insert into #tmp(sID,iname, times,t) values(3,'e','am/pm/n',3)
insert into #tmp(sID,iname, times,t) values(3,'f','am/pm/n',3)
insert into #tmp(sID,iname, times,t) values(4,'g','am/n',2)

select * from #tmp


--要select 成为 如下表格式:
create table #des(sID int, iname varchar(10),times varchar(10))
insert into #des(sID,iname, times) values(1,'a','am')
insert into #des(sID,iname, times) values(1,'b','')
insert into #des(sID,iname, times) values(1,'c','pm')
insert into #des(sID,iname, times) values(2,'d','am')
insert into #des(sID,iname, times) values(2,'e','pm')
insert into #des(sID,iname, times) values(3,'e','am')
insert into #des(sID,iname, times) values(3,'f','pm')
insert into #des(sID,iname, times) values(3,'','n')
insert into #des(sID,iname, times) values(4,'g','am')
insert into #des(sID,iname, times) values(4,'','n')

select * from #des
drop table #des
drop table #tmp

/*注:
由times 决定 得到 t, 需要由times 的'/' 数计算出来,原表是没有的.
*/

[gmlxf]

看来看去还是看不懂，楼主再解释一下吧/

[Jianli2004]

times最多3个吗？
如果：
a am/pm/n
b am/pm/n
c am/pm/n
d am/pm/n

结果是
a am
b
c pm
d n

吗？末尾优先？

[txlicenhe]

同感，不太明白。

[sunshareforever]

原来是个临时表!

暂时没看出别的

[wzh1215]

不是太明白，不过大致应该先合并：
sID iname times t
----------- ---------- ---------- ----
1 a/b/c am/pm 2
1 a/b/c am/pm 2
1 a/b/c am/pm 2
2 a/b/c am/pm 2
3 a/b/c am/pm/n 3
4 a/b/c am/n 2
3 a/b/c am/pm/n 3
--然后再处理！

[realgz]

俺也晕了３分钟，没懂

[shuixin13]

晕..

[zjcxc]

都不知道在说什么

[-狙击手-]

是不是想把times列根据/分开，形成多行记录？？？？？？？？

[zjcxc]

看结果也不像.

[goodsong]

俺也晕了３分钟，没懂

[Jianli2004]

填入每行?有规律吗？上面为何中间隔开？

1 a am
1 b pm
1 c
也可以吗？

自己说不清，别人怎能不懵

[stwx]

happyflystone(快乐的飞石) 说得对,
我是想把times列根据/分开，形成多行记录!

times 但最多5个!

例中,每个相同的 sid为一组(i行) , times 按照'/'分开,形成n行,
如果n>i,则在同一组插入 n-i ,iname为空,times 改为按'/' 分开后的每段.(am或pm)
(增加新一列存放拆分的times 值亦可).

如果n=i,则不插新行,times 改为按'/' 分开后的每段.(am或pm)

如果n<i,则不做任何处理!

[stwx]

是n<i是我错了.

应该是:
如果n<i 则将拆分后的times(am,pm) 填入每行,填完为止!

[Jianli2004]

如果n<i,则不做任何处理!

你举的例子就有问题

sID iname times
----------- ---------- ----------
1 a am
1 b
1 c pm

n=2,i=3 ,插的是头尾？

[Jianli2004]

select * into #x from #tmp

while exists (select 1 from #x where charindex('/',times)>1)
begin
insert into #x select Sid,iname,left(times,charindex('/',times)-1),t
from #x where charindex('/',times)>1
update #x set times=right(times,len(times)-charindex('/',times)) where charindex('/',times)>1
end

select identity(int,1,1) ID,* into #y from #x a order by sid,iname desc

update a set iname='' from #y a,#y b
where a.sid=b.sid and a.iname=b.iname and a.id>b.id

update a set times='' from #y a,#y b
where (a.sid=b.sid and a.times=b.times and a.iname>b.iname and a.iname<>'' and b.iname<>'')

select identity(int,1,1) id,sid,iname,times into #z from #y order by sid,iname desc

select sid,iname,times from #z a
where not exists (select iname from #z where sid=a.sid and times=a.times and id<a.id)
order by sid,iname

sid iname times
----------- ---------- ----------
1 a am
1 b pm
1 c
2 am
2 d pm
3 pm
3 e n
3 f am
4 n
4 g am

(10 row(s) affected)

[stwx]

to Jianli2004(健力)(MVP)
可以