我现在有一表,客户关系表(khgxb),机构如下:都是字符类型,环境sql2000客户帐号 关系
10352652 刘一(jsxz0208)-开发关系,
10352158 刘二jsxz0208)-开发关系,
74582156 王五(00779)-维护关系,席动动(jsxz00838)-开发关系,
41525875 刘伟大(jsxz0208)-开发关系,
33021584 王雷好(jsxz02789)-开发关系,
52154658 毕慧明(jsxz0269)-开发关系,
12354851 席东(jsxz0038)-开发关系,王流留(007769)-维护关系,
备注:括号里的是工号,长度不一样,但最长12位。要求是:取出关系字段中,开发关系的员工姓名及工号,最后得到如下字段,请问sql语句如何写?客户帐号,关系,开发员工姓名,开发员工工号
10352652 刘一(jsxz0208)-开发关系,
10352158 刘二jsxz0208)-开发关系,
74582156 王五(00779)-维护关系,席动动(jsxz00838)-开发关系,
41525875 刘伟大(jsxz0208)-开发关系,
33021584 王雷好(jsxz02789)-开发关系,
52154658 毕慧明(jsxz0269)-开发关系,
12354851 席东(jsxz0038)-开发关系,王流留(007769)-维护关系,
备注:括号里的是工号,长度不一样,但最长12位。要求是:取出关系字段中,开发关系的员工姓名及工号,最后得到如下字段,请问sql语句如何写?客户帐号,关系,开发员工姓名,开发员工工号
这条得到的是什么结果?
这条得到的是什么结果?
得到结果如下:客户帐号 开发人 开发人工号
74582156 席动动 jsxz00838
insert into khgxb select '10352652','刘一(jsxz0208)-开发关系'
insert into khgxb select '10352158','刘二(jsxz0208)-开发关系' --这里少了一个左括号!
insert into khgxb select '74582156','王五(00779)-维护关系,席动动(jsxz00838)-开发关系'
insert into khgxb select '41525875','刘伟大(jsxz0208)-开发关系'
insert into khgxb select '33021584','王雷好(jsxz02789)-开发关系'
insert into khgxb select '52154658','毕慧明(jsxz0269)-开发关系'
insert into khgxb select '12354851','席东(jsxz0038)-开发关系,王流留(007769)-维护关系'
go
while exists(select 1 from khgxb where charindex(',',关系)>0)
begin
insert into khgxb select 客户帐号,left(关系,charindex(',',关系)-1) from khgxb where charindex(',',关系)>0
update khgxb set 关系=right(关系,len(关系)-charindex(',',关系)) where charindex(',',关系)>0
end
select 客户帐号,
right(关系,len(关系)-charindex('-',关系))关系,
left(关系,charindex('(',关系)-1)姓名,
substring(关系,charindex('(',关系)+1,charindex(')',关系)-charindex('(',关系)-1)工号
from khgxb
/*
客户帐号 关系 姓名 工号
---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
10352652 开发关系 刘一 jsxz0208
10352158 开发关系 刘二 jsxz0208
74582156 开发关系 席动动 jsxz00838
41525875 开发关系 刘伟大 jsxz0208
33021584 开发关系 王雷好 jsxz02789
52154658 开发关系 毕慧明 jsxz0269
12354851 维护关系 王流留 007769
74582156 维护关系 王五 00779
12354851 开发关系 席东 jsxz0038(9 行受影响)
*/
go
drop table khgxb
SubString(关系,CHARINDEX('(',关系,1)+1,CHARINDEX(')',关系,1)-CHARINDEX('(',关系,1)-1) From T
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-10-12 13:31:47
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([客户帐号] int,[关系] varchar(47))
insert [tb]
select 10352652,'刘一(jsxz0208)-开发关系' union all
select 10352158,'刘二(jsxz0208)-开发关系' union all
select 74582156,'王五(00779)-维护关系,席动动(jsxz00838)-开发关系' union all
select 41525875,'刘伟大(jsxz0208)-开发关系' union all
select 33021584,'王雷好(jsxz02789)-开发关系' union all
select 52154658,'毕慧明(jsxz0269)-开发关系' union all
select 12354851,'席东(jsxz0038)-开发关系,王流留(007769)-维护关系'
--------------开始查询--------------------------
;with f as
(
Select
a.客户帐号,关系=substring(a.关系,b.number,charindex(',',a.关系+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.关系)
where
substring(','+a.关系,b.number,1)=','
)select
客户帐号,
right(关系,len(关系)-charindex('-',关系)) as 关系,
left(关系,charindex('(',关系)-1)姓名,
substring(关系,charindex('(',关系)+1,charindex(')',关系)-charindex('(',关系)-1)工号
from
f
----------------结果----------------------------
/* 客户帐号 关系 姓名 工号
----------- ----------------------------------------------- ----------------------------------------------- -----------------------------------------------
10352652 开发关系 刘一 jsxz0208
10352158 开发关系 刘二 jsxz0208
74582156 维护关系 王五 00779
74582156 开发关系 席动动 jsxz00838
41525875 开发关系 刘伟大 jsxz0208
33021584 开发关系 王雷好 jsxz02789
52154658 开发关系 毕慧明 jsxz0269
12354851 开发关系 席东 jsxz0038
12354851 维护关系 王流留 007769(9 行受影响)*/
DROP TABLE #khgxb
create table #khgxb(客户帐号 varchar(10),关系 nvarchar(50))
insert into #khgxb select '10352652',N'刘一(jsxz0208)-开发关系'
insert into #khgxb select '10352158',N'刘二(jsxz0208)-开发关系' --这里少了一个左括号!
insert into #khgxb select '74582156',N'王五(00779)-维护关系,席动动(jsxz00838)-开发关系'
insert into #khgxb select '41525875',N'刘伟大(jsxz0208)-开发关系'
insert into #khgxb select '33021584',N'王雷好(jsxz02789)-开发关系'
insert into #khgxb select '52154658',N'毕慧明(jsxz0269)-开发关系'
insert into #khgxb select '12354851',N'席东(jsxz0038)-开发关系,王流留(007769)-维护关系'SELECT
客户帐号,
关系=RIGHT(关系,CHARINDEX('-',REVERSE(关系))-1),
开发员工姓名=LEFT(关系,CHARINDEX('(',关系)-1),
开发员工工号=SUBSTRING(关系,CHARINDEX('(',关系)+1,CHARINDEX(')',关系)-CHARINDEX('(',关系)-1)
FROM
(SELECT 客户帐号,关系=SUBSTRING(关系,b.number,CHARINDEX(',',关系+',',b.number)-b.number) FROM #khgxb AS a,master.dbo.spt_values AS b WHERE b.type='P'
AND charindex(',',','+a.关系,b.number)=b.number) AS a
/*
客户帐号 关系 开发员工姓名 开发员工工号
10352652 开发关系 刘一 jsxz0208
10352158 开发关系 刘二 jsxz0208
74582156 维护关系 王五 00779
74582156 开发关系 席动动 jsxz00838
41525875 开发关系 刘伟大 jsxz0208
33021584 开发关系 王雷好 jsxz02789
52154658 开发关系 毕慧明 jsxz0269
12354851 开发关系 席东 jsxz0038
12354851 维护关系 王流留 007769
*/
Case When (PATINDEX('%开发关系%',内容) > 0 ) Then Left(关系,CHARINDEX('(',内容,1)-1) Else '' End As 开发员工姓名,
Case When (PATINDEX('%开发关系%',内容) > 0 ) Then SubString(内容,CHARINDEX('(',内容,1)+1,CHARINDEX(')',内容,1)-CHARINDEX('(',内容,1)-1) Else '' End As 开发员工工号,
Case When (PATINDEX('%维护关系%',内容) > 0 ) Then Left(关系,CHARINDEX('(',内容,1)-1) Else '' End As 维护员工姓名,
Case When (PATINDEX('%维护关系%',内容) > 0 ) Then SubString(内容,CHARINDEX('(',内容,1)+1,CHARINDEX(')',内容,1)-CHARINDEX('(',内容,1)-1) Else '' End As 维护员工工号
From (
Select 客户帐号, 关系,
Case When (CHARINDEX(',',关系,1) > 0 ) Then Right(关系,Len(关系)-CHARINDEX(',',关系,1)) Else Left(关系,Len(关系) -CHARINDEX(',',关系,1)) End As 内容
From @T
Union
Select 客户帐号, 关系,
Left(关系,Len(关系) -CHARINDEX(',',关系,1))
From @T
) T
客户帐号 int,
关系 Varchar(100)
)Insert Into @T
Select 10352652, '刘一(jsxz0208)-开发关系'
Union Select 10352158, '刘二(jsxz0208)-开发关系'
Union Select 74582156, '王五(00779)-维护关系,席动动(jsxz00838)-开发关系'
Union Select 41525875, '刘伟大(jsxz0208)-开发关系'
Union Select 33021584, '王雷好(jsxz02789)-开发关系'
Union Select 52154658, '毕慧明(jsxz0269)-开发关系'
Union Select 12354851, '席东(jsxz0038)开发关系,王流留(007769)-维护关系'Select 客户帐号, 关系,
Case When (PATINDEX('%开发关系%',内容) > 0 ) Then Left(关系,CHARINDEX('(',内容,1)-1) Else '' End As 开发员工姓名,
Case When (PATINDEX('%开发关系%',内容) > 0 ) Then SubString(内容,CHARINDEX('(',内容,1)+1,CHARINDEX(')',内容,1)-CHARINDEX('(',内容,1)-1) Else '' End As 开发员工工号,
Case When (PATINDEX('%维护关系%',内容) > 0 ) Then Left(关系,CHARINDEX('(',内容,1)-1) Else '' End As 维护员工姓名,
Case When (PATINDEX('%维护关系%',内容) > 0 ) Then SubString(内容,CHARINDEX('(',内容,1)+1,CHARINDEX(')',内容,1)-CHARINDEX('(',内容,1)-1) Else '' End As 维护员工工号
From (
Select 客户帐号, 关系,
Case When (CHARINDEX(',',关系,1) > 0 ) Then Right(关系,Len(关系)-CHARINDEX(',',关系,1)) Else Left(关系,Len(关系) -CHARINDEX(',',关系,1)) End As 内容
From @T
Union
Select 客户帐号, 关系,
Left(关系,Len(关系) -CHARINDEX(',',关系,1))
From @T
) T