例如有三个数据表:
表1 表2
name num time name num time
AA 10 2004-12-12 AA 6 2004-12-23
BB 20 2004-12-4 AA 4 2004-11-12
CC 9 2004-11-11 BB 4 2004-11-3
AA 8 2004-11-19 cc 7 2004-12-3
CC 8 2004-12-23 BB 3 2004-12-5
AA 7 2004-12-11 CC 2 2004-11-5
BB 13 2004-11-23 BB 5 2004-11-10
统计结果应为:
表3
name num time
AA 11 2004-12
BB 17 2004-12
CC 1 2004-12
AA 4 2004-11
BB 4 2004-11
CC 7 2004-11
其中表3的数据是表1-表2的数量的结果,且按照月份来减!
用SQL 怎么统计的??
表1 表2
name num time name num time
AA 10 2004-12-12 AA 6 2004-12-23
BB 20 2004-12-4 AA 4 2004-11-12
CC 9 2004-11-11 BB 4 2004-11-3
AA 8 2004-11-19 cc 7 2004-12-3
CC 8 2004-12-23 BB 3 2004-12-5
AA 7 2004-12-11 CC 2 2004-11-5
BB 13 2004-11-23 BB 5 2004-11-10
统计结果应为:
表3
name num time
AA 11 2004-12
BB 17 2004-12
CC 1 2004-12
AA 4 2004-11
BB 4 2004-11
CC 7 2004-11
其中表3的数据是表1-表2的数量的结果,且按照月份来减!
用SQL 怎么统计的??
(select name, sum(num), Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2)) as time from Table1
group by Name, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2))union allselect name, sum(-num), Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2)) as time from Table2
group by Name, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2))
) A group by name, time
谢谢你的代码,但你好像没有统计结果,且并没有把结果放入table3中!
(select name, sum(num), Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2)) as time from Table1
group by Name, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2))union allselect name, sum(-num), Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2)) as time from Table2
group by Name, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2))
) A group by name, time
insert into table3 select name, Sum(num) as num, time from
(select name, sum(num) as num, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2)) as time from Table1
group by Name, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2))union allselect name, sum(-num) as sum, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2)) as time from Table2
group by Name, Cast(Year(time) As varchar(4)) + Cast(Month(time) As varchar(2))
) A group by name, time
(select name, sum(num) as num, CONVERT (varchar(7), time ,20) as time from Table1
group by Name, CONVERT (varchar(7), time ,20)union allselect name, sum(-num) as Sum, CONVERT (varchar(7), time ,20) as time from Table2
group by Name, CONVERT (Varchar(7), time ,20)
) A group by name, time
(select name, sum(num) as num, CONVERT (varchar(7), time ,20) as time from Table1
group by Name, CONVERT (varchar(7), time ,20)union allselect name, sum(-num) as Sum, CONVERT (varchar(7), time ,20) as time from Table2
group by Name, CONVERT (Varchar(7), time ,20)
) A group by name, time
应该没问题了吧。 怪不得老被K 工作不细心select name, Sum(num) as num, time from
(select name, sum(num) as num, CONVERT (varchar(7), time ,20) as time from Table1
group by Name, CONVERT (varchar(7), time ,20)union allselect name, sum(-num) as num, CONVERT (varchar(7), time ,20) as time from Table2
group by Name, CONVERT (Varchar(7), time ,20)
) A group by name, time
from table1 A,table2 B
where Substr(A.time,1,7)=Substr(B.time,1,7)
group by A.name,Substr(A.time,1,7)