求sql语句一条,高手来指点。测试通过马上揭贴,

Question

此SQL昨日已经求过,不过还存在一些问题各位高手路过记得回一下
近日被一条SQL语句所困扰,求解 -- phpMyAdmin SQL Dump
-- version 2.9.2
-- http://www.phpmyadmin.net
-- 
-- 主机: localhost
-- 生成日期: 2010 年 05 月 07 日 11:03
-- 服务器版本: 5.0.27
-- PHP 版本: 5.2.1
-- 
-- 数据库: `helpdb`
-- -- ---------------------------------------------------------- 
-- 表的结构 `score`
-- CREATE TABLE `score` (
  `id` int(11) NOT NULL auto_increment,
  `Uid` varchar(8) NOT NULL,
  `totalScore` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `sid_uid` (`Uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;-- 
-- 导出表中的数据 `score`
-- INSERT INTO `score` (`id`, `Uid`, `totalScore`) VALUES 
(1, '1', 10),
(2, '2', 11),
(3, '3', 12),
(4, '4', 13),
(5, '5', 14),
(6, '6', 15),
(7, '7', 16),
(8, '8', 17),
(9, '9', 18),
(10, '10', 19),
(11, '11', 20),
(12, '12', 21),
(13, '13', 22),
(14, '14', 23),
(15, '15', 24),
(16, '16', 25),
(17, '17', 26),
(18, '18', 27),
(19, '19', 28),
(20, '20', 29),
(21, '21', 30),
(22, '22', 31),
(23, '23', 32),
(24, '24', 33),
(25, '25', 34),
(26, '26', 35),
(27, '27', 36),
(28, '28', 37),
(29, '29', 38),
(30, '30', 39);-- ---------------------------------------------------------- 
-- 表的结构 `users`
-- CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `Name` varchar(50) NOT NULL,
  `Sex` int(8) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;-- 
-- 导出表中的数据 `users`
-- INSERT INTO `users` (`id`, `Name`, `Sex`) VALUES 
(1, 'a', 1),
(2, 'aa', 1),
(3, 'aa', 1),
(4, 'a', 1),
(5, 'a', 1),
(6, 'aa', 1),
(7, 'a', 1),
(8, 'aa', 1),
(9, 'aa', 1),
(10, 'a', 1),
(11, 'aa', 1),
(12, 'aa', 1),
(13, 'a', 1),
(14, 'a', 1),
(15, 'a', 1),
(16, 'a', 1),
(17, 'b', 2),
(18, 'bb', 2),
(19, 'bb', 2),
(20, 'bb', 2),
(21, 'bb', 2),
(22, 'bb', 2),
(23, 'bb', 2),
(24, 'b', 2),
(25, 'b', 2),
(26, 'b', 2),
(27, 'b', 2),
(28, 'b', 2),
(29, 'b', 2),
(30, 'b', 2);说明:
Num是排序列,
其他列是倒序
取的是前六名,期望结果
+------+----------+----------+--------+-------------+
| Num | ManName | WomanName | ManScore | WomanScore |
+------+----------+----------+--------+-------------+
| 1   | a       | b         | 25       | 39       |
| 2   | a       | b         | 24       | 38       |
| 3   | a       | b         | 23       | 37       |
| 4   | a       | b         | 22       | 36       |
| 5   | aa      | b         | 21       | 35       |
| 6   | aa      | b         | 20       | 34       |
+------+----------+----------+--------+-------------+

Answer 1

mysql> select num,
    ->  max(if(Sex=1,name,null)) as ManName,
    ->  max(if(Sex=2,name,null)) as WomanName,
    ->  max(if(Sex=1,totalScore,0)) as ManScore,
    ->  max(if(Sex=2,totalScore,0)) as WomanScore
    -> from (
    -> select b.name,a.totalScore,b.Sex ,(
    ->  select count(*) from score a1 inner join users b1 on a1.uid=b1.id
    ->  where Sex=b.Sex and (totalScore>a.totalScore or totalScore=a.totalScoreand a1.id<=a.id)
    -> ) as num
    -> from score a inner join users b on a.uid=b.id
    -> ) x
    -> where num<=6
    -> group by num;
+------+---------+-----------+----------+------------+
| num  | ManName | WomanName | ManScore | WomanScore |
+------+---------+-----------+----------+------------+
|    1 | a       | b         |       25 |         39 |
|    2 | a       | b         |       24 |         38 |
|    3 | a       | b         |       23 |         37 |
|    4 | a       | b         |       22 |         36 |
|    5 | aa      | b         |       21 |         35 |
|    6 | aa      | b         |       20 |         34 |
+------+---------+-----------+----------+------------+
6 rows in set (0.00 sec)mysql>