ddmiss28442
04-24-2011, 01:41 PM
Table 1 a total of two columns, rows variable:
A B
C A
B C D
D Table 2 A absolute of two columns,You can not love life forever (http://allthings-fresh.net/##############/displayimage.php?pos=-348), rows precarious
01 A
02 A, B, D
03 B, C
04 C, D
If you produce table 3 (N = null)
01 A A N N N N
02 A A B D B B C D
03 B C N N
04 C C D D N N
Table 1 and Table 2, detect out how the data namely equal to the pertinent contact Table 3, Table 2, the second column is the character series.
best no apt use loops and cursors, the best SQL tin be secondhand to directly solve.
create table tb1 (c1 varchar (10), c2 varchar (10))
insert into tb1 values ('A', 'A')
insert into tb1 values ('B', 'B')
insert into tb1 values ('C', 'c')
insert into tb1 values ('D', 'd')
create table tb2 (c1 varchar (10), c2 varchar (10))
insert into tb2 values ('01 ',' A ')
insert into tb2 values ('02',如果你能达到18秒以上 (http://tjzx.hostpo.net/blog/article.php?type=blog&cid=1&itemid=271033), 'A, B, D')
insert into tb2 values (' 03 ',' B, C ')
insert into tb2 values ('04', 'C, D')
go
select m.c1,
; m.c21,
isnull ((select c2 + '' from tb1 n where n.c1 = m.c21),nike air jordans (http://www.nikeniceto.com/), 'N' ) +
isnull (m.c22 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c22), 'N') +
isnull (m.c23 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c23), 'N') +
isnull (m.c24 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c24), 'N') c3
from
(
select c1,
; parsename (replace (reverse (c2), ',','.'), 1) c21,
parsename (replace (reverse (c2), ' , ','.'), 2) c22,
parsename (replace (reverse (c2),', ','.'), 3) c23,
parsename (replace (reverse (c2), ',','.'), 4) c24
from tb2
) m
drop table tb1, tb2
from alike problems -
/ * specification of the problem
tba
ID classid name
1 ; 1,2,3 suit
2 2,3 tunic
3 1 , 3 pants
tbb
id classname
1 dress shirt
2 I have 3 pants
result is
id classname ; name
1 clothes, jacket, pants suit
2 jacket, pants ; tunic
3 clothes, pants Name pants
* /
------------------------------------- ----------------
- sql server 2000 in a written
create table tba (ID int, classid varchar (20), name varchar (10))
insert into tba values (1, '1, 2,3 ',' suits ')
insert into tba values (2, '2, 3',mbt shoes las vegas (http://www.mbts-clearance.com/), 'tunic')
insert into tba values (3, '1, 3 ',' label of pants ')
create table tbb (ID varchar (10), classname varchar (10))
insert into tbb values ('1' , 'clothes')
insert into tbb values ('2 ',travel straighteners (http://www.ghdivsale.net/specials.html),' Top ')
insert into tbb values ('3', 'trousers')
go
- the first 1 kind of method, create a feature to display
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 (','+ hurl (id for varchar) +',',','+id +',')> 0
return matter (@ str, 1,1,'')
end
go
select id,shoes jordans (http://cgwave.dothome.co.kr/renew/smf/index.php?topic=954322.msg704713#msg704713), classid = dbo.f_hb (classid), name from tba
drop function f_hb
/ *
id classid name
----------- ------------- - ---------
1 clothes, jacket, pants suits
2 ; shirt, jeans tunic
3 dress, jeans were pants
(number of rows affected by line 3)
* /
- 2 ways. update
when (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 ; clothes, jacket, pants suits
2 T-shirt, pants tunic
3 ; clothes, trousers were pants
(number of rows affected by line 3)
* /
drop table tba, tbb
-------------------------------- ----------------------------------------
- sql server 2005 in the first decomposition tba the classid, and then merge classname
create table tba (ID integer, classid varchar (20), name varchar (10))
insert into tba values (1, '1, 2,3 ', 'suits')
insert into tba values (2, '2,mbt shoes chapa gtx (http://www.mbtoutletstores.com/), 3', 'tunic')
insert into tba values (3, '1, 3 ',' name pants')
establish chart tbb (ID varchar (10), classname varchar (10))
insert into tbb values ('1 ',' clothes')
insert into tbb values ('2 ', 'Top')
insert into tbb values ('3 ',' trousers')
go
SELECT 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,air max turbulence (http://www.nikeniceto.com/), [classid] = CONVERT (xml, ' ' + REPLACE ([classid], ',', ' ') + ' ') FROM tba) A
OUTER APPLY (SELECT classid = Nvvalue ('.',' varchar (100) ') FROM A. [ ,],[A
OUTER APPLY
(
SELECT [classname] = STUFF (REPLACE (REPLACE ((
SELECT classname FROM (choose tbc.id, tbc . name, tbb.classname from
(
SELECT A.id, A.name, B.classid FROM (SELECT id , name, [classid] = CONVERT (xml, ' ' + REPLACE ([classid], ',', ' ') + ' ; ') FROM tba) A
OUTER APPLY (SELECT classid = Nvvalue ('.',' 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), ' ',''), 1, 1,'')
) N
order by id
drop table tba, tbb
/ *
id ; classname name
----------- ---- ---------- ----------
1 clothes, jacket, pants suits
2 T-shirt, pants tunic
3 ; clothes, trousers were pants
(3 rows affected)
* /
A B
C A
B C D
D Table 2 A absolute of two columns,You can not love life forever (http://allthings-fresh.net/##############/displayimage.php?pos=-348), rows precarious
01 A
02 A, B, D
03 B, C
04 C, D
If you produce table 3 (N = null)
01 A A N N N N
02 A A B D B B C D
03 B C N N
04 C C D D N N
Table 1 and Table 2, detect out how the data namely equal to the pertinent contact Table 3, Table 2, the second column is the character series.
best no apt use loops and cursors, the best SQL tin be secondhand to directly solve.
create table tb1 (c1 varchar (10), c2 varchar (10))
insert into tb1 values ('A', 'A')
insert into tb1 values ('B', 'B')
insert into tb1 values ('C', 'c')
insert into tb1 values ('D', 'd')
create table tb2 (c1 varchar (10), c2 varchar (10))
insert into tb2 values ('01 ',' A ')
insert into tb2 values ('02',如果你能达到18秒以上 (http://tjzx.hostpo.net/blog/article.php?type=blog&cid=1&itemid=271033), 'A, B, D')
insert into tb2 values (' 03 ',' B, C ')
insert into tb2 values ('04', 'C, D')
go
select m.c1,
; m.c21,
isnull ((select c2 + '' from tb1 n where n.c1 = m.c21),nike air jordans (http://www.nikeniceto.com/), 'N' ) +
isnull (m.c22 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c22), 'N') +
isnull (m.c23 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c23), 'N') +
isnull (m.c24 + '', 'N') +
isnull ((select c2 + '' from tb1 n where n.c1 = m.c24), 'N') c3
from
(
select c1,
; parsename (replace (reverse (c2), ',','.'), 1) c21,
parsename (replace (reverse (c2), ' , ','.'), 2) c22,
parsename (replace (reverse (c2),', ','.'), 3) c23,
parsename (replace (reverse (c2), ',','.'), 4) c24
from tb2
) m
drop table tb1, tb2
from alike problems -
/ * specification of the problem
tba
ID classid name
1 ; 1,2,3 suit
2 2,3 tunic
3 1 , 3 pants
tbb
id classname
1 dress shirt
2 I have 3 pants
result is
id classname ; name
1 clothes, jacket, pants suit
2 jacket, pants ; tunic
3 clothes, pants Name pants
* /
------------------------------------- ----------------
- sql server 2000 in a written
create table tba (ID int, classid varchar (20), name varchar (10))
insert into tba values (1, '1, 2,3 ',' suits ')
insert into tba values (2, '2, 3',mbt shoes las vegas (http://www.mbts-clearance.com/), 'tunic')
insert into tba values (3, '1, 3 ',' label of pants ')
create table tbb (ID varchar (10), classname varchar (10))
insert into tbb values ('1' , 'clothes')
insert into tbb values ('2 ',travel straighteners (http://www.ghdivsale.net/specials.html),' Top ')
insert into tbb values ('3', 'trousers')
go
- the first 1 kind of method, create a feature to display
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 (','+ hurl (id for varchar) +',',','+id +',')> 0
return matter (@ str, 1,1,'')
end
go
select id,shoes jordans (http://cgwave.dothome.co.kr/renew/smf/index.php?topic=954322.msg704713#msg704713), classid = dbo.f_hb (classid), name from tba
drop function f_hb
/ *
id classid name
----------- ------------- - ---------
1 clothes, jacket, pants suits
2 ; shirt, jeans tunic
3 dress, jeans were pants
(number of rows affected by line 3)
* /
- 2 ways. update
when (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 ; clothes, jacket, pants suits
2 T-shirt, pants tunic
3 ; clothes, trousers were pants
(number of rows affected by line 3)
* /
drop table tba, tbb
-------------------------------- ----------------------------------------
- sql server 2005 in the first decomposition tba the classid, and then merge classname
create table tba (ID integer, classid varchar (20), name varchar (10))
insert into tba values (1, '1, 2,3 ', 'suits')
insert into tba values (2, '2,mbt shoes chapa gtx (http://www.mbtoutletstores.com/), 3', 'tunic')
insert into tba values (3, '1, 3 ',' name pants')
establish chart tbb (ID varchar (10), classname varchar (10))
insert into tbb values ('1 ',' clothes')
insert into tbb values ('2 ', 'Top')
insert into tbb values ('3 ',' trousers')
go
SELECT 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,air max turbulence (http://www.nikeniceto.com/), [classid] = CONVERT (xml, ' ' + REPLACE ([classid], ',', ' ') + ' ') FROM tba) A
OUTER APPLY (SELECT classid = Nvvalue ('.',' varchar (100) ') FROM A. [ ,],[A
OUTER APPLY
(
SELECT [classname] = STUFF (REPLACE (REPLACE ((
SELECT classname FROM (choose tbc.id, tbc . name, tbb.classname from
(
SELECT A.id, A.name, B.classid FROM (SELECT id , name, [classid] = CONVERT (xml, ' ' + REPLACE ([classid], ',', ' ') + ' ; ') FROM tba) A
OUTER APPLY (SELECT classid = Nvvalue ('.',' 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), ' ',''), 1, 1,'')
) N
order by id
drop table tba, tbb
/ *
id ; classname name
----------- ---- ---------- ----------
1 clothes, jacket, pants suits
2 T-shirt, pants tunic
3 ; clothes, trousers were pants
(3 rows affected)
* /