帮忙分析问题

Question

表 khn_recstaff
createddate account avgcount  comments  recstaffguid
该表的数据
createddate account avgcount comments recstaffguid
2009-11-18 10:09:27.000 36.0 88.0 评价呀 DB0B8182-34C1-BA43-A358-56024605569A
表khn_staffdetail
recstaffguid  reasonlistid
数据
recstaffguid reasonlistid
DB0B8182-34C1-BA43-A358-56024605569A ,1E9FB3,898E64,A72ECK
DB0B8182-34C1-BA43-A358-56024605569A ,02076B,EA9D32,2A9AC7
表 khn_detailreason
kh_reason_id kh_reason_desc 
1E9FB3 KKS
898E64 DDF
A72ECK PPOI
02076B KSUW
EA9D32 ZEKE
2A9AC7 LIOW
需要一条sql语句显示
khn_recstaff的createddate ,account,avgcount, comments, recstaffguid字段
和表khn_staffdetail的recstaffguid 
以及khn_detailreason表的kh_reason_desc
注：khn_recstaff为主表
关联条件khn_recstaff.recstaffguid=khn_staffdetail.recstaffguid
以及khn_staffdetail.reasonlistid=khn_detailreason.kh_reason_id

Answer 1

select 
 a.createddate ,a.account,a.avgcount, a.comments, a.recstaffguid,b.recstaffguid,c.kh_reason_desc 
from
 khn_recstaff a
join
 khn_staffdetail b
on
 a..recstaffguid=b..recstaffguid
join
 khn_recstaff c
on
 b.reasonlistid=c.kh_reason_id
 

Answer 2

没有这么简单的，你这样写是错的
reasonlistid 的值,1E9FB3,898E64,A72ECK  是这样的呀。

Answer 3

SELECT T1.createddate ,T1.account,T1.avgcount, T1.comments, T1.recstaffguid, T2.recstaffguid, T3.kh_reason_desc FROM khn_recstaff T1 INNER JOIN khn_staffdetail T2 T1.recstaffguid = T2.recstaffguid
                     INNER JOIN khn_detailreason T3 T1.reasonlistid = T3.kh_reason_id

Answer 4

khn_staffdetail  的reasonlistid 字段的值为 ,1E9FB3,898E64,A72ECK 
而khn_detailreason 的kh_reason_id字段是
1E9FB3   
898E64   
A72ECK   
02076B   
EA9D32   
2A9AC7  
请问高手如何关联？这样INNER JOIN khn_detailreason T3 T1.reasonlistid = T3.kh_reason_id
可以吗？

Answer 5

declare @TB table([createddate] datetime,[account] numeric(3,1),[avgcount] numeric(3,1),[comments] varchar(6),[recstaffguid] uniqueidentifier)
insert @TB
select '2009-11-18 10:09:27.000',36.0,88.0,'评价呀','DB0B8182-34C1-BA43-A358-56024605569A'declare @TB1 table([recstaffguid] uniqueidentifier,[reasonlistid] varchar(21))
insert @TB1
select 'DB0B8182-34C1-BA43-A358-56024605569A',',1E9FB3,898E64,A72ECK' union all
select 'DB0B8182-34C1-BA43-A358-56024605569A',',02076B,EA9D32,2A9AC7'declare @TB2 table([kh_reason_id] varchar(6),[kh_reason_desc] varchar(4))
insert @TB2
select '1E9FB3','KKS' union all
select '898E64','DDF' union all
select 'A72ECK','PPOI' union all
select '02076B','KSUW' union all
select 'EA9D32','ZEKE' union all
select '2A9AC7','LIOW';with cte as
(
SELECT A.[recstaffguid], B.value AS [reasonlistid]
FROM( 
SELECT [recstaffguid], [reasonlistid] = CONVERT(xml,' <root> <v>' + REPLACE([reasonlistid], ',', ' </v> <v>') + ' </v> </root>') FROM @TB1 
)A 
OUTER APPLY( 
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[reasonlistid].nodes('/root/v') N(v) 
)B 
where B.value is not null and B.value <>''
)
SELECT T.createddate ,T.account,T.avgcount, T.comments, T.recstaffguid, T1.recstaffguid, T2.kh_reason_desc 
FROM @TB T LEFT JOIN cte T1 ON T.[recstaffguid] = T1.[recstaffguid]
   LEFT JOIN @TB2 T2 ON T1.[reasonlistid] = T2.[kh_reason_id]
