现有一张表,表名为:a
我的SQL:
select day_id,name,sum(data) from a
where day_id between '20080617' and '20080618'
group by name,day_id
order by name
结果如下:
day_id name data
20080617 w 277
20080618 w 280
20080617 y 145
20080618 y 151我想得到如下结果SQL语句怎么写:
day_id name data day_id data
20080617 w 277 20080618 280
20080617 y 145 20080618 151
我的SQL:
select day_id,name,sum(data) from a
where day_id between '20080617' and '20080618'
group by name,day_id
order by name
结果如下:
day_id name data
20080617 w 277
20080618 w 280
20080617 y 145
20080618 y 151我想得到如下结果SQL语句怎么写:
day_id name data day_id data
20080617 w 277 20080618 280
20080617 y 145 20080618 151
from ((select c.day_id,
c.name,
c.date1,
row_number() over(PARTITION BY TRUNC(c.name) ORDER BY c.day_id DESC) rn
from (select day_id, name, sum(data) as date1,
from where day_id between '20080617' and '20080618'
group by name, day_id
order by name) c) a,
(select day_id, name, sum(data)
from a
where day_id between '20080617' and '20080618'
group by name, day_id
order by name) b)
where a.name = b.name
and a.rn = 1随便写写,你试试
,SUM(decode(day_id, '20080617', 20080617, 0)) day1
,SUM(decode(day_id, '20080617', data, 0)) data1
,SUM(decode(day_id, '20080618', 20080618, 0)) day2
,SUM(decode(day_id, '20080618', data, 0)) data2
FROM (SELECT day_id, NAME, SUM(data) data
FROM a
WHERE day_id BETWEEN '20080617' AND '20080618'
GROUP BY NAME, day_id
ORDER BY NAME)
GROUP BY NAME