sql的分组统计与group by 日期的处理



近几天补oracle的sql知识,这块记录下sql的分组统计

1.简单的分组统计

创建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;

查询结果:

 


2.group by 日期的处理:

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')


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u010652906/article/details/47077263
文章标签: sql 分组统计
个人分类: ajax
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