sql的分组统计与groupby⽇期的处理近⼏天补oracle的sql知识，这块记录下sql的分组统计
1.简单的分组统计
groupby分组
创建STUDENT表：
CREATE TABLE STUDENT(
"NAME" VARCHAR2(10 BYTE),
"MAJOR" VARCHAR2(10 BYTE),
"SCORE" NUMBER(5,2),
"SEX" VARCHAR2(3 BYTE)
);
录⼊数据：
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (1,'邱君','语⽂',70,'⼥');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (2,'⼩狗','语⽂',76,'男');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (3,'混蛋','语⽂',60,'男');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (4,'邱君','数学',81,'⼥');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (5,'混蛋','数学',90,'男');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (6,'⼩狗','数学',77,'男');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (7,'邱君','外语',98,'⼥');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (8,'⼩狗','外语',71,'男');
Insert into STUDENT (ID,NAME,MAJOR,SCORE,SEX) values (9,'混蛋','外语',88,'男');
查询语句（查出有两科分数⼤于80，并且平均平均成绩⼤于80的同学）：
SELECT name,
SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 ,
SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 ,
SUM(CASE WHEN major = '语⽂' THEN score ELSE 0 END)语⽂,
AVG(score)
FROM
student
GROUP BY NAME
HAVING
AVG(score) > 80
AND
SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2;
查询结果：
select to_char(dt,'yyyy-mm-dd'),
SUM(case when re='胜' then 1 else 0 end)胜,
sum(case when re='负' then 1 else 0 end)负
from
test_tab
group by to_char(dt,'yyyy-mm-dd')
;
to_char(date,'yyyy-mm-dd')处理⽇期，之后to_char(date,'yyyy-mm-dd')

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。