2008/1/17---ID和名字对应问题2，求救!!!!

Question

给的表字段定义太长,数量和字段数量不一.原理是拆分后发送E-MAIL.2000需要用函数

Answer 1

create function dbo.getmail(@id varchar(5000))
returns varchar(5000)
as
begin
 declare @s varchar(5000)
 set @s = ''
 select @s = @s + umail+',' from tablea where indexchar(uid,@id) > 0 
 if @s <> '' set @s = left(@s,len(@s) -1)
 return @s
end
select id,dbo.getmail(mailtouid) mailtouid,
          dbo.getmail(mailtoccuid) mailtoccuid,
          dbo.getmail(mailtobcuid) mailtobcuid
from tableb

Answer 2

他们让写的是纯SQL代码。呜呜呜呜。
就是要实现邮件的CC,TO,BCC功能，并且数据保存到DB中。

Answer 3

create table tableA(uid varchar(50),uname varchar(50),umail varchar(100)) 
insert into tablea select 'A01','AAAAA','[email protected]' 
insert into tablea select 'A02','BBBBB','[email protected]' 
insert into tablea select 'A03','CCCCC','[email protected]' 
insert into tablea select 'A04','DDDDD','[email protected]' 
insert into tablea select 'A05','EEEEE','[email protected]' 
insert into tablea select 'A06','GGGGG','[email protected]' 
insert into tablea select 'A07','HHHHH','[email protected]' 
create table tableb(id int,mailtoUID varchar(50),mailCCUID varchar(50),mailBCCUID varchar(50)) 
insert into tableb select 1,'A01','A01,A02,A03,A04','A02,A07,A06'
insert into tableb select 2,'A02','A03,A04,A05','A03'
insert into tableb select 3,'A01','A03','A01'
insert into tableb select 4,'A01','','' 
insert into tableb select 5,'A02','A03','' 
go
SELECT TOP 8000 id = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b   select m.mailtoUID , n.umail from
(
  SELECT A.mailtoUID,mail = SUBSTRING(A.mailCCUID, B.ID, CHARINDEX(',', A.mailCCUID + ',', B.ID) - B.ID) FROM tableb A, # B WHERE SUBSTRING(',' + a.mailCCUID, B.id, 1) = ','   
  union all
  SELECT A.mailtoUID,uname = SUBSTRING(A.mailBCCUID, B.ID, CHARINDEX(',', A.mailBCCUID + ',', B.ID) - B.ID) FROM tableb A, # B WHERE SUBSTRING(',' + a.mailBCCUID, B.id, 1) = ','   
) m,tablea n
where m.mailtoUID = n.uid
order by m.mailtoUIDdrop table tablea,tableb,#/*
mailtoUID                                          umail                                                                                                
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]（所影响的行数为 17 行）
*/

Answer 4

create table tableA(uid varchar(50),uname varchar(50),umail varchar(100)) 
insert into tablea select 'A01','AAAAA','[email protected]' 
insert into tablea select 'A02','BBBBB','[email protected]' 
insert into tablea select 'A03','CCCCC','[email protected]' 
insert into tablea select 'A04','DDDDD','[email protected]' 
insert into tablea select 'A05','EEEEE','[email protected]' 
insert into tablea select 'A06','GGGGG','[email protected]' 
insert into tablea select 'A07','HHHHH','[email protected]' 
create table tableb(id int,mailtoUID varchar(50),mailCCUID varchar(50),mailBCCUID varchar(50)) 
insert into tableb select 1,'A01','A01,A02,A03,A04','A02,A07,A06'
insert into tableb select 2,'A02','A03,A04,A05','A03'
insert into tableb select 3,'A01','A03','A01'
insert into tableb select 4,'A01','','' 
insert into tableb select 5,'A02','A03','' 
go
SELECT TOP 8000 id = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b   select m.mailtoUID , n.umail from
(
  SELECT A.mailtoUID,mail = SUBSTRING(A.mailCCUID, B.ID, CHARINDEX(',', A.mailCCUID + ',', B.ID) - B.ID) FROM tableb A, # B WHERE SUBSTRING(',' + a.mailCCUID, B.id, 1) = ','   
  union all
  SELECT A.mailtoUID,uname = SUBSTRING(A.mailBCCUID, B.ID, CHARINDEX(',', A.mailBCCUID + ',', B.ID) - B.ID) FROM tableb A, # B WHERE SUBSTRING(',' + a.mailBCCUID, B.id, 1) = ','   
) m,tablea n
where m.mail = n.uid
order by m.mailtoUIDdrop table tablea,tableb,#/*
mailtoUID                                          umail                                                                                                
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A01                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]
A02                                                [email protected]（所影响的行数为 14 行）
*/

