表a
mobile mid num
12 2 4
13 2 3
14 2 1
15 3 11表b
mobile mid Province InsertTime
12 2 北京 2010-09-26
12 2 北京 2010-09-25
12 2 北京 2010-09-21
12 2 北京 2010-09-05
13 2 河南 2010-08-05
13 2 河南 2010-08-07
13 2 河南 2010-08-15select Province,count(distinct b.mobile),sum(a.num)
form b inner join a on a.mobile = b.mobile
where b.mid = 2 group by b.Province 为什么查询的结果sum(num)不是4和3呢?
mobile mid num
12 2 4
13 2 3
14 2 1
15 3 11表b
mobile mid Province InsertTime
12 2 北京 2010-09-26
12 2 北京 2010-09-25
12 2 北京 2010-09-21
12 2 北京 2010-09-05
13 2 河南 2010-08-05
13 2 河南 2010-08-07
13 2 河南 2010-08-15select Province,count(distinct b.mobile),sum(a.num)
form b inner join a on a.mobile = b.mobile
where b.mid = 2 group by b.Province 为什么查询的结果sum(num)不是4和3呢?
也可以达到这样的效果,也就是数记录的条数。
不是记录条数而是,累加num的值,比如说,把北京的手机号对应 的num值,sum到一起,的到最后北京num值的总数
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
go
if object_id('test.dbo.a') is not null drop table a
-- 创建数据表
create table a
(
mobile int,
mid int,
num int
)
go
--插入测试数据
insert into a select 12,2,4
union all select 13,2,3
union all select 14,2,1
union all select 15,3,11
go
if object_id('test.dbo.b') is not null drop table b
-- 创建数据表
create table b
(
mobile int,
mid int,
Province char(5),
InsertTime datetime
)
go
--插入测试数据
insert into b select 12,2,'北京','2010-09-26'
union all select 12,2,'北京','2010-09-25'
union all select 12,2,'北京','2010-09-21'
union all select 12,2,'北京','2010-09-05'
union all select 13,2,'河南','2010-08-05'
union all select 13,2,'河南','2010-08-07'
union all select 13,2,'河南','2010-08-15'
go
--代码实现select Province,mobile=count(distinct b.mobile),num=sum(a.num)
from b inner join a on a.mobile=b.mobile
where b.mid=2
group by b.Province/*测试结果Province mobile num
---------------------
北京 1 16
河南 1 9(2 行受影响)
*/
select Province,mobile=count(distinct b.mobile),num=sum(a.num)
from b inner join a on a.mobile=b.mobile
where b.mid=2
group by b.Province