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 with a secondary processing data (here the 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 Table ADD CONSTRAINT PK_ID_ Sequence Table Primary Key (ID) GO - Update Processing 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.jsbh declare @ JSBH INT, @ RE VARCHAR 8000) Update #t set @ RE = Case @JSBH WHEN JSBH THEN @RE ',' KGLLM ELSE KGLLM END?, RE = @ RE, @ JSBH = JSBH Update a set kgllm = B.refrom jslmb A, (? Select JSBH, RE = Max (re) from #t group by jsbh) b Where a.jsbh =
B.JSBH DROP TABLE #T - Display Update Result Select * from JSLMBGO - Delete Test Data DROP TABLE MENUTREE, JSLMBDROP TABLE Sequence Table / * - Test Result JSBH ?????? JSMC ?????? ?????????? kgllm ?????????? --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ---------------- 1 ?????????? Query all data ?????????? '1', '3', '4'2 ?????????? Query the northwest area data ????????' 1 ',' 4 ',' 9 '(the number of rows affected is 2 lines) - * /