/*
createddate             account                                 avgcount                                comments recstaffguid                         recstaffguid                         kh_reason_desc
----------------------- --------------------------------------- --------------------------------------- -------- ------------------------------------ ------------------------------------ --------------
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A KKS
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A DDF
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A PPOI
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A KSUW
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A ZEKE
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A LIOW(6 row(s) affected)

Answer 6

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-18 16:01:52
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([createddate] datetime,[account] numeric(3,1),[avgcount] numeric(3,1),[comments] varchar(6),[recstaffguid] uniqueidentifier)
insert [a]
select '2009-11-18 10:09:27.000',36.0,88.0,'评价呀','DB0B8182-34C1-BA43-A358-56024605569A'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([recstaffguid] uniqueidentifier,[reasonlistid] varchar(20))
insert [b]
select 'DB0B8182-34C1-BA43-A358-56024605569A','1E9FB3,898E64,A72ECK' union all
select 'DB0B8182-34C1-BA43-A358-56024605569A','02076B,EA9D32,2A9AC7'
--> 测试数据：[c]
if object_id('[c]') is not null drop table [c]
go 
create table [c]([kh_reason_id] varchar(6),[kh_reason_desc] varchar(4))
insert [c]
select '1E9FB3','KKS' union all
select '898E64','DDF' union all
select 'A72ECK','PPOI' union all
select '02076B','KSUW' union all
select 'EA9D32','ZEKE' union all
select '2A9AC7','LIOW'
--------------开始查询--------------------------
;with f as
(
Select
    a.recstaffguid,reasonlistid=substring(a.reasonlistid,b.number,charindex(',',a.reasonlistid+',',b.number)-b.number) 
from 
    b a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.reasonlistid)
where
     substring(','+a.reasonlistid,b.number,1)=','
)
select 
 a.createddate ,a.account,a.avgcount, a.comments, a.recstaffguid,b.recstaffguid,c.kh_reason_desc 
from
 a
join
 f b
on
 a.recstaffguid=b.recstaffguid
join
  c
on
 b.reasonlistid=c.kh_reason_id----------------结果----------------------------
/* createddate             account                                 avgcount                                comments recstaffguid                         recstaffguid                         kh_reason_desc
----------------------- --------------------------------------- --------------------------------------- -------- ------------------------------------ ------------------------------------ --------------
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A KKS
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A DDF
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A PPOI
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A KSUW
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A ZEKE
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A DB0B8182-34C1-BA43-A358-56024605569A LIOW(6 行受影响)
*/

Answer 7

declare @TB table([createddate] datetime,[account] numeric(3,1),[avgcount] numeric(3,1),[comments] varchar(6),[recstaffguid] uniqueidentifier)
insert @TB
select '2009-11-18 10:09:27.000',36.0,88.0,'评价呀','DB0B8182-34C1-BA43-A358-56024605569A'declare @TB1 table([recstaffguid] uniqueidentifier,[reasonlistid] varchar(21))
insert @TB1
select 'DB0B8182-34C1-BA43-A358-56024605569A',',1E9FB3,898E64,A72ECK' union all
select 'DB0B8182-34C1-BA43-A358-56024605569A',',02076B,EA9D32,2A9AC7'declare @TB2 table([kh_reason_id] varchar(6),[kh_reason_desc] varchar(4))
insert @TB2
select '1E9FB3','KKS' union all
select '898E64','DDF' union all
select 'A72ECK','PPOI' union all
select '02076B','KSUW' union all
select 'EA9D32','ZEKE' union all
select '2A9AC7','LIOW';with cte as
(
SELECT A.[recstaffguid], B.value AS [reasonlistid]
FROM( 
SELECT [recstaffguid], [reasonlistid] = CONVERT(xml,' <root> <v>' + REPLACE([reasonlistid], ',', ' </v> <v>') + ' </v> </root>') FROM @TB1 
)A 
OUTER APPLY( 
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[reasonlistid].nodes('/root/v') N(v) 
)B 
where B.value is not null and B.value <>''
)
SELECT T.createddate ,T.account,T.avgcount, T.comments, T.recstaffguid, T1.[reasonlistid], T2.kh_reason_desc 
FROM @TB T LEFT JOIN cte T1 ON T.[recstaffguid] = T1.[recstaffguid]
   LEFT JOIN @TB2 T2 ON T1.[reasonlistid] = T2.[kh_reason_id]
