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

这篇具有很好参考价值的文章主要介绍了sql 分组讨论，二级分组（非2个字段分组），使用窗口函数和普通分组实现。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1. 二级分组需求

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

比如：

先按照站点分组，再按照设备分组，
即：如果站点上配置了2个设备。就筛选出来。
- 然后：这2个设备都必须是屏幕
查出配置了2个设备的站点

SELECT
	e2.station,
	e2.device 
FROM
	esb_config e2 
GROUP BY
	e2.station 
HAVING
	count( 1 ) > 1

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

错误写法

	SELECT
		e2.station,
		e2.device 
	FROM
		esb_config e2
		GROUP 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	1

SELECT * 
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
	* 
FROM
	device d,
	device_type dt 
WHERE
	d.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
	(
	SELECT
		e2.station,
		ROW_NUMBER() OVER ( PARTITION BY e2.station ) row_num 
	FROM
		esb_config e2,
		device d,
		device_type dt 
	WHERE
		d.deviceType = dt.id 
		AND dt.attribute = '屏幕' 
		AND e2.device = d.id 
	) t1 
WHERE
	t1.row_num > 1
	
-- 窗口函数 再次赋值
SELECT
	* 
FROM
	(
	SELECT
		e2.station,
		ROW_NUMBER() OVER ( PARTITION BY e2.station ) row_num 
	FROM
		esb_config e2
	) t1
WHERE
	t1.row_num > 1

SQL和实现思路2 分组

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

SELECT
	e2.station,
	e2.device 
FROM
	esb_config e2 
GROUP BY
	e2.station 
HAVING
	count( 1 ) > 1

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

SELECT
	e2.station,
	e2.device 
FROM
	esb_config e2,
	device d,
	device_type dt 
WHERE
	d.deviceType = dt.id 
	AND dt.attribute = '屏幕' 
	AND e2.device = d.id
-- 上面SQL 查出了配置表中，所有为屏幕的 设备和站点
-- 此时一个站点 如果配置了 多个屏幕，这个站点就会展示出多条

-- 然后在筛选一下，站点>1 的
GROUP BY
	e2.station 
HAVING
	count( 1 ) > 1

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

思路1再关联原表
使用这个逻辑，有一个问题。怎么显示一个站点，显示2次呢（设备不同的时候）
- 很简单，只需要在外层关联一个表即可（因为这个表本来就是这种逻辑，一个站点，显示2次）

SELECT * from esb_config e1, (

	SELECT
		e2.station 
	FROM
		esb_config e2,
		device d,
		device_type dt 
	WHERE
		d.deviceType = dt.id 
		AND dt.attribute = '屏幕' 
		AND e2.device = d.id 
	GROUP BY
		e2.station 
	HAVING
		count( 1 ) > 1
) t1
WHERE e1.station = t1.station



-- 核心是这样的结果
SELECT * from esb_config e1, (

	SELECT
		e2.station -- 这里,e2.device 取了没用，只是会筛选第一个 
	FROM
		esb_config e2
	GROUP BY
		e2.station 
	HAVING
		count( e2.station  ) > 1

) t1
WHERE e1.station = t1.station

-- 那这个结果加上限制对不对呢？不对的，为啥不对，这里不懂。
-- AND dt.attribute = '屏幕'

SQL和思路3 错误的

把一级分组，查询出来
把二级分组，查询出来
如果一级分组和二级分组，关联上就展示
这样查出的数据只查出了一条（也是上面的数据之一）文章来源地址https://www.toymoban.com/news/detail-636636.html

SELECT
	* 
FROM
	( 
		SELECT * FROM (
			SELECT
				e2.station,
				e2.device
			FROM
				esb_config e2,
				device d,
				device_type dt 
			WHERE
				e2.device = d.id 
				AND d.deviceType = dt.id 
				AND dt.attribute = '屏幕'
		) t2
		GROUP BY t2.station HAVING count(t2.station) > 1 
	) t1,
	
(SELECT * FROM (
	SELECT
		e2.station,
		e2.device 
	FROM
		esb_config e2,
		device d,
		device_type dt 
	WHERE
		e2.device = d.id 
		AND d.deviceType = dt.id 
		AND dt.attribute = '屏幕'
		) t2
		GROUP BY t2.device HAVING count(t2.device) > 1 
	) t3
	
WHERE
	t1.station = t3.station and t1.device = t3.device