sql

1.groupy

select * 
from sc tsc 
group by tsc.`s#`;

+----+----+-------+
| s# | c# | score |
+----+----+-------+
| s1 | c1 |    60 |
| s2 | c3 |    70 |
| s3 | c5 |   100 |
| s4 | c1 |    74 |
| s5 | c2 |    86 |
| s6 | c1 |    77 |
+----+----+-------+

select count(*),tc.`t#`
from c tc
GROUP BY tc.`t#`;

+----------+------+
| count(*) | t#   |
+----------+------+
|        2 | t1   |
|        2 | t2   |
|        2 | t3   |
|        1 | t4   |
|        1 | t5   |
+----------+------+

由此可知groupby就是根据他后面的参数，将每个参数的不同值只显示第一条记录；
           所以他应当结合聚合函数使用，而不是没有聚合函数就使用

2.聚合函数

select tsc.`c#`,tc.cname
	      ,count(tsc.`c#`)
from sc tsc,c tc
where tsc.`c#`=tc.`c#`;

+----+--------+-----------------+
| c# | cname  | count(tsc.`c#`) |
+----+--------+-----------------+
| c1 | 语文   |              14 |
+----+--------+-----------------+

select tsc.`c#`,tsc.score,max(tsc.`s#`)
from sc tsc 
;
-- 聚合函数单独求出来，和第一条记录放在一起，并不是聚合函数（如max）所在的同一条记录

同样，只使用聚合函数的时候，他count()只显示一条记录，若count()和其他字段是一起select的，也是只能显示一条记录，聚合函数单独算出来，然后和第一条记录的其他字段放在一起（切记切记尤其当时max时不是选择和聚合函数所在的同一条记录），而不是每条其他字段对应记录都带上count值；
           所以聚合函数和其他字段一起select的话，应当搭配groudby（咦，我发现在每组里，如果select 聚合函数,其他字段 的话，也是只显示一条记录和聚合函数值放在一起）；如果是只查 select  聚合函数的话当然是没事了

3.

Group By中Select指定的字段限制:在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。提到group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中，常见的聚合函数如下表：count(列名)、avg(列名)、sum(列名)、max(列名)、min(列名)。

select *
from sc sc1
group by sc1.`s#`;

select max(sc1.score)
from sc sc1
group by sc1.`s#`;

            group by的意思是对数据分组，然后每组最后只返回第一条（一般是用来和聚合函数搭配的，目的是求一组的计数啊求和啊），而且select出的字段是有要求的。

分组的时候取的是每组中的第一条，一般可能是按数据库中的顺序，那如果我想每组取特定的最大或者最小的记录怎么办？

可以先使用子查询排好序后作为新表被查。如果遇到多个表，就把相应的表也先子查询排好序后做为新表。（参考https://blog.csdn.net/u012660464/article/details/78605078/）

select sc2.score
from (select * from sc sc1 order by sc1.score desc ) sc2
group by sc2.`s#`

select a.title, t.name, t.price from a 
inner join (select * from b order by id, price ) t on t.id = a.b_id 
group by a.b_id;

或者用函数取表最值的id，然后再表中查

SELECT distinct memo,name from xytest.student
where id in (select  min(id) from xytest.student group by memo)

我们可以将Group By操作想象成如下的一个过程，首先系统根据SELECT 语句得到一个结果集，如最开始的那个水果、出产国家、单价的一个详细表。然后根据分组字段，将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在于Group By语句后面作为分组依据的字段就有可能出现多个值，但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数

然而mysql使用group by  select中包含不服规则的列时 奇怪的是可以显示不报错，只显示每组中的第一条。但是我们不应该这样用。

对于group by在mysql中的使用和Oracle的差异性很大，准确的说不光和Oracle和别的数据库差异性一样，这些有点不太遵循标准SQL。我们知道常规的 sql，对于group by来说一定要结合聚合函数，而且选择的字段除了聚合函数外，还必须在group by中出现，否则报错，但是在mysql中扩展了这样的功能
首先对于不加聚合函数的sql来说，它的功能结合了limit来得出结果，仔细想想的时候有点Oracle分析函数的味道，limit的时候得到的并不是 最大最小的值，而是某一下group by结果集中第一行，也就是刚才说的相当与先group by， 然后在每个group by下面进行limit 1。
其次，刚才还说了常规的group by结合聚合函数的时候，选择的字段除了聚合函数外，必须在group by中存在，但是在mysql中不是这样了，它具有隐含字段的功能，例如：


