高难度的数据筛选

Question

各位朋友，想请教一下，有两个表表1如下：
kwph  ms  ph
a-123 s   1
a-123 m   2
a-123 l   3
a-123 l   4
a-123 l   5
a-123 l   6
a-123 m   7
a-123 m   8
a-123 m   9
a-123 l   10
a-123 l   11
a-123 l   12
a-123 l   13
a-123 l   14
表2如下：
kwph   ph
a-123  1
a-123  2-3
a-123  4-5-6
a-123  7-8-9-10
现在想作一个选择，找出表1里面有的PH，而表2没有的PH最终想要的结果为：
kwph    ph
a-123   11
a-123   12
a-123   13
a-123   14请论坛上的各位朋友赐教，谢谢！

Answer 1

select kwph,ph from tb1
except
select kwph,ph from tb2

Answer 2

前面看错了
SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO a FROM syscolumns a, syscolumns b 
select kwph,ph from tb1
except
select kwph,value from (
SELECT A.kwph, SUBSTRING(A.ph, B.id, CHARINDEX('-', A.ph + '-', B.id) - B.id) as value
FROM tb A, a B 
WHERE SUBSTRING('-' + A.ph, B.id, 1) = '-' 
order by A.kwph) t

Answer 3

--还有一种做法
select kwph,ph 
from tb1
where not exists (select * from tb2 where kwph=tb1.kwph and CHARINDEX(rtrim(tb1.ph),ph)>0)
2楼的写法我修改个地方SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO a FROM syscolumns a, syscolumns b 
select kwph,ph from tb1
except
select kwph,value from (
SELECT A.kwph, SUBSTRING(A.ph, B.id, CHARINDEX('-', A.ph + '-', B.id) - B.id) as value
FROM tb2 A, a B 
WHERE SUBSTRING('-' + A.ph, B.id, 1) = '-' 
order by A.kwph) t

Answer 4

--2005 TRY 
select kwph,ph 
from 表1 a 
where 
ph not in
(select ph from 
(select 
    a.kwph,b.ph
from 
    (select kwph,ph=convert(xml,'<root><v>'+replace(ph,',','</v><v>')+'</v></root>') from tb)a
outer apply
    (select ph=C.v.value('.','nvarchar(100)') from a.ph.nodes('/root/v')C(v))b)t

Answer 5

select kwph,ph 
from 表1 a 
where 
ph not in
(select ph from 
(select 
    a.kwph,b.ph
from 
    (select kwph,ph=convert(xml,'<root><v>'+replace(ph,',','</v><v>')+'</v></root>') from 表2)a
outer apply
    (select ph=C.v.value('.','nvarchar(100)') from a.ph.nodes('/root/v')C(v))b)t

Answer 6

--差个括号
select kwph,ph 
from 表1 
where 
ph not in
(select ph from 
(select 
    a.kwph,b.ph
from 
    (select kwph,ph=convert(xml,'<root><v>'+replace(ph,',','</v><v>')+'</v></root>') from 表2)a
outer apply
    (select ph=C.v.value('.','nvarchar(100)') from a.ph.nodes('/root/v')C(v))b)t)

Answer 7

declare @tb1 table (kwph nvarchar(10),ms nvarchar(10),ph int)
insert into @tb1 select 'a-123','s',1
      union all select 'a-123','m',2
      union all select 'a-123','1',3
      union all select 'a-123','1',4
      union all select 'a-123','1',5
      union all select 'a-123','1',6
      union all select 'a-123','m',7
      union all select 'a-123','m',8
      union all select 'a-123','m',9
      union all select 'a-123','1',10
      union all select 'a-123','1',11
      union all select 'a-123','1',12
      union all select 'a-123','1',13
      union all select 'a-123','1',14
declare @tb2 table (kwph nvarchar(10),ph nvarchar(10))
insert into @tb2 select 'a-123','1'
    union all    select 'a-123','2-3'
    union all    select 'a-123','4-5-6'
    union all    select 'a-123','7-8-9-10'
select * from @tb1 a where not exists (select * from @tb2 where CHARINDEX(ltrim(a.ph),ph)>0)kwph       ms         ph
---------- ---------- -----------
a-123      1          11
a-123      1          12
a-123      1          13
a-123      1          14(4 行受影响)

Answer 8

declare @t1 table (kwph varchar(10), ms varchar(10),ph varchar(20))
insert into @t1 (kwph,ms,ph)
select 'a-123','s',  '1' 
union all select 'a-123','m',  '2' 
union all select 'a-123','l',  '3' 
union all select 'a-123','l',  '4' 
union all select 'a-123','l',  '5' 
union all select 'a-123','l' , '6' 
union all select 'a-123','m',  '7' 
union all select 'a-123','m',  '8' 
union all select 'a-123','m',  '9' 
union all select 'a-123','l',  '10' 
union all select 'a-123','l',  '11' 
union all select 'a-123','l',  '12' 
union all select 'a-123','l',  '13' 
union all select 'a-123','l',  '14' declare @t2 table(kwph varchar(10),  ph varchar(50))
insert into @t2 (kwph,ph)
select 'a-123' , '1' 
union all select 'a-123' , '2-3' 
union all select 'a-123' , '4-5-6' 
union all select 'a-123' , '7-8-9-10' select * from @t1 a
where not exists(select * from @t2 where charindex('-'+a.ph+'-','-'+ph+'-',1)>0)

Answer 9

declare @tb1 table (kwph nvarchar(10),ms nvarchar(10),ph int)
insert into @tb1 select 'a-123','s',1
      union all select 'a-123','m',2
      union all select 'a-123','1',3
      union all select 'a-123','1',4
      union all select 'a-123','1',5
      union all select 'a-123','1',6
      union all select 'a-123','m',7
      union all select 'a-123','m',8
      union all select 'a-123','m',9
      union all select 'a-123','1',10
      union all select 'a-123','1',11
      union all select 'a-123','1',12
      union all select 'a-123','1',13
      union all select 'a-123','1',14
declare @tb2 table (kwph nvarchar(10),ph nvarchar(10))
insert into @tb2 select 'a-123','1'
    union all    select 'a-123','2-3'
    union all    select 'a-123','4-5-6'
    union all    select 'a-123','7-8-9-10'select kwph,ph from @tb1 where ph not in
(select a.ph from @tb1 a inner join @tb2 b on charindex(cast(a.ph as varchar(10)),b.ph)>0 group by a.ph)