mysql 先排序后分组

Question

不用子查询，用了子查询会查一次全表。
表结构
ID Type User Level
取出Level 最高，Type分组的ID出来

Answer 1

贴建表及插入记录的SQL，及要求结果出来看看select * from tt a where not exists(select 1 fromn tt where a.Type=Type
and a.Level<Level)

Answer 2

CREATE TABLE IF NOT EXISTS `test` (
  `Id` int(11) NOT NULL,
  `PlayerId` int(11) NOT NULL,
  `Type` int(11) NOT NULL,
  `Level` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`test` (`Id`, `PlayerId`, `Type`, `Level`) VALUES (NULL, '1', '1', '2'), (NULL, '1', '1', '13'), (NULL, '1', '2', '23'), (NULL, '1', '2', '3'), (NULL, '1', '3', '24');
EXPLAIN SELECT C.`Id` FROM ( SELECT * FROM `test` WHERE `PlayerId` = 1 ORDER BY `Level` DESC ) AS C GROUP BY C.`Type` 
这样的执行效率太低了。

Answer 3

SELECT * FROM TEST a WHERE NOT EXISTS(SELECT 1 FROM TEST WHERE a.Type=TYPE
 AND a.Level<LEVEL)在Type、Level上建立复合索引

Answer 4

-- 按照type 进行分组，Level 的id( so easy)
SELECT id,TYPE,MAX(LEVEL) FROM test GROUP BY TYPE;

Answer 5

-- 按照type 进行分组，Level 的id( so easy)
SELECT id,TYPE,MAX(LEVEL) FROM test GROUP BY TYPE;-----------------------------------------------------------------额这个有问题，呵呵，错了

Answer 6

版主，少了个user。

Answer 7

2楼的数据中没有user嘛。

Answer 8

我Sb了结构应该是这样的。
CREATE TABLE IF NOT EXISTS `test` (
  `Id` int(11) NOT NULL auto_increment,
  `PlayerId` int(11) NOT NULL,
  `Type` int(11) NOT NULL,
  `Star` tinyint(4) NOT NULL,
  `Level` tinyint(4) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
INSERT INTO `test`.`test` (`Id`, `PlayerId`, `Type`,`Star` , `Level`) VALUES (NULL, '1', '1', '2' ,'2'), (NULL, '1', '1', '1' , '13'), (NULL, '1', '2', '3' , '23'), (NULL, '1', '2', '1' , '3'), (NULL, '1', '3', '2' , '24');

Answer 9

要Star倒序，level倒序。Playerid = 1 Group BY TYPE,取得Id

Answer 10

SELECT * FROM ( SELECT * FROM `test` WHERE `PlayerId` = 1 ORDER BY `Star` DESC , `Level` DESC ) AS C GROUP BY C.`Type
PlayerId
Id      Type      Star      Level
1      1      1      2      2
1      3      2      3      23
1      5      3      2      24

Answer 11

SELECT * FROM TEST a WHERE NOT EXISTS(SELECT 1 FROM TEST WHERE a.Type=TYPE
   AND a.star<star)

Answer 12

or
SELECT * FROM TEST a WHERE NOT EXISTS(SELECT 1 FROM TEST WHERE a.Type=TYPE
   AND (a.star<star
   OR
   a.star=star AND a.level<LEVEL))
  

Answer 13

这个可以 ，不过Player加到那里？谢谢大神~

Answer 14

SELECT * FROM TEST a WHERE NOT EXISTS(SELECT 1 FROM TEST WHERE a.Type=TYPE
    AND (a.star<star
    OR
    a.star=star AND a.level<LEVEL))
and a.Playerid=1

Answer 15

INSERT INTO `test` (`Id`, `PlayerId`, `Type`, `Star`, `Level`) VALUES
(2, 1, 1, 1, 13),(8, 1, 1, 1, 50),(1, 1, 1, 2, 2),(4, 1, 2, 1, 3),(3, 1, 2, 3, 23),(5, 1, 3, 2, 24),(6, 2, 1, 1, 1),(7, 2, 5, 1, 1);
SELECT * FROM test a WHERE NOT EXISTS(SELECT 1 FROM test WHERE a.Type = Type AND ( a.Star < Star OR a.Star = Star AND a.Level < Level ) )  AND a.PlayerId = 2
这样查不出来。

Answer 16

SELECT * FROM test a WHERE NOT EXISTS(SELECT 1 FROM test WHERE a.Type = Type AND ( a.Star < Star OR a.Star = Star AND a.Level < Level ) AND PlayerId = 2 )  AND a.PlayerId = 2
这样就好了。

Answer 17

INSERT INTO `test` (`Id`, `PlayerId`, `Type`, `Star`, `Level`) VALUES
(2, 1, 1, 1, 13),(8, 1, 1, 1, 50),(1, 1, 1, 2, 2),(9, 1, 1, 2, 2),(4, 1, 2, 1, 3),(3, 1, 2, 3, 23),(5, 1, 3, 2, 24),(6, 2, 1, 1, 1),(7, 2, 5, 1, 1);
这样的数据不能排除重复的数据呢。PlayerId = 1

Answer 18

这样的数据不能排除重复的数据呢
加入唯一标识的字段IDSELECT * FROM test a WHERE NOT EXISTS(SELECT 1 FROM test WHERE a.Type = `Type`
AND (a.Star < Star OR (a.Star=Star AND a.Level < `Level`)
OR
(a.Star = Star AND a.Level= `LEVEl` AND a.id<id)
))
AND a.`PlayerId`=2

Answer 19

SELECT * FROM test a WHERE NOT EXISTS(SELECT 1 FROM test WHERE a.Type = `Type`
 AND (a.Star < Star OR (a.Star=Star AND a.Level < `Level`)
 OR
 (a.Star = Star AND a.Level= `LEVEl` AND a.id<id)
 ))
 AND a.`PlayerId`=1

Answer 20

SELECT * FROM test a WHERE NOT EXISTS(SELECT 1 FROM test WHERE a.Type = `Type` AND ( a.Star < Star OR ( a.Star=Star AND a.Level < `Level` ) OR( a.Star = Star AND a.Level = `LEVEl` AND a.id < id ) ) AND `PlayerId`=1 ) AND a.`PlayerId`=1
这个成功了。但是效率比SELECT * FROM ( SELECT * FROM `test` WHERE `PlayerId` = 1 ORDER BY `Star` DESC , `Level` DESC ) AS C GROUP BY C.`Type慢多了。

Answer 21

SELECT * FROM ( SELECT * FROM `test` WHERE `PlayerId` = 1 ORDER BY `Star` DESC , `Level` DESC ) AS C GROUP BY C.`Type
不是标准的SQL语句在Type、Star、Level、id、PlayerId上建立复合索引

Answer 22

create index xxx on `test` (PlayerId,C.`Type,`Star` DESC , `Level` DESC )select *
from from `test` USE INDEX (xxx)
where `PlayerId` = 1 
GROUP BY C.`Typeselect *
from `test` a
where `PlayerId` = 1 
and not exists (select 1 from `test` where  `PlayerId` = 1 and `Type` =a.`Type` and (`Star`>a.`Star` or (`Star`=a.`Star` and `Level`>a.`Level`)))