表a arrivalDate , accounta
表b departDate ,accountb
参数: dBegin,dEnd
要一个sql 语句 结果为 日期在 dBegin,dEnd之间的 日期 sum(accounta) sum(accountb)
group by 日期
表b departDate ,accountb
参数: dBegin,dEnd
要一个sql 语句 结果为 日期在 dBegin,dEnd之间的 日期 sum(accounta) sum(accountb)
group by 日期
表a
2008-9-1 1
2008-9-1 2
2008-9-2 10
表b
2008-9-1 3
2008-9-1 4
2008-9-2 20
dbegin = 2008-9-1 dend = 2008-9-2
我想得到的结果是
2008-9-1 3 7
2008-9-2 10 30
不知道我表述的清楚么?
select arrivalDate,sum(accounta) as accounta,sum(accountb) as accountb
from (
select arrivalDate,sum(accounta) as accounta,0 as accountb union all
select departDate ,0 as accounta,sum(accountb) as accountb
) a
where arrivalDate>='2008/09/01' and arrivalDate<='2008/09/02'
group by arrivalDate
select arrivalDate,sum(accounta) as accounta,sum(accountb) as accountb
from (
select arrivalDate,accounta,0 as accountb union all
select departDate ,0 as accounta,accountb
) a
where arrivalDate>='2008/09/01' and arrivalDate<='2008/09/02'
group by arrivalDate
(
time1 datetime,
count1 int
)
create table b
(
time1 datetime,
count1 int
)insert into a
select '2008-9-1', 1 union all
select '2008-9-1', 2 union all
select '2008-9-2', 10 insert into b
select '2008-9-1', 3 union all
select '2008-9-1', 4 union all
select '2008-9-2', 20 select time1,sumcounta = sum((case tablename when 'a' then sumcount else null end)),
sumcountb = sum((case tablename when 'b' then sumcount else null end))
from
(select time1,sum(count1) as sumcount , tablename = 'a' from a
group by time1
union
select time1,sum(count1)as sumcount, tablename = 'b' from b
group by time1) t
group by time1
create table a
(
time1 datetime,
count1 int
)
create table b
(
time1 datetime,
count1 int
)insert into a
select '2008-9-1', 1 union all
select '2008-9-1', 2 union all
select '2008-9-2', 10 insert into b
select '2008-9-1', 3 union all
select '2008-9-1', 4 union all
select '2008-9-2', 20 select time1,sumcounta = sum((case tablename when 'a' then sumcount else null end)),
sumcountb = sum((case tablename when 'b' then sumcount else null end))
from
(select time1,sum(count1) as sumcount , tablename = 'a' from a
group by time1
union
select time1,sum(count1)as sumcount, tablename = 'b' from b
group by time1) t
group by time1
(
time1 datetime,
count1 int
)
create table b
(
time1 datetime,
count1 int
)insert into a
select '2008-9-1', 1 union all
select '2008-9-1', 2 union all
select '2008-9-2', 10 insert into b
select '2008-9-1', 3 union all
select '2008-9-1', 4 union all
select '2008-9-2', 20 select time1,sumcounta = sum((case tablename when 'a' then sumcount else null end)),
sumcountb = sum((case tablename when 'b' then sumcount else null end))
from
(select time1,sum(count1) as sumcount , tablename = 'a' from a
group by time1
union
select time1,sum(count1)as sumcount, tablename = 'b' from b
group by time1) t
group by time1