sql left join 问题！！！

Question

只能是先分解后,再做LEFT JOIN的连接,分解的方法见下:分拆列值原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], 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
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)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 1

select t1.problem_name,t1.repair_name,count(t2.record_id) 记录数 
from  
 (select a.repair_name,b.problem_name  from 
  (select distinct repair_name from SVINFO_REPAIR_MST where add_time between '2008-1-1' and '2008-1-2')a,
SVINFO_PROBLEM_MST as b)t1 
left join 
    SVINFO_REPAIR_MST t2 
 on  t1.problem_name=t2.problem collate Chinese_PRC_CI_AS 
 and  t1.repair_name=t2.repair_name  and t2.add_time between '2008-1-1' and '2008-1-2'--加上條件
group by t1.repair_name,t1.problem_name
order by t1.repair_name desc

Answer 2

--> 测试数据: [SVINFO_PROBLEM_MST]
if object_id('[SVINFO_PROBLEM_MST]') is not null drop table [SVINFO_PROBLEM_MST]
create table [SVINFO_PROBLEM_MST] (id int,problem_name varchar(8))
insert into [SVINFO_PROBLEM_MST]
select 1,'数据更新' union all
select 2,'天气预报' union all
select 3,'主机维修' union all
select 4,'病毒感染' union all
select 5,'主机硬件'
--> 测试数据: [SVINFO_REPAIR_MST]
if object_id('[SVINFO_REPAIR_MST]') is not null drop table [SVINFO_REPAIR_MST]
create table [SVINFO_REPAIR_MST] (record_id int,problem varchar(50),add_Time varchar(8),repair_name varchar(8))
insert into [SVINFO_REPAIR_MST]
select 1,'病毒感染','2008-1-1','小张' union all
select 2,'主机维修','2008-1-3','小李' union all
select 3,'病毒感染','2008-1-2','小张' union all
select 4,'数据更新','2008-1-1','小张' union all
select 5,'主机硬件,主机维修','2008-2-1','小张'
--加上时间限制的话：
select t1.problem_name,t1.repair_name,count(t2.record_id) 记录数 
from  
 (select a.repair_name,b.problem_name  from 
  (select distinct repair_name from SVINFO_REPAIR_MST where add_time between '2008-1-1' and '2008-1-2')a,
SVINFO_PROBLEM_MST as b)t1 
left join 
    SVINFO_REPAIR_MST t2 
 on  charindex(','+t1.problem_name+',',','+t2.problem+',')>0
 and  t1.repair_name=t2.repair_name  
and add_time between '2008-1-1' and '2008-1-2'
group by t1.repair_name,t1.problem_name
order by t1.repair_name desc--去掉时间限制： 
select t1.problem_name,t1.repair_name,count(t2.record_id) 记录数 
from  
 (select a.repair_name,b.problem_name  from 
  (select distinct repair_name from SVINFO_REPAIR_MST)a,
SVINFO_PROBLEM_MST as b)t1 
left join 
    SVINFO_REPAIR_MST t2 
 on  charindex(','+t1.problem_name+',',','+t2.problem+',')>0
 and  t1.repair_name=t2.repair_name  
group by t1.repair_name,t1.problem_name
order by t1.repair_name desc

Answer 3

楼上的有错误
消息 468，级别 16，状态 9，第 1 行
无法解决 charindex 操作中 "SQL_Latin1_General_CP1_CI_AS" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。

Answer 4

select t1.problem_name,t1.repair_name,t2.add_time,count(t2.record_id) 记录数 
from  
 (select a.repair_name,b.problem_name  from 
  (select distinct repair_name from SVINFO_REPAIR_MST )a,
SVINFO_PROBLEM_MST as b)t1 
left join 
  
 (SELECT A.record_id,A.repair_name,A.problem,A.add_time, B.value 
FROM( 
    SELECT record_id,repair_name,add_time,problem = CONVERT(xml,' <root> <v>' + REPLACE(problem, ',', ' </v> <v>') + ' </v> </root>') FROM SVINFO_REPAIR_MST
)A 
OUTER APPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.problem.nodes('/root/v') N(v) 
)B ) t2 
 on  t1.problem_name=t2.problem collate Chinese_PRC_CI_AS 
 and  t1.repair_name=t2.repair_name  
group by t1.repair_name,t1.problem_name,t2.add_time
order by t1.repair_name desc
错误~~~
消息 447，级别 16，状态 0，第 1 行
表达式类型 xml 对 COLLATE 子句无效。 

Answer 5

--> --> 
 
if not object_id('Tempdb..#SVINFO_PROBLEM_MST') is null
drop table #SVINFO_PROBLEM_MST
Go
Create table #SVINFO_PROBLEM_MST([id] int,[problem_name] nvarchar(4))
Insert #SVINFO_PROBLEM_MST
select 1,N'数据更新' union all
select 2,N'天气预报' union all
select 3,N'主机维修' union all
select 4,N'病毒感染' union all
select 5,N'主机硬件'
Go
--> --> 
 
if not object_id('Tempdb..#SVINFO_REPAIR_MST') is null
drop table #SVINFO_REPAIR_MST
Go
Create table #SVINFO_REPAIR_MST([id] int,[problem] nvarchar(9),[add_Time] Datetime,[repair_name] nvarchar(2))
Insert #SVINFO_REPAIR_MST
select 1,N'病毒感染','2008-1-1',N'小张' union all
select 2,N'主机维修','2008-1-3',N'小李' union all
select 3,N'病毒感染','2008-1-2',N'小张' union all
select 4,N'数据更新','2008-1-1',N'小张' union all
select 5,N'主机硬件,主机维修','2008-2-1',N'小张'
Go
select t1.problem_name,t1.repair_name,count(t2.[id]) 记录数 
from  
 (select a.repair_name,b.problem_name  from 
  (select distinct repair_name from #SVINFO_REPAIR_MST )a,
#SVINFO_PROBLEM_MST as b)t1 
left join 
    #SVINFO_REPAIR_MST t2 
 on  patindex('%,'+t1.problem_name+',%',','+t2.problem+',' collate Chinese_PRC_CI_AS)>0 
 and  t1.repair_name=t2.repair_name  and t2.add_time between '2008-1-1' and '2008-2-1'--加上條件(2008-2-1)
group by t1.repair_name,t1.problem_name
order by t1.repair_name desc
(5 個資料列受到影響)(5 個資料列受到影響)
problem_name repair_name 记录数
------------ ----------- -----------
天气预报         小张          0
主机硬件         小张          1
主机维修         小张          1
病毒感染         小张          2
数据更新         小张          1
天气预报         小李          0
主机硬件         小李          0
主机维修         小李          1
病毒感染         小李          0
数据更新         小李          0
(10 個資料列受到影響)

Answer 6

patindex('%,'+t1.problem_name+',%',','+t2.problem+',' collate Chinese_PRC_CI_AS)>0
这句可以讲解一下吗？

Answer 7

等同
','+t2.problem+','  collate Chinese_PRC_CI_AS like '%,'+t1.problem_name+',%'