这样的统计又如何做？？

[zz791224]

例如有三个数据表：
表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 怎么统计的？？

[linzhisong]

大概就这样，具体你调试一下了

select name, Sum(num) as num, time from
(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 all

select 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

[linzhisong]

Sql server 版，

建议一下，字段的命名最好要特别一点，不然很容易跟数据库的关键字冲突的。

比如time

[zz791224]

to linzhisong(無聊)
谢谢你的代码，但你好像没有统计结果，且并没有把结果放入table3中！

[linzhisong]

insert into table3 select name, Sum(num) as num, time from
(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 all

select 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

[linzhisong]

你运行测试了吗？

只是那个年月你自己再处理一下。

理论上没有问题了

[linzhisong]

不好意思，忘了加别名了 as num
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 all

select 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

[risingsoft]

select A.name,sum(A.num-B.num),Substr(A.time,1,7)
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)

[wusikaishanshan]

谢谢了

[linzhisong]

先测试下这个语句有没有问题

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 all

select 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

[linzhisong]

没有问题就用这个

insert into table3 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 all

select 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

[linzhisong]

晕啊。

写什么啊 名字都写错了


应该没问题了吧。 怪不得老被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 all

select 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