一个关于SQL的查询语句

Question

A表:                            B表
id(自增)   name       type      id(自增)   name         type 
1        张三_李四              1          张三        属于A队
2        王五_周六              2          李四        属于B队
..................              3          王五        属于C队
..................              4          周六        属于D队
..................              ...............................要实现在效果:
A表:                                                              B表
id(自增)   name       type                                           id(自增)   name         type 
1        张三_李四    张三,属于A队,李四,属于B队                      1          张三        属于A队
2        王五_周六    张三,属于A队,李四,属于B                        2          李四        属于B队
..................                                                   3          王五        属于C队
..................                                                   4          周六        属于D队
..................                                                    ...............................
就是根据A表中的name和B表中的name 和type 确定A表的type值
SQL语句实现  高效

Answer 1

拆分又合并?分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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 2

A表                                 B表
id   name      type      id   name type   
1    张三_李四               1    张表  是人
2.................        2    李四 不是人要实现效果:
A表                           
id       name              type      
1    张三_李四           张三,是人,李四,不是人    
2.................       .................
就是根据A表中的name和B表中的name 和type 确定A表的type值
SQL语句实现 高效

Answer 3

id name type id name type   
1 张三_李四 1 张表 是人  “表"为“三”吧

Answer 4

--sql 2000 查询
create table A(id int, name varchar(10),type varchar(100))
create table b(id int, name varchar(10),type varchar(20))
insert into a values(1 ,'张三_李四','')
insert into a values(2 ,'王五_周六','') 
insert into b values(1 ,'张三', '属于A队')
insert into b values(2 ,'李四', '属于B队')
insert into b values(3 ,'王五', '属于C队')
insert into b values(4 ,'周六', '属于D队')
goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(b.type as varchar) from 
(
SELECT A.id, name = SUBSTRING(A.[name],t.id, CHARINDEX('_', A.[name] + '_', t.id) - t.id)
FROM A, tmp t
WHERE SUBSTRING('_' + A.[name], t.id, 1) = '_'
) m , b 
where m.id = @id and m.name = b.name
    set @str = right(@str , len(@str) - 1)
    return @str
end
go--调用函数
select m.id ,type = dbo.f_str(m.id) from 
(
SELECT A.id, name = SUBSTRING(A.[name],t.id, CHARINDEX('_', A.[name] + '_', t.id) - t.id)
FROM A, tmp t
WHERE SUBSTRING('_' + A.[name], t.id, 1) = '_'
) m , b where m.name = b.name group by m.iddrop function dbo.f_strdrop table a , b,tmp/*
id          type                                                                                                 
----------- ---------------------------------------------------------------------------------------------------- 
1           属于A队,属于B队
2           属于C队,属于D队（所影响的行数为 2 行）*/

Answer 5

--sql 2000查询.
create table A(id int, name varchar(10),type varchar(100))
create table b(id int, name varchar(10),type varchar(20))
insert into a values(1 ,'张三_李四','')
insert into a values(2 ,'王五_周六','') 
insert into b values(1 ,'张三', '属于A队')
insert into b values(2 ,'李四', '属于B队')
insert into b values(3 ,'王五', '属于C队')
insert into b values(4 ,'周六', '属于D队')
goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + m.name + ',' + cast(b.type as varchar) from 
(
SELECT A.id, name = SUBSTRING(A.[name],t.id, CHARINDEX('_', A.[name] + '_', t.id) - t.id)
FROM A, tmp t
WHERE SUBSTRING('_' + A.[name], t.id, 1) = '_'
) m , b 
where m.id = @id and m.name = b.name
    set @str = right(@str , len(@str) - 1)
    return @str
end
go--调用函数
select a.id ,a.name , type = dbo.f_str(a.id) from 
(
SELECT A.id, name = SUBSTRING(A.[name],t.id, CHARINDEX('_', A.[name] + '_', t.id) - t.id)
FROM A, tmp t
WHERE SUBSTRING('_' + A.[name], t.id, 1) = '_'
) m , a , b where a.id = m.id and m.name = b.name group by a.id,a.namedrop function dbo.f_strdrop table a , b,tmp/*
id          name       type                                                                                                 
----------- ---------- ---------------------------------------------------------------------------------------------------- 
1           张三_李四      张三,属于A队,李四,属于B队
2           王五_周六      王五,属于C队,周六,属于D队（所影响的行数为 2 行）*/

Answer 6

