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

﻿﻿

1.简单的分组统计

  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,'男');

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

﻿﻿

#### mysql按日期group by分组查询

2017-11-30 22:46:13

#### 在mysql中使用group by和order by取每个分组中日期最大一行数据

2017-11-08 20:15:55

#### mysql按日期分组（group by）查询统计的时候，没有数据补0的解决办法。

2016-03-14 16:53:49

#### GROUP BY 条件查询最新时间记录

2017-10-25 12:35:10

#### sql分别用日期、月、年 分组 group by 分组，datepart函数

2013-12-26 15:31:48

#### ORACLE用GROUP BY 来分组日期字段 按月分组

2016-03-11 13:52:06

#### 数据库排名sql,group by 分组查询按照时间最大值

2017-07-04 09:33:05

#### group by分组后获得每组中时间最大的那条记录

2017-11-22 16:53:09

#### sql日期按月份和年份分组查询

2016-03-11 13:53:08

#### Sql server Group by 统计数量 order by时间

2016-07-14 18:09:23