此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 |
+------+----------+----------+--------+-------------+
近日被一条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 |
+------+----------+----------+--------+-------------+
解决方案 »
- 求一条SQL语句
- 大牛请进,mysql插入一条记录,插入的主键值与主键自增数值不一致的问题?
- 求砖石 mysql备份方案 大量数据备份 数据上百G
- 怎么从MYSQL的字符串字段中匹配一个字符???
- windows平台下drop table时出现'ODBC'@'localhost'错误信息
- MySQL,如何实现类似partition的功能
- 百分请教一个mysql权限限制的问题
- 在线等,mysql 如何解决大数据量的问题,每周一个表里就有50-60万条记录产生,如何进行表的设计,查询等。
- 安装方维公开课系统的时候,文件上传完打开后报错
- mysql 如果解决并非insert的数据主键冲突问题?
- 想买本mysql的经典教材,哪本好呢?
- password()与password_old(),这两个函数区别是什么呢?
-> 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>