mysql先排序在分組

標籤：

– 表的結構 `test`
–

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`phone` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

–
– 匯出表中的資料 `test`
–

INSERT INTO `test` (`id`, `name`, `phone`) VALUES
(1, ‘a’, 1234),
(2, ‘a’, 3333),
(3, ‘b’, 555),
(4, ‘b’, 6773),
(5, ‘a’, 743),
(6, ‘c’, 95434);

查詢一下,
SELECT * FROM `test` group by name
得到
id name phone
1 a 1234
3 b 555
6 c 95434

但我們想得到id最大的name怎麼辦?
SELECT max(id),id,name,phone FROM test group by name
得到
max(id) id name phone
5 1 a 1234
4 3 b 555
6 6 c 95434
可以看到,雖然每個name的最大id得到了,但是,其他資料依然是每個name的第一行
用子查詢
select * from (select * from test order by id desc) t group by name
得到
id name phone
5 a 743
4 b 6773
6 c 95434
這就是我們想要的結果了,但是,這種作法在行數非常多的情況下,相當於把整個表複製了一次,估計效率低.
那用這種子查詢
select * from test t where id in (select max(id) from test group by name)
得到
id name phone
4 b 6773
5 a 743
6 c 95434
然而,這種子查詢因為用了in,在數量多的情況下,也許還更慢些?不確定,沒時間測試
另外,還有一種方法,不過原理我也有些糊塗了,只是看網上有人這樣作,
select * from test t inner join (select * from test order by id desc) t2 on t.id=t2.id group by t.name
得到
id name phone id name phone
5 a 743 5 a 743
4 b 6773 4 b 6773
6 c 95434 6 c 95434
為了想提高效率,想到了視圖,先按id desc排個視圖,再group by name,豈不是相當於子查詢?
CREATE VIEW `testv` AS select `test`.`id` AS `id`,`test`.`name` AS `name`,`test`.`phone` AS `phone` from `test` order by `test`.`id` desc;
視圖建立了,再查詢
SELECT * FROM `testv` group by name
結果竟然是
id name phone
1 a 1234
3 b 555
6 c 95434
和在原表用
SELECT * FROM `test` group by name
的結果一樣.看來視圖和真正的表畢竟是有區別的

補充:
上網再看了一下,原來第二種子查詢方法網上不是那樣的,儘管我那樣寫在這個例子上也成功了,但是,說不定其他表會錯?沒時間測試.網上的方法是
select * from test t inner join (select max(id) as id,name from test group by name) t2 on t.id=t2.id and t.name=t2.name
得到
id name phone id name
5 a 743 5 a
4 b 6773 4 b
6 c 95434 6 c
另外,把上面這些查詢方法中的test表換成testv,都可以得到正確的結果,儘管order by有點不同.
上面的排序都只針對一個欄位,兩個及以上欄位也可以採用類似於
select * from (select * from test order by id desc) t group by name
這樣的方法,在子查詢中可以多個欄位來order by
下面刪除原來的test,重建一下
–
– 表的結構 `test`
–

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`month` int(11) NOT NULL,
`serial` int(11) NOT NULL,
`other` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

–
– 匯出表中的資料 `test`
–

INSERT INTO `test` (`id`, `name`, `month`, `serial`, `other`) VALUES
(1, ‘a’, 200807, 2, ‘aaa1′),
(2, ‘a’, 200805, 2, ‘aaa2′),
(3, ‘b’, 200805, 3, ‘bbb3′),
(4, ‘b’, 200805, 4, ‘bbb4′),
(5, ‘a’, 200805, 1, ‘aaa5′),
(6, ‘c’, 200807, 5, ‘ccc6′),
(7, ‘b’, 200807, 8, ‘bbb7′),
(8, ‘c’, 200807, 3, ‘ccc8′),
(9, ‘a’, 200805, 6, ‘aaa9′);

查詢
select * from (select * from test order by month desc,serial desc) t group by name
得到
id name month serial other
1 a 200807 2 aaa1
7 b 200807 8 bbb7
6 c 200807 5 ccc6
換一下排序方式
select * from (select * from test order by month asc,serial desc) t group by name
得到
id name month serial other
9 a 200805 6 aaa9
4 b 200805 4 bbb4
6 c 200807 5 ccc6
都按我們的要求顯示了結果

此條目發表在 Excellence Article 分類目錄，貼了 MySQL, SQL 標籤。將固定連結加入收藏夾。

 

 

select * from (select * from households order by id desc) h group by e_id
select * from households t where id = (select max(id) from households where e_id = t.e_id)
select * from tb t where not exists (select 1 from tb where 網站 = t.網站 and 時間 > t.時間)

mysql先排序在分組