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

数据如下：

第一列为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
)