sql 分组讨论，二级分组（非2个字段分组），使用 窗口函数和普通分组实现

1. 二级分组需求

• 先按照一个字段分组，在按照 第二个字段分组。
• 之后，如果 这个 二级分组中的数据，是 > 1条的。就筛选出来。

比如：

• 先按照 站点分组，再按照 设备分组，

• 即：如果站点上配置了2个设备。就筛选出来。

  • 然后：这2个设备 都必须是屏幕

• 查出配置了2个设备的站点

SELECTe2.station,e2.device 
FROMesb_config e2 
GROUP BYe2.station 
HAVINGcount( 1 ) > 1

• 要求 这两个设备都是屏幕，参考SQL和思路1

错误写法

	SELECTe2.station,e2.device FROMesb_config e2GROUP BY e2.station,e2.device HAVING count(*) > 1 -- 这样是错误的，这样是根据2个字段 分组。结果不会存在 > 1的情况

GROUP BY e2.station HAVING count(e2.device) > 1
-- 这样写无异议 等于 HAVING count(e2.station) > 1，也是错误的GROUP BY e2.station
-- 核心是：这样查询后，会随机带出 一个device。
-- 所以哪怕 外层在套一个分组（因为经过一层后，已经选出了一个device了），也是错的。

问题从头整理

表结构和数据

CREATE TABLE `user` (`id` int NOT NULL,`username` varchar(22) DEFAULT NULL,`info` varchar(22) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;1	zhangsan	1
2	lisi	22
3	lisi	33-- 我们要查出lisi
-- 先按照姓名分组，在按照 info 分组。
-- info中的 数据>1，就查询出来

collate
英
/kəˈleɪt/
vt.
核对，校对；校勘

按照username分组

SELECT * 
FROM `user`
GROUP BY username-- 按照username分组后，lisi会随机选一条（第一条）
1	zhangsan	1
2	lisi	22

加入扰乱数据

INSERT INTO `user`(`id`, `username`, `info`) VALUES (4, 'zhangsan', '1');
-- 此时数据的数据为
1	zhangsan	1
2	lisi	22
3	lisi	33
4	zhangsan	1

• 加条件

SELECT * 
FROM `user`
GROUP BY username
HAVING count(username) > 1-- 结果为：此时zhangsan是info相同的，不应该出现。count(1)，或 count(其他值) 结果都一样
1	zhangsan	1
2	lisi	22

两个字段分组 加条件

• 分3组

-- 此时数据的数据为
1	zhangsan	1
2	lisi	22
3	lisi	33
4	zhangsan	1SELECT * 
FROM `user`
GROUP BY username,info-- 根据 两个字段分组，后变成3组
1	zhangsan	1
2	lisi	22
3	lisi	33

• 此时分组中的数据 > 1的为 zhangsan

SELECT * 
FROM `user`
GROUP BY username,info
HAVING count(1) > 1-- 结果
1	zhangsan	1

2. 业务

真实的问题

• 业务要求了，必须是 一个站点绑定两个设备都是屏幕，才查询出来。

• 无用的SQL保存，验证这个真实的设备是不是 屏幕

SELECT* 
FROMdevice d,device_type dt 
WHEREd.deviceType = dt.id AND d.id = 'e696cfeeb4568ccfcda0ae6787388760' AND dt.attribute = '屏幕'

先 where，在 having

• where语句的执行顺序先于group by，group by语句的执行顺序先于having
  • having 子句中的每一个元素也必须出现在select列表中，
  • having语句可以使用聚合函数。

SQL和实现思路1 窗口函数

SELECT* 
FROM(SELECTe2.station,ROW_NUMBER() OVER ( PARTITION BY e2.station ) row_num FROMesb_config e2,device d,device_type dt WHEREd.deviceType = dt.id AND dt.attribute = '屏幕' AND e2.device = d.id ) t1 
WHEREt1.row_num > 1-- 窗口函数 再次赋值
SELECT* 
FROM(SELECTe2.station,ROW_NUMBER() OVER ( PARTITION BY e2.station ) row_num FROMesb_config e2) t1
WHEREt1.row_num > 1

SQL和实现思路2 分组

• 查出 站点配置了 多个设备的 站点

SELECTe2.station,e2.device 
FROMesb_config e2 
GROUP BYe2.station 
HAVINGcount( 1 ) > 1

• 这多个设备 必须是屏幕，才能查出来。
  • 对站点进行了分组：

SELECTe2.station,e2.device 
FROMesb_config e2,device d,device_type dt 
WHEREd.deviceType = dt.id AND dt.attribute = '屏幕' AND e2.device = d.id
-- 上面SQL 查出了配置表中，所有为屏幕的 设备和站点
-- 此时一个站点 如果配置了 多个屏幕，这个站点就会展示出多条-- 然后在筛选一下，站点>1 的
GROUP BYe2.station 
HAVINGcount( 1 ) > 1

扩展怎么显示 一个站点，显示2次呢

• 思路1再关联原表

• 使用这个逻辑，有一个问题。怎么显示 一个站点，显示2次呢（设备不同的时候）

  • 很简单，只需要在 外层关联一个 表即可（因为这个表 本来就是这种逻辑，一个站点，显示2次）

SELECT * from esb_config e1, (SELECTe2.station FROMesb_config e2,device d,device_type dt WHEREd.deviceType = dt.id AND dt.attribute = '屏幕' AND e2.device = d.id GROUP BYe2.station HAVINGcount( 1 ) > 1
) t1
WHERE e1.station = t1.station-- 核心是这样的结果
SELECT * from esb_config e1, (SELECTe2.station -- 这里,e2.device 取了没用，只是会筛选第一个 FROMesb_config e2GROUP BYe2.station HAVINGcount( e2.station  ) > 1) t1
WHERE e1.station = t1.station-- 那这个结果加上限制对不对呢？不对的，为啥不对，这里不懂。
-- AND dt.attribute = '屏幕' 

SQL和思路3 错误的

• 把一级分组，查询出来

• 把二级分组，查询出来

• 如果 一级分组 和 二级分组，关联上就展示

• 这样 查出的数据 只查出了一条（也是上面的 数据之一）

SELECT* 
FROM( SELECT * FROM (SELECTe2.station,e2.deviceFROMesb_config e2,device d,device_type dt WHEREe2.device = d.id AND d.deviceType = dt.id AND dt.attribute = '屏幕') t2GROUP BY t2.station HAVING count(t2.station) > 1 ) t1,(SELECT * FROM (SELECTe2.station,e2.device FROMesb_config e2,device d,device_type dt WHEREe2.device = d.id AND d.deviceType = dt.id AND dt.attribute = '屏幕') t2GROUP BY t2.device HAVING count(t2.device) > 1 ) t3WHEREt1.station = t3.station and t1.device = t3.device