ALTER TABLE 表A ADD 干部 int update 表a set 干部=。 from 表a,对照表b where 。。不知道你是具体怎么对照的
--> 测试数据:[TBA] if object_id('[TBA]') is not null drop table [TBA] create table [TBA]([ID] int,[Name] varchar(9)) insert [TBA] select 1,'张三,李四' union all select 2,'王五' union all select 3,'马六,朱七'select * from [TBA]select a.[ID],b.[Name] from (select [ID],[Name]=convert(xml,'<root><v>'+replace([Name],',','</v><v>')+'</v></root>') from [TBA])a outer apply (select [Name]=C.v.value('.','nvarchar(100)') from a.[Name].nodes('/root/v')C(v))b
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A GOCREATE TABLE A ( ID INT, NAME VARCHAR(50) ) GOIF OBJECT_ID('B') IS NOT NULL DROP TABLE B GOCREATE TABLE B ( NAME VARCHAR(20) ) GOINSERT INTO A SELECT 1,'张三,李四' UNION ALL SELECT 2,'王五' UNION ALL SELECT 3,'马六,朱七' GOINSERT INTO B SELECT '张三' UNION SELECT '朱七' GOSELECT ID,C.NAME,GADRES=(CASE WHEN NAME1=B.NAME AND NAME1 IS NOT NULL THEN '1' WHEN B.NAME IS NULL THEN '' ELSE '0' END) +(CASE WHEN B.NAME IS NULL THEN '' ELSE ',' END) +(CASE WHEN NAME2=B.NAME THEN '1' ELSE '0' END) FROM ( SELECT ID,NAME,NAME1=SUBSTRING(NAME,0,CHARINDEX(',',NAME)) ,NAME2=SUBSTRING(NAME,CHARINDEX(',',NAME)+1,LEN(NAME)-CHARINDEX(',',NAME)+1) FROM A ) C LEFT JOIN B ON C.NAME1=B.NAME OR C.NAME2=B.NAME GO
update 表a set 干部=。 from 表a,对照表b where 。。不知道你是具体怎么对照的
if object_id('[TBA]') is not null drop table [TBA]
create table [TBA]([ID] int,[Name] varchar(9))
insert [TBA]
select 1,'张三,李四' union all
select 2,'王五' union all
select 3,'马六,朱七'select * from [TBA]select
a.[ID],b.[Name]
from
(select [ID],[Name]=convert(xml,'<root><v>'+replace([Name],',','</v><v>')+'</v></root>') from [TBA])a
outer apply
(select [Name]=C.v.value('.','nvarchar(100)') from a.[Name].nodes('/root/v')C(v))b
/*ID Name
----------- ----------------------------------------------------------------------------------------------------
1 张三
1 李四
2 王五
3 马六
3 朱七(5 行受影响)
*/用这个再连接你的干部列表。就可以得到1,0 了。不过好像意义不是很大。本来干部表就已经列了名称和属性了。
IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GOCREATE TABLE A
(
ID INT,
NAME VARCHAR(50)
)
GOIF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GOCREATE TABLE B
(
NAME VARCHAR(20)
)
GOINSERT INTO A
SELECT 1,'张三,李四'
UNION ALL
SELECT 2,'王五'
UNION ALL
SELECT 3,'马六,朱七'
GOINSERT INTO B
SELECT '张三'
UNION
SELECT '朱七'
GOSELECT ID,C.NAME,GADRES=(CASE WHEN NAME1=B.NAME AND NAME1 IS NOT NULL THEN '1'
WHEN B.NAME IS NULL THEN '' ELSE '0' END)
+(CASE WHEN B.NAME IS NULL THEN '' ELSE ',' END)
+(CASE WHEN NAME2=B.NAME THEN '1' ELSE '0' END)
FROM
(
SELECT ID,NAME,NAME1=SUBSTRING(NAME,0,CHARINDEX(',',NAME))
,NAME2=SUBSTRING(NAME,CHARINDEX(',',NAME)+1,LEN(NAME)-CHARINDEX(',',NAME)+1)
FROM A
) C
LEFT JOIN B
ON C.NAME1=B.NAME OR C.NAME2=B.NAME
GO