--sql 2000更新.
create table A(id int, name varchar(10),type varchar(100))
create table b(id int, name varchar(10),type varchar(20))
insert into a values(1 ,'张三_李四','')
insert into a values(2 ,'王五_周六','') 
insert into b values(1 ,'张三', '属于A队')
insert into b values(2 ,'李四', '属于B队')
insert into b values(3 ,'王五', '属于C队')
insert into b values(4 ,'周六', '属于D队')
goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + m.name + ',' + cast(b.type as varchar) from 
(
SELECT A.id, name = SUBSTRING(A.[name],t.id, CHARINDEX('_', A.[name] + '_', t.id) - t.id)
FROM A, tmp t
WHERE SUBSTRING('_' + A.[name], t.id, 1) = '_'
) m , b 
where m.id = @id and m.name = b.name
    set @str = right(@str , len(@str) - 1)
    return @str
end
go--调用函数
update a set type = k.type from a , 
(
select a.id ,a.name , type = dbo.f_str(a.id) from 
(
SELECT A.id, name = SUBSTRING(A.[name],t.id, CHARINDEX('_', A.[name] + '_', t.id) - t.id)
FROM A, tmp t
WHERE SUBSTRING('_' + A.[name], t.id, 1) = '_'
) m , a , b where a.id = m.id and m.name = b.name group by a.id,a.name
) k where a.id = k.idselect * from adrop function dbo.f_strdrop table a , b,tmp/*
id          name       type                                                                                                 
----------- ---------- ---------------------------------------------------------------------------------------------------- 
1           张三_李四      张三,属于A队,李四,属于B队
2           王五_周六      王五,属于C队,周六,属于D队（所影响的行数为 2 行）*/

Answer 7

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-25 23:02:50
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([id] INT,[name] NVARCHAR(10),[type] NVARCHAR(40))
INSERT [a]
SELECT 1,N'张三_李四',NULL
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT,[name] NVARCHAR(10),[type] NVARCHAR(10))
INSERT [b]
SELECT 1,N'张三',N'是人' UNION ALL
SELECT 2,N'李四',N'不是人'
GO
--SELECT * FROM [b]-->SQL查询如下:
IF NOT OBJECT_ID('[fn_test]') IS NULL
DROP function [fn_test]
GO
create function fn_test(@name nvarchar(20))
returns nvarchar(50)
as
begin
declare @s nvarchar(50)
select @s=isnull(@s+',','') +[name]+','+[type]
from b
where charindex([name],@name)>0
return @s
end
goupdate a set 
a.type=dbo.fn_test(name)select * from a
/*
id          name       type
----------- ---------- ----------------------------------------
1           张三_李四      张三,是人,李四,不是人(1 行受影响)*/

Answer 8

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-25 23:02:50
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:aIF NOT OBJECT_ID('[a]') IS NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([id] INT,[name] NVARCHAR(10),[type] NVARCHAR(40))
INSERT [a]
SELECT 1,N'张三_李四',NULL
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF NOT OBJECT_ID('[b]') IS NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([id] INT,[name] NVARCHAR(10),[type] NVARCHAR(10))
INSERT [b]
SELECT 1,N'张三',N'是人' UNION ALL
SELECT 2,N'李四',N'不是人'
GO
--SELECT * FROM [b]-->SQL查询如下:
IF NOT OBJECT_ID('[fn_test]') IS NULL
DROP function [fn_test]
GO
create function fn_test(@name nvarchar(20))
returns nvarchar(50)
as
begin
declare @s nvarchar(50)
select @s=isnull(@s+',','') +[name]+','+[type]
from b
where charindex('_'+[name]+'_','_'+@name+'_')>0
return @s
end
goupdate a set 
a.type=dbo.fn_test(name)
where dbo.fn_test(name) is not nullselect * from a
/*
id          name       type
----------- ---------- ----------------------------------------
1           张三_李四      张三,是人,李四,不是人(1 行受影响)*/这样好点

Answer 9

其实就是个先拆分再合并.9,10楼为2000的方法.以下有2005的方法./*
标题：数据拆分1
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1)
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)
)B--方法2)
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]=C.v.value('.','nvarchar(100)') from a.[value].nodes('/root/v')C(v))b--方法3)
;with tt as 
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*//*
标题：按某字段合并字符串之一(简单合并)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-06
地点：广东深圳描述：将如下形式的数据按id字段合并value字段。
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     value
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(value as varchar) from tb where id = @id
    set @str = right(@str , len(@str) - 1)
    return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop table tb
--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
    if @id = @id_old
       select @s = @s + ',' + cast(@value as varchar)
    else
      begin
        insert @t values(@id_old , stuff(@s,1,1,''))
        select @s = ',' + cast(@value as varchar) , @id_old = @id
      end
    fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursorselect * from @t
drop table tb