已知表:fruit_log
indate fruit amount 一月 苹果 15
一月 苹果 35
一月 香蕉 35
一月 香蕉 5
二月 菠萝 5
二月 橘子 50
当
select count(*),indate from fruit_log group by indate 显示:
一月 4
二月 2当
select count(*),indate from fruit_log where fruit='苹果' group by indate
显示一月 2问题:
能不能用一句sql,实现:时间 总数 苹果数
一月 4 2
二月 2 0
indate fruit amount 一月 苹果 15
一月 苹果 35
一月 香蕉 35
一月 香蕉 5
二月 菠萝 5
二月 橘子 50
当
select count(*),indate from fruit_log group by indate 显示:
一月 4
二月 2当
select count(*),indate from fruit_log where fruit='苹果' group by indate
显示一月 2问题:
能不能用一句sql,实现:时间 总数 苹果数
一月 4 2
二月 2 0
insert into fruit_log values('一月','苹果',35);
insert into fruit_log values('一月','香蕉',35);
insert into fruit_log values('一月','香蕉',5);
insert into fruit_log values('二月','菠萝',5);
insert into fruit_log values('二月','橘子',50);select t1.indate,总数,nvl(苹果数,0) from
(select indate,count(fruit) 总数 from fruit_log group by indate) t1,
(select count(*) 苹果数,indate from fruit_log where fruit='苹果' group by indate) t2
where t1.indate=t2.indate(+);
CREATE TABLE fruit_log(indate VARCHAR2(10),fruit VARCHAR2(10),amount INTEGER);insert into fruit_log values('一月','苹果',15);
insert into fruit_log values('一月','苹果',35);
insert into fruit_log values('一月','香蕉',35);
insert into fruit_log values('一月','香蕉',5);
insert into fruit_log values('二月','菠萝',5);
insert into fruit_log values('二月','橘子',50);select count(*),indate,sum(decode(fruit,'苹果',1,0)) from fruit_log group by indate
decode就可以了
FROM (
SELECT INDATE, COUNT(INDATE) OVER (PARTITION BY INDATE) AS TOTAL,
SUM(DECODE(FRUIT, '苹果', 1, 0)) OVER (PARTITION BY INDATE, FRUIT) AS APPLE,
ROW_NUMBER() OVER (PARTITION BY INDATE ORDER BY DECODE(FRUIT, '苹果', 1, 0) DESC) AS SEQ
FROM FRUIT_LOG
) A
WHERE A.SEQ = 1
ORDER BY INDATE DESC
/