SQL学习——选择分组中的最大值

最新推荐文章于 2023-12-21 15:03:10 发布

skyxfire

最新推荐文章于 2023-12-21 15:03:10 发布

阅读量1.1k

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/skyxfire/article/details/11473193

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据如下：

第一列为id，第二列是标签标号，第三列是时间，第四列是房间id

|------------ID------------||-------------------------rfid-------------------------||---------------------time-------------------||-----------------------------room_id----------------------------||

0 100000000000000020120001 20130716 09:24:07 8a8191913d826b19013daae03c5f0f69

1 100000000000000020120002 20130712 09:24:07 8a8191913d826b19013daae03c5f0f60

2 100000000000000020120001 20130714 09:24:07 8a8191913d826b19013daae03c5f0f69

3 100000000000000020120003 20130712 09:24:07 8a8191913d826b19013daae03c5f0f60

4 100000000000000020120002 20130714 09:24:07 8a8191913d826b19013daae03c5f0f69

5 100000000000000020120003 20130714 09:24:07 8a8191913d826b19013daae03c5f0f69

|------------ID------------||-------------------------rfid-------------------------||---------------------time-------------------||-----------------------------room_id----------------------------||

现在需要从中选出每个标签号时间最新的房间id。

刚开始想到用Group By来查，

SELECT max(time),rfid,id, room_id FROM TABLE_TEST GROUP BY rfid ORDER BY ID;

比较纠结的是，查询结果的room_id和time对不上号。

网上找了下，有用内联查询的

select A.id,A.time,A.rfid, A.room_id from TABLE_TEST A inner join (select rfid, max(time) as time fromTABLE_TEST group by rfid) B on A.rfid=B.rfid AND A.time=B.time;

查询结果一看，对了！！！

但是这个查询还有一个过滤不了，就是如果有两条最新的记录，比如说

|------------ID------------||-------------------------rfid-------------------------||---------------------time-------------------||-----------------------------room_id----------------------------||

0 100000000000000020120001 20130716 09:24:07 8a8191913d826b19013daae03c5f0f69

1 100000000000000020120002 20130712 09:24:07 8a8191913d826b19013daae03c5f0f60

2 100000000000000020120001 20130714 09:24:07 8a8191913d826b19013daae03c5f0f69

3 100000000000000020120003 20130712 09:24:07 8a8191913d826b19013daae03c5f0f60

4 100000000000000020120002 20130714 09:24:07 8a8191913d826b19013daae03c5f0f69

5 100000000000000020120003 20130714 09:24:07 8a8191913d826b19013daae03c5f0f69

6 100000000000000020120003 20130714 09:24:07 8a8191913d826b19013daae03c5f0f69

|------------ID------------||-------------------------rfid-------------------------||---------------------time-------------------||-----------------------------room_id----------------------------||

这个好解决，加了个group by将就着解决问题。

select A.id,A.time,A.rfid, A.fangjid from TABLE_TEST A inner join (select id,rfid, max(time) as time from TABLE_TEST group by rfid) B on A.rfid=B.rfid AND A.time=B.time group by a.rfid;

拆开来看的话，其实也可以用两个select 组合查询

删除冗余记录：

SELECT count(*) FROM WZ_PAND;
DELETE FROM WZ_PAND WHERE id NOT IN 
(
SELECT
	A.id
FROM
	WZ_PAND A
INNER JOIN (
	SELECT
		max(time) AS time,
		rfid
	FROM
		WZ_PAND
	GROUP BY
		rfid
) B ON A.rfid = B.rfid
AND A.time=B.time);

SELECT count(*) FROM WZ_PAND;

SELECT * FROM WZ_PAND;

记录下SQLite批量更新的学习成果。
更新盘点表中的信息到物资表中

UPDATE WZ_WUZ
SET fangjid = (
	SELECT
		fangjid
	FROM
		WZ_PAND
	WHERE
		WZ_PAND.rfid = WZ_WUZ.rfid
	LIMIT 1
),
 state = '02',
 operType = (
	CASE operType
	WHEN '' THEN
		'02'
	WHEN NULL THEN
		'02'
	ELSE
		operType
	END
)
WHERE
	EXISTS (
		SELECT
			*
		FROM
			WZ_PAND
		WHERE
			WZ_PAND.rfid = WZ_WUZ.rfid
		AND WZ_PAND.fangjid <> WZ_WUZ.fangjid
	);

这个语句在SQLite下可用，加上fangjid不等于的判断，将六千条数据80秒的时间，减少到3.062s，减少了无效更新的次数。

还有个语句SQLite用不了，我也没条件试了，记下来先

UPDATE WZ_WUZ
SET (fangjid, state, operType) = (
	SELECT
		t2.fangjid,
		'02',
		CASE t3.operType
	WHEN '' THEN
		'02'
	WHEN NULL THEN
		'02'
	ELSE
		t3.operType
	END
	FROM
		WZ_PAND t2
	INNER JOIN WZ_WUZ t3 ON t3.rfid = t2.rfid
)