表一:
时间1 数量1
2007-1-20 200
2007-1-22 300
2007-1-23 400
2007-1-25 500
2007-1-25 300
2007-1-25 200
2007-1-25 100
2007-1-25 400表2:
时间2 数量2
2007-1-20 100
2007-1-27 200
2007-1-25 500量表组合之后成一个新表,就是用视图连接成一个新表:
时间3 数量1 数量2
2007-1-20 200 100
2007-1-22 300 0
2007-1-23 400 0
2007-1-25 500 500
2007-1-25 300 500
2007-1-25 200 500
2007-1-25 100 500
2007-1-25 400 500
2007-1-27 0 200但是我要将新表中的字段数量1+数量2得出一个数量之后再吧这个月的总数量计算机出来!
但是这样计算的话绝对不正确,我想要结果如下的表格:
时间4 数量1 数量2
2007-1-20 200 100
2007-1-22 300 0
2007-1-23 400 0
2007-1-25 500 500
2007-1-25 300 0
2007-1-25 200 0
2007-1-25 100 0
2007-1-25 400 0
2007-1-27 0 200或是高手看明白了我的意思,可以得出这样的结果也可以!请各位帮帮忙!
时间1 数量1
2007-1-20 200
2007-1-22 300
2007-1-23 400
2007-1-25 500
2007-1-25 300
2007-1-25 200
2007-1-25 100
2007-1-25 400表2:
时间2 数量2
2007-1-20 100
2007-1-27 200
2007-1-25 500量表组合之后成一个新表,就是用视图连接成一个新表:
时间3 数量1 数量2
2007-1-20 200 100
2007-1-22 300 0
2007-1-23 400 0
2007-1-25 500 500
2007-1-25 300 500
2007-1-25 200 500
2007-1-25 100 500
2007-1-25 400 500
2007-1-27 0 200但是我要将新表中的字段数量1+数量2得出一个数量之后再吧这个月的总数量计算机出来!
但是这样计算的话绝对不正确,我想要结果如下的表格:
时间4 数量1 数量2
2007-1-20 200 100
2007-1-22 300 0
2007-1-23 400 0
2007-1-25 500 500
2007-1-25 300 0
2007-1-25 200 0
2007-1-25 100 0
2007-1-25 400 0
2007-1-27 0 200或是高手看明白了我的意思,可以得出这样的结果也可以!请各位帮帮忙!
timepoint, amount=sum(amount)
from (select timepoint,amount from table1 union
select timepoint,amount from table2) as a
group by timepoint
insert @ta select
'2007-1-20',200 union select
'2007-1-22', 300 union select
'2007-1-23', 400 union select
'2007-1-25', 500 union select
'2007-1-25', 300 union select
'2007-1-25', 200 union select
'2007-1-25', 100 union select
'2007-1-25', 400 declare @tb table(时间2 datetime,数量2 int)
insert @tb select
'2007-1-20', 100 union select
'2007-1-27', 200 union select
'2007-1-25', 500
select isnull(a.时间1,b.时间2) 时间,
数量1 = isnull(a.数量1,0),
数量2 = isnull(case when (a.数量1 = b.数量2 ) or 1>=(select count(1) from @ta where 时间1 = b.时间2) then 数量2 else 0 end,0)
from @ta a full join @tb b
on a.时间1 = b.时间2
order by 时间,数量1 desc
/*时间 数量1 数量2
------------------------------------------------------ ----------- -----------
2007-01-20 00:00:00.000 200 100
2007-01-22 00:00:00.000 300 0
2007-01-23 00:00:00.000 400 0
2007-01-25 00:00:00.000 500 500
2007-01-25 00:00:00.000 400 0
2007-01-25 00:00:00.000 300 0
2007-01-25 00:00:00.000 200 0
2007-01-25 00:00:00.000 100 0
2007-01-27 00:00:00.000 0 200(所影响的行数为 9 行)
*/
select isnull(t1.时间,t2.时间), isnull(t1.数量1,0), isnull(t2.数量2,0)
from 表1 t1 full join 表2 t2 on t1.时间=t2.时间Plan B:
select 时间, sum(数量1), sum(数量2)
from (select 时间,数量1,0 from 表1 union select 时间,0,数量2 from 表2)
group by 时间
create table ta(时间1 datetime,数量1 int)
insert ta select
'2007-1-20',200 union select
'2007-1-22', 300 union select
'2007-1-23', 400 union select
'2007-1-25', 500 union select
'2007-1-25', 300 union select
'2007-1-25', 200 union select
'2007-1-25', 100 union select
'2007-1-25', 400 create table tb(时间2 datetime,数量2 int)
insert tb select
'2007-1-20', 100 union select
'2007-1-27', 200 union select
'2007-1-25', 500 select isnull(m.时间1,n.时间2) 时间 , isnull(m.数量1,0) 数量1,isnull(n.数量2,0) 数量2 from
(select 时间1,数量1,px = (select count(*) from ta where 时间1 = t.时间1 and 数量1 < t.数量1) + 1 from ta t) m
full join
(select 时间2,数量2,px = (select count(*) from tb where 时间2 = t.时间2 and 数量2 < t.数量2) + 1 from tb t) n
on m.时间1 = n.时间2 and m.px = n.pxdrop table ta,tb/*
时间 数量1 数量2
------------------------------------------------------ ----------- -----------
2007-01-20 00:00:00.000 200 100
2007-01-22 00:00:00.000 300 0
2007-01-23 00:00:00.000 400 0
2007-01-25 00:00:00.000 100 500
2007-01-25 00:00:00.000 200 0
2007-01-25 00:00:00.000 300 0
2007-01-25 00:00:00.000 400 0
2007-01-25 00:00:00.000 500 0
2007-01-27 00:00:00.000 0 200(所影响的行数为 9 行)
*/
不能为了做题而做题吧
full join 当然比较慢
晕,这也出来了呀
呵呵,改一下‘
declare @ta table(时间1 datetime,数量1 int)
insert @ta select
'2007-1-20',200 union select
'2007-1-22', 300 union select
'2007-1-23', 400 union select
'2007-1-25', 500 union select
'2007-1-25', 300 union select
'2007-1-25', 200 union select
'2007-1-25', 100 union select
'2007-1-25', 400 declare @tb table(时间2 datetime,数量2 int)
insert @tb select
'2007-1-20', 100 union select
'2007-1-27', 200 union select
'2007-1-25', 500
select isnull(a.时间1,b.时间2) 时间,
数量1 = isnull(a.数量1,0),
isnull(数量2,0) 数量2
from @ta a full join @tb b
on a.时间1 = b.时间2 and a.数量1 >= b.数量2
order by 时间,数量1 desc
/*时间 数量1 数量2
------------------------------------------------------ ----------- -----------
2007-01-20 00:00:00.000 200 100
2007-01-22 00:00:00.000 300 0
2007-01-23 00:00:00.000 400 0
2007-01-25 00:00:00.000 500 500
2007-01-25 00:00:00.000 400 0
2007-01-25 00:00:00.000 300 0
2007-01-25 00:00:00.000 200 0
2007-01-25 00:00:00.000 100 0
2007-01-27 00:00:00.000 0 200(所影响的行数为 9 行)
*/
人们关注的是每日各个类别的统计(还是按收支两条线走)。这样一来,应该是两个表分别统计。例如:
select timepoint, type, amount=sum(amount)
from (
-- 分别统计入出
select timepoint, type='A', amount=sum(amount) from table1 group by timepoint union
select timepoint, type='B', amount=sum(amount) from table2 group by timepoint
) as a
group by timepoint, type
这样统计出来的结果也没必要在SQL中做行列转换,(如果有这方面的需求找老龟:p),在前台应用中展现也好,在后台继续进行统计处理也好...