两表之间的高难度关联查询SQL写法，请大牛出手，解决理解给分！
有两个表关联查询：
表a，字段：web,num
记录如下：
http://xxx.com/1, 10
http://xxx.com/1/2,5
http://xxx.com, 30
http://xxx.com/3/1,10
http://xxx.com/3/2,11
...
表b,字段：name,web,ifok
记录如下：
a,http://xxx.com, 1
b,http://xxx.com/1,0
c,http://xxx.com/3,0
...
希望关联a，b得到结果为：
字段name,num
a,30
b,15
c,21
也就是说表b的记录中ifok为1表示精确匹配表a的记录,ifok为0时模糊匹配表a的记录。
如果表b,字段：name,web,ifok
记录如下：
a,http://xxx.com, 1
b,http://xxx.com/1,1
c,http://xxx.com/3,0
...
那么关联a,b得到结果应该如下：
字段name,num
a,30
b,10
c,21
请教各位大牛，这种既要模糊又要精确的匹配，通过一条SQL语句或是一个存储过程如何实现？ SQL
[解决办法]
select b.name,sum(a.num)
from a,b
where (b.ifok = 1 and a.web = b.web)
or (b.ifok = 0 and a.web like b.web
[解决办法]
'%')
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [web] varchar(100), [num] int);
insert #temp
select 'http://xxx.com/1','10' union all
select 'http://xxx.com/1/2','5' union all
select 'http://xxx.com','30' union all
select 'http://xxx.com/3/1','10' union all
select 'http://xxx.com/3/2','11'
if OBJECT_ID('tempdb..#tempB', 'u') is not null drop table #tempB;
go
create table #tempB( [name] varchar(100), [web] varchar(100), [ifok] bit);
insert #tempB
select 'a','http://xxx.com','1' union all
select 'b','http://xxx.com/1','0' union all
select 'c','http://xxx.com/3','0'
--SQL:
select B.name, num = SUM(num)
from #tempB B
INNER JOIN #temp A
ON A.web LIKE (CASE WHEN B.ifok = 1 THEN '' ELSE '%' END) + B.web + (CASE WHEN B.ifok = 1 THEN '' ELSE '%' END)
GROUP BY B.name
/*
namenum
a30
b15
c21
*/
create table #ta(web varchar(100),num int)
insert into #ta
select 'http://xxx.com/1',10
union all select 'http://xxx.com/1/2',5
union all select 'http://xxx.com',30
union all select 'http://xxx.com/3/1',10
union all select 'http://xxx.com/3/2',11
create table #tb(name varchar(10),web varchar(100),ifok int)
insert into #tb
select 'a','http://xxx.com',1
union all select 'b','http://xxx.com/1',0
union all select 'c','http://xxx.com/3',0
select * from #ta
select * from #tb
select name,case when ifok=1 then num1 else num2 end as num
from (
select name,ifok,num1=(select sum(num) from #ta a where a.web=b.web)
,num2=(select sum(num) from #ta a where a.web like b.web+'%')
from #tb b)t
--结果:
/*
name num
a30
b15
c21
*/
update #tb set ifok=1 where name='b'
select name,case when ifok=1 then num1 else num2 end as num
from (
select name,ifok,num1=(select sum(num) from #ta a where a.web=b.web)
,num2=(select sum(num) from #ta a where a.web like b.web+'%')
from #tb b)t
--结果:
/*
name num
a30
b10
c21
*/
-- drop table #ta,#tb
create table 表a
(web varchar(50), num int)
insert into 表a
select 'http://xxx.com/1', 10 union all
select 'http://xxx.com/1/2', 5 union all
select 'http://xxx.com', 30 union all
select 'http://xxx.com/3/1', 10 union all
select 'http://xxx.com/3/2', 11
create table 表b
(name varchar(10), web varchar(50), ifok int)
insert into 表b
select 'a', 'http://xxx.com', 1 union all
select 'b', 'http://xxx.com/1', 0 union all
select 'b', 'http://xxx.com/2', 0 union all
select 'b', 'http://xxx.com/5/1', 0 union all
select 'c', 'http://xxx.com/3', 0 union all
select 'c', 'http://xxx.com/4', 0
select b.name,
b.web,
isnull(
case when b.ifok=1 then
(select sum(a.num) from 表a a where a.web=b.web)
when b.ifok=0 then
(select sum(a.num) from 表a a where charindex(b.web,a.web,1)>0) end,0) 'num'
from 表b b
/*
name web num
---------- -------------------------------------------------- -----------
a http://xxx.com 30
b http://xxx.com/1 15
b http://xxx.com/2 0
b http://xxx.com/5/1 0
c http://xxx.com/3 21
c http://xxx.com/4 0
(6 row(s) affected)
*/