(root:im-mysql:16:34:45)[test]> select a,b,c,count(*) from test1 group by a,b;
+------+------+------+----------+
| a   | b    | c    | count(*) |
+------+------+------+----------+
|    1 |    1 |    1 |        3 |
|    1 |    2 |    5 |        3 |
+------+------+------+----------+

对于没有选择的字段，上面是c，c的值还是和上面说到的一样，是根据得到的结果集然后根据每个group by 进行limit 1得到的结果。

经查找资料,分析,与MySql数据库中sql_mode是否含有only_full_group_by有关.如果无only_full_group_by,则groupby不受上面的认知限制,否则,必须按上面的共识操作.

在 MySQL5.7.5及以上版本实现了函数依赖检测。如果ONLY_FULL_GROUP_BY模式开启（默认是开启的），则MYSQL会拒绝查询，以上查询就会出错：“[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘uqi_union.r.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”；在5.7.5版本之前，MYSQL未开启检测，并且ONLY_FULL_GROUP_BY默认是关闭的，所以查询不会出错。

参考：https://blog.csdn.net/wxwzy738/article/details/20636563

https://blog.csdn.net/ljz1315/article/details/84890254

https://www.cnblogs.com/fireporsche/p/8618691.html?tdsourcetag=s_pcqq_aiomsg#

https://blog.csdn.net/hu_zhiting/article/details/80752747

select tsc.`s#`,max(tsc.`c#`),tsc.score
from sc tsc 
group by tsc.`s#`
;
--查出的score是每组的第一条，而并不是和max的在同一条记录。也就是说，聚合函数的单独求出来，和分组第一条的非法字段放在一起。和
--只有聚合函数没group by的情况类似

 

https://www.cnblogs.com/jpfss/p/7390399.html

https://blog.csdn.net/dongapple/article/details/80923040

4.

一般我们会在group by下面的having中写聚合函数的比较问题，where后不可以直接使用聚合函数，但where中好像可以用一种方法转化达到这样的效果

select *
from s s1
where (
	(
	select count(*) 
	from sc sc1
	where sc1.`s#`=s1.`s#`
	)
	>1
);

但是下面这种写法不行：

select *
from s s1
where (
-- 	(
	select count(*) 
	from sc sc1
	where sc1.`s#`=s1.`s#`
-- 	)
	>1
);

5.exists/not exists 用来测试一个集合 是否为空/非空。后面接的需是集合，就不能像where一样随意了，随便接表达式。

比如这样是报错的：

select *
from s s1
where not exists(
	(
	select count(*) 
	from sc sc1
	where sc1.`s#`=s1.`s#`
	)
	>5
);

6.取最大/小值

不仅要获得最大的付款金额，还要获得其他付款信息，如客户编号，支票号码和付款日期：

写法一：
SELECT 
    *
FROM
    payments
WHERE
    amount = (
 SELECT 
            MAX(amount)
        FROM
            payments);


错误写法：
select ...,max(amout)
from payments 
-- 这样不是取到max（amout）所在的同一条记录

不使用MAX函数的另一种方法是使用order by子句以降序对结果集进行排序，并使用limit子句获取第一行 

SELECT 
    *
FROM
    payments
ORDER BY amount DESC
LIMIT 1;

如果您没有在amount列上创建索引，则第二个查询执行速度更快，因为它会检查payments表中的所有行，而第一个查询会检查payments表中的所有行两次，一次是在子查询中，另一次在外部查询中。 但是，如果amount列被索引，则第一个查询将执行得更快。

参考：MySQL max()函数 

---

注意：

1.select 的from 中如果有多个表，是一定会做笛卡尔积的，即使where中不做等值连接，查询结果也一定是笛卡尔积的结果。