/*
createddate             account                                 avgcount                                comments recstaffguid                         reasonlistid                                                                                         kh_reason_desc
----------------------- --------------------------------------- --------------------------------------- -------- ------------------------------------ ---------------------------------------------------------------------------------------------------- --------------
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A 1E9FB3                                                                                               KKS
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A 898E64                                                                                               DDF
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A A72ECK                                                                                               PPOI
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A 02076B                                                                                               KSUW
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A EA9D32                                                                                               ZEKE
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A 2A9AC7                                                                                               LIOW(6 row(s) affected)

Answer 8

createddate ,account,avgcount, comments,kh_reason_desc
2009-11-18 10:09:27.000 36.0 88.0 评价呀    KKS,DDF,PPOI,KSUW,ZEKE,LIOW

Answer 9

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-18 16:01:52
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([createddate] datetime,[account] numeric(3,1),[avgcount] numeric(3,1),[comments] varchar(6),[recstaffguid] uniqueidentifier)
insert [a]
select '2009-11-18 10:09:27.000',36.0,88.0,'评价呀','DB0B8182-34C1-BA43-A358-56024605569A'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([recstaffguid] uniqueidentifier,[reasonlistid] varchar(20))
insert [b]
select 'DB0B8182-34C1-BA43-A358-56024605569A','1E9FB3,898E64,A72ECK' union all
select 'DB0B8182-34C1-BA43-A358-56024605569A','02076B,EA9D32,2A9AC7'
--> 测试数据：[c]
if object_id('[c]') is not null drop table [c]
go 
create table [c]([kh_reason_id] varchar(6),[kh_reason_desc] varchar(4))
insert [c]
select '1E9FB3','KKS' union all
select '898E64','DDF' union all
select 'A72ECK','PPOI' union all
select '02076B','KSUW' union all
select 'EA9D32','ZEKE' union all
select '2A9AC7','LIOW'
--------------开始查询--------------------------
;with f as
(
Select
    a.recstaffguid,reasonlistid=substring(a.reasonlistid,b.number,charindex(',',a.reasonlistid+',',b.number)-b.number) 
from 
    b a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.reasonlistid)
where
     substring(','+a.reasonlistid,b.number,1)=','
),
f1 as
(
select 
 a.createddate ,a.account,a.avgcount, a.comments, a.recstaffguid,c.kh_reason_desc 
from
 a
join
 f b
on
 a.recstaffguid=b.recstaffguid
join
  c
on
 b.reasonlistid=c.kh_reason_id
)
select 
  createddate,account,avgcount,comments,recstaffguid,
  [kh_reason_desc]=stuff((select ','+[kh_reason_desc] from f1 t where createddate=f1.createddate for xml path('')), 1, 1, '') 
from 
  f1 
group by 
  createddate,account,avgcount,comments,recstaffguid 
----------------结果----------------------------
/* createddate             account                                 avgcount                                comments recstaffguid                         kh_reason_desc
----------------------- --------------------------------------- --------------------------------------- -------- ------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009-11-18 10:09:27.000 36.0                                    88.0                                    评价呀      DB0B8182-34C1-BA43-A358-56024605569A KKS,DDF,PPOI,KSUW,ZEKE,LIOW(1 行受影响)
*/