select a.地区,convert(varchar(7),b.交易时间,120),sum(b.交易额) as 交易总额 from a join b on a.终端=b.终端 group by a.地区,convert(varchar(7),b.交易时间,120) having sum(b.交易额)<200你的哪个字段是算交易总额的?
create table tabA([终端] varchar(30), [地区] varchar(50)) create table tabB([终端] varchar(30), [交易情况] float,[交易时间] datetime) go insert tabA select 'T1', 'Region1' union all select 'T2', 'Region2' union all select 'T3', 'Region1' union all select 'T4', 'Region2'insert tabB select 'T1', 50,'2012-05-01' union all select 'T2', 10,'2012-05-13' union all select 'T1', 10,'2012-05-22' union all select 'T3', 400,'2012-06-01' union all select 'T1', 150,'2012-07-01' union all select 'T2', 10,'2012-04-13' union all select 'T1', 210,'2012-04-22' union all select 'T3', 40,'2012-06-01' union all select 'T4', 150,'2012-07-01'select [终端],[地区],[月份] from (select a.[终端],a.[地区],month(b.交易时间) '月份',sum(b.[交易情况]) '统计' from tabA a inner join tabB b on a.[终端]=b.[终端] Group by a.[终端],a.[地区],month(b.交易时间)) t where t.[统计]<200 drop table tabA drop table tabB/* 终端 地区 月份 ------------------------------ -------------------------------------------------- ----------- T1 Region1 5 T1 Region1 7 T2 Region2 4 T2 Region2 5 T4 Region2 7 (5 row(s) affected) */
create table tabA([终端] varchar(30), [地区] varchar(50)) create table tabB([终端] varchar(30), [交易情况] float,[交易时间] datetime) go insert tabA select 'T1', 'Region1' union all select 'T2', 'Region2' union all select 'T3', 'Region1' union all select 'T4', 'Region2'insert tabB select 'T1', 50,'2012-05-01' union all select 'T2', 10,'2012-05-13' union all select 'T1', 10,'2012-05-22' union all select 'T3', 400,'2012-06-01' union all select 'T1', 150,'2012-07-01' union all select 'T2', 10,'2012-04-13' union all select 'T1', 210,'2012-04-22' union all select 'T3', 40,'2012-06-01' union all select 'T4', 150,'2012-07-01'select count([终端]) as '终端数据',[地区],[月份] from (select a.[终端],a.[地区],month(b.交易时间) '月份',sum(b.[交易情况]) '统计' from tabA a inner join tabB b on a.[终端]=b.[终端] Group by a.[终端],a.[地区],month(b.交易时间)) t where t.[统计]<200 group by [地区],[月份] drop table tabA drop table tabB/* 终端数据 地区 月份 ------------------------------ -------------------------------------------------- ----------- 1 Region2 4 1 Region1 5 1 Region2 5 1 Region1 7 1 Region2 7 (5 row(s) affected) */
a.地区,convert(varchar(7),b.交易时间,120),sum(b.交易额) as 交易总额
from
a join b
on
a.终端=b.终端
group by
a.地区,convert(varchar(7),b.交易时间,120)
having
sum(b.交易额)<200你的哪个字段是算交易总额的?
create table tabB([终端] varchar(30), [交易情况] float,[交易时间] datetime)
go
insert tabA
select 'T1', 'Region1' union all
select 'T2', 'Region2' union all
select 'T3', 'Region1' union all
select 'T4', 'Region2'insert tabB
select 'T1', 50,'2012-05-01' union all
select 'T2', 10,'2012-05-13' union all
select 'T1', 10,'2012-05-22' union all
select 'T3', 400,'2012-06-01' union all
select 'T1', 150,'2012-07-01' union all
select 'T2', 10,'2012-04-13' union all
select 'T1', 210,'2012-04-22' union all
select 'T3', 40,'2012-06-01' union all
select 'T4', 150,'2012-07-01'select [终端],[地区],[月份]
from
(select a.[终端],a.[地区],month(b.交易时间) '月份',sum(b.[交易情况]) '统计' from tabA a
inner join tabB b
on a.[终端]=b.[终端]
Group by a.[终端],a.[地区],month(b.交易时间)) t
where t.[统计]<200
drop table tabA
drop table tabB/*
终端 地区 月份
------------------------------ -------------------------------------------------- -----------
T1 Region1 5
T1 Region1 7
T2 Region2 4
T2 Region2 5
T4 Region2 7
(5 row(s) affected)
*/
地区 终端数量 月份
region1 num1 5
region2 num2 5
.....
create table tabB([终端] varchar(30), [交易情况] float,[交易时间] datetime)
go
insert tabA
select 'T1', 'Region1' union all
select 'T2', 'Region2' union all
select 'T3', 'Region1' union all
select 'T4', 'Region2'insert tabB
select 'T1', 50,'2012-05-01' union all
select 'T2', 10,'2012-05-13' union all
select 'T1', 10,'2012-05-22' union all
select 'T3', 400,'2012-06-01' union all
select 'T1', 150,'2012-07-01' union all
select 'T2', 10,'2012-04-13' union all
select 'T1', 210,'2012-04-22' union all
select 'T3', 40,'2012-06-01' union all
select 'T4', 150,'2012-07-01'select count([终端]) as '终端数据',[地区],[月份]
from
(select a.[终端],a.[地区],month(b.交易时间) '月份',sum(b.[交易情况]) '统计' from tabA a
inner join tabB b
on a.[终端]=b.[终端]
Group by a.[终端],a.[地区],month(b.交易时间)) t
where t.[统计]<200
group by [地区],[月份]
drop table tabA
drop table tabB/*
终端数据 地区 月份
------------------------------ -------------------------------------------------- -----------
1 Region2 4
1 Region1 5
1 Region2 5
1 Region1 7
1 Region2 7
(5 row(s) affected)
*/