group by 先排序再分組的問題

gydoesit

                                group by 先排序再分組的問題
group是分組,想先排序如何辦?
建一個表試試
--
-- 表的結(jié)構(gòu) `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;
--
-- 導(dǎo)出表中的數(shù)據(jù) `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得到了,但是,其他數(shù)據(jù)依然是每個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
這就是我們想要的結(jié)果了,但是,這種作法在行數(shù)非常多的情況下,相當(dāng)于把整個表復(fù)制了一次,估計(jì)效率低.
那用這種子查詢
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
然而,這種子查詢因?yàn)橛昧薸n,在數(shù)量多的情況下,也許還更慢些?不確定,沒時間測試
另外,還有一種方法,不過原理我也有些糊涂了,只是看網(wǎng)上有人這樣作,
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,豈不是相當(dāng)于子查詢?
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
結(jié)果竟然是
id     name     phone
1     a     1234
3     b     555
6     c     95434
和在原表用
SELECT * FROM `test` group by name
的結(jié)果一樣.看來視圖和真正的表畢竟是有區(qū)別的
補(bǔ)充:
上網(wǎng)再看了一下,原來第二種子查詢方法網(wǎng)上不是那樣的,盡管我那樣寫在這個例子上也成功了,但是,說不定其他表會錯?沒時間測試.網(wǎng)上的方法是
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,都可以得到正確的結(jié)果,盡管order by有點(diǎn)不同.
上面的排序都只針對一個字段,兩個及以上字段也可以采用類似于
select * from (select * from test order by id desc) t group by name
這樣的方法,在子查詢中可以多個字段來order by
下面刪除原來的test,重建一下
--
-- 表的結(jié)構(gòu) `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;
--
-- 導(dǎo)出表中的數(shù)據(jù) `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
都按我們的要求顯示了結(jié)果
               
               
               
               
               
               
               

本文來自ChinaUnix博客，如果查看原文請點(diǎn)：http://blog.chinaunix.net/u/4891/showart_1773895.html