Answer 5

--sql server 2005可以使用如下的方法拆分.分拆列值原著：邹建
改编：爱新觉罗.毓华  2007-12-16  广东深圳有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc1. 旧的解决方法
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #2. 新的解决方法 create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 6

DECLARE @id INT,@SQL VARCHAR(8000)
SELECT @id=1SELECT @SQL=REPLACE((SELECT [mailToUID] FROM [tableB] WHERE [id]=@id),',',''',''')
EXEC('SELECT [umail]
FROM [tableA]
WHERE [uid] IN ('''+@SQL+''')')

Answer 7

DECLARE   @id   INT,@SQL   VARCHAR(8000)
SELECT   @id=1SELECT   @SQL=REPLACE((SELECT   [mailToUID]   FROM   [tableB]   WHERE   [id]=@id),',',''',''')
EXEC('SELECT   [umail]
FROM   [tableA]
WHERE   [uid]   IN   ('''+@SQL+''')') 

Answer 8

虽然你的办法把问题值分裂出来，但是这不是我想要得结果。
我想要的结果是以TableB作为中心表。结果如下：id  mailTo  mailCC, mailBCC   1,'[email protected]','[email protected],[email protected],[email protected],[email protected]','[email protected],[email protected],[email protected]'
  2,'[email protected]','[email protected],[email protected],[email protected]','[email protected]' 
  3,'[email protected]','[email protected]','[email protected]' 
  4,'[email protected]','','' 
  5,'[email protected]','[email protected]',''这样的话，我就可以利用mail功能直接发送邮件了。
不然，光分类是没意义的。不过还是学习了。

Answer 9

to JiangHongTao 
indexchar  是一个SQL函数？？？ 还是自定义函数？

Answer 10

分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华  2007-12-23  广东深圳)/*问题描述
tba
ID  classid   name
1     1,2,3   西服 
2     2,3     中山装
3     1,3     名裤
tbb 
id   classname
1     衣服
2     上衣
3     裤子我得的结果是
id   classname            name
1     衣服,上衣,裤子      西服 
2     上衣，裤子          中山装
3     衣服，裤子          名裤
*/-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go--第1种方法，创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
  return stuff(@str,1,1,'')
end
go 
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id          classid       name       
----------- ------------- ---------- 
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
（所影响的行数为 3 行）
*/--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID          classid              name       
----------- -------------------- ---------- 
1           衣服,上衣,裤子       西服
2           上衣,裤子            中山装
3           衣服,裤子            名裤
（所影响的行数为 3 行）
*/
drop table tba,tbb------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
goSELECT id , classname , name FROM
(
  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from 
  (
    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
  ) tbc , tbb where tbc.classid = tbb.id
  ) T
)A 
OUTER APPLY
(
  SELECT [classname]= STUFF(REPLACE(REPLACE((
    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from 
    (
      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
    ) tbc , tbb where tbc.classid = tbb.id
  ) N
  WHERE id = A.id and name = A.name
  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by iddrop table tba,tbb/*
id          classname      name
----------- -------------- ----------
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
(3 行受影响)
*/

Answer 11

我的天！！！
这么复杂，还用到XML了。牛X...