a1和b1两个字段两连接，同一天时间的a2,b2分别汇总，同时求出汇总后的百分比b2对a2的百分比
向csdn达人求解
表a
字符弄 整型 日期时间型
a1 a2 a3
a 1 2010-05-05 01:20:20
a 1 2010-05-05 01:20:20
b 2 2010-05-05 01:20:20
c 1 2010-06-05 01:20:20
c 2 2010-05-05 01:20:20
d 1 2010-06-05 01:20:20
d 1 2010-06-05 01:20:20
d 1 2010-06-05 01:20:20表b
字符弄 整型 日期时间型
b1 b2 b3
a 1 2010-05-05 01:20:20
a 1 2010-05-05 01:20:20
b 2 2010-05-05 01:20:20
c 2 2010-05-05 01:20:20
d 1 2010-06-05 01:20:20
两表处理后的结果为
字符弄 整型 整型 double 两位数 日期时间型
a1 a2 b2 百分比(b2/a2*100)% 日期
a 2 2 100 2010-05-05 01:20:20
b 2 2 100 2010-05-05 01:20:20
c 1 0 0 2010-06-05 01:20:20
c 2 2 100 2010-05-05 01:20:20
d 3 1 33.33 2010-06-05 01:20:20
insert into a values('a', 1 ,'2010-05-05 01:20:20')
insert into a values('a', 1 ,'2010-05-05 01:20:20')
insert into a values('b', 2 ,'2010-05-05 01:20:20')
insert into a values('c', 1 ,'2010-06-05 01:20:20')
insert into a values('c', 2 ,'2010-05-05 01:20:20')
insert into a values('d', 1 ,'2010-06-05 01:20:20')
insert into a values('d', 1 ,'2010-06-05 01:20:20')
insert into a values('d', 1 ,'2010-06-05 01:20:20')
create table b(b1 varchar(10),b2 int,b3 datetime)
insert into b values('a', 1 ,'2010-05-05 01:20:20')
insert into b values('a', 1 ,'2010-05-05 01:20:20')
insert into b values('b', 2 ,'2010-05-05 01:20:20')
insert into b values('c', 2 ,'2010-05-05 01:20:20')
insert into b values('d', 1 ,'2010-06-05 01:20:20')
goselect m.a1 ,
m.a2,
isnull(n.b2,0) b2,
cast(isnull(n.b2,0)*100.0 / m.a2 as decimal(18,2)) [百分比],
m.a3
from
(select a1 , sum(a2) a2 , convert(varchar(10),a3,120) a3 from a group by a1 , convert(varchar(10),a3,120)) m
left join
(select b1 , sum(b2) b2 , convert(varchar(10),b3,120) b3 from b group by b1 , convert(varchar(10),b3,120)) n
on m.a1 = n.b1 and m.a3 = n.b3drop table a , b/*
a1 a2 b2 百分比 a3
---------- ----------- ----------- -------------------- ----------
a 2 2 100.00 2010-05-05
b 2 2 100.00 2010-05-05
c 2 2 100.00 2010-05-05
c 1 0 .00 2010-06-05
d 3 1 33.33 2010-06-05（所影响的行数为 5 行）
*/