关于oracle求和的sql怎么写？

Question

select sampling_time as time,(max(A)-min(A)) as total from data where id='1' and sampling_time >= '2013-08-24 00:00:00' and sampling_time < '2016-08-25 00:00:00' group by sampling_time order by sampling_time;
TIME TOTAL
------- ----------
2016-01 80
select sampling_time as time,(max(A)-min(A)) as total from data where id='2' and sampling_time >= '2013-08-24 00:00:00' and sampling_time < '2016-08-25 00:00:00' group by sampling_time order by sampling_time;
TIME TOTAL
------- ----------
2016-01 90
我想在一条sql语句中求id为1和2的值，即结果为:
TIME TOTAL
------- ----------
2016-01 170

Answer 1

SELECT TIME,SUM(total)
FROM
(
SELECT sampling_time AS TIME, (MAX(A) - MIN(A)) AS total
FROM data
WHERE id = '1'
AND sampling_time >= '2013-08-24 00:00:00'
AND sampling_time < '2016-08-25 00:00:00'
GROUP BY sampling_time
ORDER BY sampling_time;
UNION
SELECT sampling_time AS TIME, (MAX(A) - MIN(A)) AS total
FROM data
WHERE id = '2'
AND sampling_time >= '2013-08-24 00:00:00'
AND sampling_time < '2016-08-25 00:00:00'
GROUP BY sampling_time
ORDER BY sampling_time
) GROUP BY TIME

Answer 2

‍

select time, sum(total) total
from (
    select 
        sampling_time as time,
        (max(A)-min(A)) as total 
    from data 
    where id='1' 
        and sampling_time >= '2013-08-24 00:00:00' 
        and sampling_time < '2016-08-25 00:00:00' 
    group by sampling_time
    union all
    select 
        sampling_time as time,
        (max(A)-min(A)) as total 
    from data 
    where id='2' 
        and sampling_time >= '2013-08-24 00:00:00' 
        and sampling_time < '2016-08-25 00:00:00' 
    group by sampling_time 
) a
group by time
order by time;

追问

我觉得这个写的有点复杂，实际上两个select语句中只有id是不一样的，其他的条件是一样的，是否可以简化一下?

追答

‍

select time, sum(total) total
from (
    select 
        sampling_time as time,
        id,
        (max(A)-min(A)) as total 
    from data 
    where id in ('1','2')
        and sampling_time >= '2013-08-24 00:00:00' 
        and sampling_time < '2016-08-25 00:00:00' 
    group by sampling_time, id
) a
group by time
order by time;