请教一个两表关联判断的SQL语句
两个表
table A (tel_no,info,num) ; table B (area,tel_area)
A B
tel_no info num area tel_area
61234567 wang 1 西城 6123
61235467 yang 2 西城 61245
61245555 li 3 东城 6789
67894321 li 2 东城 6786
67866666 wang 1
67895555 li 5
我想找出每个区的号码个数及NUM总数
逻辑：B表中的tel_area与A表中的tel_no前四（五）位关联，即：通过比较
A表中的tel_no前四（五）位与B表中的tel_area字段来决定A表中该号码属于哪个区
注：若B表中的tel_area出现五位，则该五位的前四位就不会出现在B表中，如：存在 “西城 61245”这条记录,就不会有“西城 6124”这条记录.
由上述测试数据想得到如下结果:
area count total_num
西城 3 6(1+2+3得出的)
东城 3 8(2+1+5得出的)
问题点数：50、回复次数：11Top
1 楼zlp321002（Life Is Good,Let's Shine）回复于 2005-11-16 09:07:14 得分 20
select
area=B.area,
count=count(*),
total_num=sum(num)
from 表A A inner join 表B B
on substring(A.tel_no,1,4)=substring(B.tel_area,1,4)
group by B.area
order by area desc
Top
2 楼zlp321002（Life Is Good,Let's Shine）回复于 2005-11-16 09:08:00 得分 0
--测试环境
declare @A table(tel_no varchar(20),info varchar(10),num int)
insert into @A select '61234567','wang',1
union all select '61235467','yang',2
union all select '61245555','li',3
union all select '67894321','li',2
union all select '67866666','wang',1
union all select '67895555','li',5
declare @B table(area varchar(10),tel_area varchar(10))
insert into @B select '西城','6123'
union all select '西城','61245'
union all select '东城','6789'
union all select '东城','6786'
--查询语句
select
area=B.area,
count=count(*),
total_num=sum(num)
from @A A inner join @B B
on substring(A.tel_no,1,4)=substring(B.tel_area,1,4)
group by B.area
order by area desc
--结果
area count total_num
---------- ----------- -----------
西城 3 6
东城 3 8
Top
3 楼wangdehao（找找找(现在很幸福)）回复于 2005-11-16 09:14:27 得分 20
select area,count(*) as [count],sum(tel_area) as total_num from(select B.area,B.tel_area
from B inner join A
on left(A.tel_no,len(B.tel_area))=B.tel_area)t group by areaTop
4 楼wangdehao（找找找(现在很幸福)）回复于 2005-11-16 09:18:57 得分 0
select area,count(*) as [count],sum( num ) as total_num from
(select B.area ,B.tel_area,A.num
from @B B inner join @A A
on left(A.tel_no,len(B.tel_area))=B.tel_area)t
group by area
Top
5 楼wangdehao（找找找(现在很幸福)）回复于 2005-11-16 09:26:50 得分 0
1楼的有点问题的:
--当数据为:
declare @A table(tel_no varchar(20),info varchar(10),num int)
insert into @A select '61234567','wang',1
union all select '61235467','yang',2
union all select '61245555','li',3 -----它
union all select '67894321','li',2
union all select '67866666','wang',1
union all select '67895555','li',5
union all select '61246555','li',5 -------它
declare @B table(area varchar(10),tel_area varchar(10))
insert into @B select '西城','6123'
union all select '西城','61245' ---它
union all select '东城','6789'
union all select '东城','6786'
union all select '西城','61246' ----它
--时,结果是不正确的Top
6 楼spsaint（圣人）回复于 2005-11-16 10:06:31 得分 3
SELECT B.tel_area,COUNT(A.tel_no),SUM(A.NUM)
FROM A
INNER JOIN B ON SUBSTRING(tel_no ,1,4)=SUBSTRING( tel_area,1,4)
GROUP BY tel_areaTop
7 楼kubou（牧逸）回复于 2005-11-16 11:25:29 得分 3
select
area=B.area,
count=count(*),
total_num=sum(num)
from 表A A inner join 表B B
on substring(A.tel_no,1,4)=substring(B.tel_area,1,4)
group by B.area
order by area descTop
8 楼funsuzhou（☆【处变不惊】☆）回复于 2005-11-16 11:48:08 得分 4
declare @a table (tel_no int,info varchar(16),num int)
insert into @a
select 61234567, 'wang', 1 union all
select 61235467, 'yang', 2 union all
select 61245555, 'li', 3 union all
select 67894321, 'li', 2 union all
select 67866666, 'wang', 1 union all
select 67895555, 'li', 5
declare @b table (area varchar(16),tel_area int)
insert into @b
select '西城', 6123 union all
select '西城', 61245 union all
select '东城', 6789 union all
select '东城', 6786
select t.area,count(t.num)as [count],sum(t.num) as total_num from (
select b.area,a.num from @a a,@b b where charindex(cast(b.tel_area as varchar),cast(a.tel_no as char(8)))>0
)t group by t.area
/*
area count total_num
---------------- ----------- -----------
东城 3 8
西城 3 6
（所影响的行数为 2 行）
*/Top
9 楼funsuzhou（☆【处变不惊】☆）回复于 2005-11-16 12:23:28 得分 0
好象有问题，前面贴的不算
Top
10 楼funsuzhou（☆【处变不惊】☆）回复于 2005-11-16 12:38:32 得分 0
--乱了测试数据的顺序，并加了一些测试数据，以下是修改后的代码：
declare @a table (tel_no int,info varchar(16),num int)
insert into @a
select 61234567, 'wang', 1 union all
select 61245555, 'li', 3 union all
select 67894321, 'li', 2 union all
select 67866666, 'wang', 1 union all
select 61235467, 'yang', 2 union all
select 67895555, 'li', 5 union all
select 61245999, 'zhang', 6 union all
select 61238888, 'zhang', 7
declare @b table (area varchar(16),tel_area int)
insert into @b
select '西城', 6123 union all
select '西城', 61245 union all
select '东城', 6789 union all
select '东城', 6786
select t.area,count(num)as [count],sum(num)as total_num from (
select b.area,a.num,sumflag=case when charindex(cast(b.tel_area as varchar),cast(a.tel_no as char(8)))>0 then 1 else 0 end from @b b,@a a
)t
where t.sumflag=1
group by t.area
/*
area count total_num
---------------- ----------- -----------
东城 3 8
西城 5 19
（所影响的行数为 2 行）
*/Top
11 楼wangdehao（找找找(现在很幸福)）回复于 2005-11-16 12:53:26 得分 0
--这样也可以:
select area,count(*) as [count],sum( num ) as total_num from
(select B.area ,B.tel_area,A.num
from @B B inner join @A A
on charindex(B.tel_area,left(A.tel_no,5))>0)t
group by area
Top
