有两个表关联查询:
表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
表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
解决方案 »
- 请教access行转列问题
- 如何判断查询结果的个数?
- 如何将一台电脑中所有数据库快速复制到另外一台电脑中?
- 数据导入的问题
- 怎样跟踪查询的物理读数与逻辑读数
- 问一个存储过程的小问题
- 安装sp3出错?
- 数据格式问题
- SQLServer 的全文索引 的 全文目录出了问题,重新生成没有项计数,而且目录大小总是0MB
- 请各位高手指教:给新手一点建议(Access——SQLServer)。
- 求一句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 || '%')
你试试 这样可以不? 我没有安装Oracle,没有试过 ~~~
如果不行的话 将 or的条件拆出来 用 union 链接 这样应该也是算一段SQL吧
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/*
name num
a 30
b 15
c 21
*/
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',11create 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',0select * 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
a 30
b 15
c 21
*/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
a 30
b 10
c 21
*/-- drop table #ta,#tb
表b,字段:name,web,ifok
比如记录如下:
a,http://xxx.com, 1
b,http://xxx.com/1,0
b,http://xxx.com/2,0
b,http://xxx.com/5/1,0
c,http://xxx.com/3,0
c,http://xxx.com/4,0
表b,字段:name,web,ifok
比如记录如下:
a,http://xxx.com, 1
b,http://xxx.com/1,0
b,http://xxx.com/2,0
b,http://xxx.com/5/1,0
c,http://xxx.com/3,0
c,http://xxx.com/4,0
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', 11create 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)
*/