表A:
字段:DDATE,VAL1,VAL2
表B:
字段:DDATE,VAL1,VAL2有日期:2010-01-08请用SQL语句得出
表c:
字段: (2010-01-08之前所有VAL1之和)VAL1,(2010-01-08之前所有VAL2之和)VAL2,,,(之后)VAL1, (之后)VAL2
表C中的字段之和为表A与表B 的相同字段在2010-01-08之前与之后的和
字段:DDATE,VAL1,VAL2
表B:
字段:DDATE,VAL1,VAL2有日期:2010-01-08请用SQL语句得出
表c:
字段: (2010-01-08之前所有VAL1之和)VAL1,(2010-01-08之前所有VAL2之和)VAL2,,,(之后)VAL1, (之后)VAL2
表C中的字段之和为表A与表B 的相同字段在2010-01-08之前与之后的和
表A:
字段:DDATE,VAL1,VAL2
表B:
字段:DDATE,VAL1,VAL2 有日期:2010-01-08 请用SQL语句得出 表c:
字段: (2010-01-08之前所有VAL1之和)VAL1-(2010-01-08之前所有VAL2之和)VAL2),
(2010-01-08之前所有VAL1之和)VAL1,
(2010-01-08之前所有VAL2之和)VAL2,,,
(之后)VAL1,
(之后)VAL2
表C中的字段之和为表A与表B 的相同字段在2010-01-08之前与之后的和
UNION ALL
SELECT * FROM BSELECT CONVERT(VARCHAR(10),DDATE,120)AS DDATE,
(SELECT SUM(VAL1) FROM #T
WHERE CONVERT(VARCHAR(10),DDATE,120)<='2010-01-08')AS VAL1,
(SELECT SUM(VAL2) FROM #T
WHERE CONVERT(VARCHAR(10),DDATE,120)>'2010-01-08')AS VAL2
FROM #T?
convert(varchar(10),ddate,120) as ddate,
(select sum(val1) from tb where convert(varchar(10),ddate,120)<='2010-01-08') as val1,
(select sum(val1) from tb where convert(varchar(10),ddate,120)>'2010-01-08') as val1
from
tb
UNION ALL
SELECT * FROM BSELECT CONVERT(VARCHAR(10),DDATE,120)AS DDATE,
SUM(CASE WHEN CONVERT(VARCHAR(10),DDATE,120)<='2010-01-08'
THEN VAL1 ELSE 0 END )-
SUM(CASE WHEN CONVERT(VARCHAR(10),DDATE,120)<='2010-01-08'
THEN VAL2 ELSE 0 END )AS 差,
SUM(CASE WHEN CONVERT(VARCHAR(10),DDATE,120)<='2010-01-08'
THEN VAL1 ELSE 0 END ) AS VAL1之前,
SUM(CASE WHEN CONVERT(VARCHAR(10),DDATE,120)<='2010-01-08'
THEN VAL2 ELSE 0 END ) AS VAL2 之前,SUM(CASE WHEN CONVERT(VARCHAR(10),DDATE,120)>'2010-01-08'
THEN VAL1 ELSE 0 END ) AS VAL1之后,
SUM(CASE WHEN CONVERT(VARCHAR(10),DDATE,120)>'2010-01-08'
THEN VAL2 ELSE 0 END ) AS VAL2之后
FROM #TGROUP BY CONVERT(VARCHAR(10),DDATE,120)
sum(case when convert(varchar(10),DDATE,120) < '2010-01-08' then VAL1 else 0 end) as VAL1,
sum(case when convert(varchar(10),DDATE,120) < '2010-01-08' then VAL2 else 0 end) as VAL2,
sum(case when convert(varchar(10),DDATE,120) > '2010-01-08' then VAL1 else 0 end) as VAL11,
sum(case when convert(varchar(10),DDATE,120) > '2010-01-08' then VAL2 else 0 end) as VAL22
from
(
select DDATE,VAL1,VAL2 from 表A
union all
select DDATE,VAL1,VAL2 from 表B
) t
insert into
c
select
convert(varchar(10),ddate,120) as ddate,
(select sum(val1) from tb where convert(varchar(10),ddate,120)<='2010-01-08') as val1,
(select sum(val1) from tb where convert(varchar(10),ddate,120)>'2010-01-08') as val1
from
tb
sum(case when convert(varchar(10),DDATE,120) < '2010-01-08' then VAL1 else 0 end) as VAL1,
sum(case when convert(varchar(10),DDATE,120) < '2010-01-08' then VAL2 else 0 end) as VAL2,
sum(case when convert(varchar(10),DDATE,120) > '2010-01-08' then VAL1 else 0 end) as VAL11,
sum(case when convert(varchar(10),DDATE,120) > '2010-01-08' then VAL2 else 0 end) as VAL22
from
(
select DDATE,VAL1,VAL2 from 表A
union all
select DDATE,VAL1,VAL2 from 表B
) t
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then VAL1 else 0 end) as V11,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)<'2010-01-08' then VAL1 else 0 end) as V12,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then VAL2 else 0 end) as V21,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)<'2010-01-08' then VAL2 else 0 end) as V22
Into tbl3
From tbl1 Inner Join tbl2 On tbl1.DDATE=tbl2.DDATE
sum(case when convert(varchar(10),DDATE,120) < '2010-01-08' then VAL1-VAL2 else 0 end) as 差额,
sum(case when convert(varchar(10),DDATE,120) < '2010-01-08' then VAL1 else 0 end) as VAL1,
sum(case when convert(varchar(10),DDATE,120) < '2010-01-08' then VAL2 else 0 end) as VAL2,
sum(case when convert(varchar(10),DDATE,120) > '2010-01-08' then VAL1 else 0 end) as VAL11,
sum(case when convert(varchar(10),DDATE,120) > '2010-01-08' then VAL2 else 0 end) as VAL22
from
(
select DDATE,VAL1,VAL2 from 表A
union all
select DDATE,VAL1,VAL2 from 表B
) t
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then
ISNULL(tbl1.VAL1,0)+ISNULL(tbl2.VAL1,0)-ISNULL(tbl1.VAL2,0)-ISNULL(tbl2.VAL2,0) else 0 end) as VAL,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then ISNULL(tbl1.VAL1,0)+ISNULL(tbl2.VAL1,0) else 0 end) as VAL1,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then ISNULL(tbl1.VAL2,0)+ISNULL(tbl2.VAL2,0) else 0 end) as VAL2,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)<'2010-01-08' then ISNULL(tbl1.VAL1,0)+ISNULL(tbl2.VAL1,0) else 0 end) as VAL21,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)<'2010-01-08' then ISNULL(tbl1.VAL2,0)+ISNULL(tbl2.VAL2,0) else 0 end) as VAL22
Into tbl3
From tbl1 Inner Join tbl2 On tbl1.DDATE=tbl2.DDATE
Create Table tbl2 ( DDATE datetime,VAL1 int,VAL2 int)
Insert Into tbl1 values( '2010-01-07',10,20 )
Insert Into tbl1 values( '2010-01-08',30,40 )
Insert Into tbl1 values( '2010-01-09',50,60 )
Insert Into tbl2 values( '2010-01-07',70,80 )
Insert Into tbl2 values( '2010-01-08',90,100 )
Insert Into tbl2 values( '2010-01-09',110,120 )--Insert into tbl3
Select ISNULL(tbl1.DDATE ,tbl2.DDATE) as DDATE,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then
ISNULL(tbl1.VAL1,0)+ISNULL(tbl2.VAL1,0)-ISNULL(tbl1.VAL2,0)-ISNULL(tbl2.VAL2,0) else 0 end) as VAL,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then ISNULL(tbl1.VAL1,0)+ISNULL(tbl2.VAL1,0) else 0 end) as VAL1,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)>'2010-01-08' then ISNULL(tbl1.VAL2,0)+ISNULL(tbl2.VAL2,0) else 0 end) as VAL2,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)<'2010-01-08' then ISNULL(tbl1.VAL1,0)+ISNULL(tbl2.VAL1,0) else 0 end) as VAL21,
sum(case when ISNULL(tbl1.DDATE ,tbl2.DDATE)<'2010-01-08' then ISNULL(tbl1.VAL2,0)+ISNULL(tbl2.VAL2,0) else 0 end) as VAL22
--Into tbl3
From tbl1 Inner Join tbl2 On tbl1.DDATE=tbl2.DDATE
Group By ISNULL(tbl1.DDATE ,tbl2.DDATE)drop table tbl1;drop table tbl2