Original post:
http://community.9cbs.net/expert/topic/3244/3244874.xml?temp =.5755274 Table 1: MenuTree? ID ??????????????????? ?????????????????? -----------? --------------------- ------ 1 ????????????? Mall monitoring system? 3 ????????????????????????????? ???? comprehensive report analysis? 4 ????????????????????? Sales month report? 6 ???????????????? ?????? ◇ Sales data collection? 7 ?????????????????????? ◇ monitored shopping mall management? 9 ??????????? ?????????? ◇ Monitor product management ?? Table 2:? JSLMB?? Jsbh ????? jsmc ?????????????????? KGLLM? ??????? --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - 1 ??????? Query all data ????????? '1', '2', '3', '4', '5' 2 ??????? query Northwestern data ??????? '1', '2', '4', '5', '9' ??
How to remove the table number in the table in the KGLLM (Managed Column) field in the delete table two? For example: the IDZ field of the table: "2" and "5" values do not exist.
I want to remove the "2" and "5" value in the KGLLM field of the table I, other values, such as "1", "3" to keep
-------------------------------------------------- ------------------------------------
- Test data create Table Menutree (id int, context varchar (20)) Insert Menutree SELECT 1, 'Shopping Mall Monitoring System' Union? All ????? SELECT 3, 'Integrated Report Analysis' Union? All ???? ? SELECT 4, 'Sales Miller "Union? all ????? SELECT 6,' ◇ Sales Data Collection 'union? all ????? SELECT 7,' ◇ Monitoring Mall Management 'Union? all ???? SELECT 9, '◇ Monitor Products Management' Create Table JSLMB (JSBH INT, JSMC VARCHAR (20), KGLLM VARCHAR (8000)) Insert JSLMB SELECT 1, 'Query all data'?, '' '1', '' ' 2 '', '' 3 '', '' '4', '' '' '' union? All ?? SELECT 2, 'Query the northwest area data', '' '1' ',' '2' ', '' 4 '', '' '5', '' 9 '' Go - Create a table of assistance processing data (here 8000 is determined according to your KGLLM size, if your KGLLM length is 1000, change For 1000) SELECT TOP 8000 ID = Identity Table from Syscolumns A, Syscolumns Balter Table Sequence Table Add Constraint PK_ID_ Sequence Table Primary Key (ID) GO - Update SELECT A.JSBH, KGLLM = Substring (A.Kgllm, B.ID, Charindex (',', A.kgllm ', B.ID) -b.id)?, re = cast (' 'as varchar (8000)) INTO #TFROM JSLMB A, Sequence Table B, Menutree Cwhere B.ID <= LEN (A.KGLLM)? And Substring (',' a.kgllm, B.ID, 1) = ','? and stuff (Substring (A. KGLLM, B.ID, Charindex (',', a.kgllm ',', b.id) -b.id-1), 1, 1, '') ?? = C.Idorder by a.jsbhdeclare @JSBH Int, @ RE varchar (8000 Update #t set @ RE = Case @JSBH WHEN JSBH THEN @RE ',' KGLLM ELSE KGLLM END?, RE = @ RE, @ JSBH = JSBHUPDATE A SET KGLLM = B.REFROM JSLMB A, (? Select JSBH, RE = max (re) from # # by jsbh) b Where a.jsbh =
