mysql统计查询问题

Question

数据库为mysql 4.0.26現在，有兩張表：用戶表：　　　　   school_id（學校id),user_id（用戶id）,user_pwd（登陸密碼）用戶做題記錄表：   user_id（用戶id）, site_id（做題站點 id） 情況是這樣的：用戶登錄后。 開始由第一站做題(必須按第一站到最後一站的順序，中間不能跳躍，站點的id是递增的,比如第一站站點id為1，第二站為2。。），每通過一站， 則會在用戶記錄表中產生一條記錄。 記錄用戶的id 跟通過站點的id 當用戶到达某个站点时，没有通过，退出整个做题流程然后要求查询出像下面这样的记录。名次 學校名稱 登入編號 密碼 最高站點
1 大一學校 pom1a11c 123 22
2 大一學校 pom1a13c 555 18
3 大一學校 pom1a15c 5850 14
1 小一學校 poc1a15c 455 27
2 小一學校 poc1a17c 564 26
3 小一學校 poc1a19c 12 25
4 小一學校 poc1a21c 96855 24
5 小一學校 poc1a23c 458 22
6 小一學校 poc1a25c 157 22
7 小一學校 poc1a27c 13568 22
8 小一學校 poc1a29c 2168 20
9 小一學校 poc1a31c 169578 18
10 小一學校 poc1a33c 2168 16多谢指点！！！！

Answer 1

   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 2

数据和期望结果如下：create table sm_user(
school_id int,
user_id varchar(30),
user_pwd varchar(30));create table sm_user_log(
user_id varchar(30),
level int);insert into sm_user values
(2,'poc1a23c','458'),(2,'poc1a25c','157'),(1,'pom1a11c','123'),
(2,'poc1a27c','13568'),(2,'poc1a29c','2168'),(2,'poc1a19c','12'),
(2,'poc1a21c','96855'),(2,'poc1a31c','169578'),(2,'poc1a33c','2168'),
(2,'poc1a15c','455'),(2,'poc1a17c','564'),(1,'pom1a13c','555'),
(1,'pom1a15c','5850');
insert into sm_user_log values
('poc1a23c',15),('poc1a23c',30),('poc1a23c',24),('poc1a25c',13),('poc1a25c',24),
('poc1a25c',18),('pom1a11c',25),('pom1a11c',28),('pom1a11c',36),('pom1a11c',44),
('poc1a27c',10),('poc1a27c',12),('poc1a27c',8),('poc1a27c',10),('poc1a29c',6),
('poc1a29c',15),('poc1a29c',19),('poc1a19c',14),('poc1a19c',20),('poc1a19c',15),
('poc1a21c',20),('poc1a21c',30),('poc1a21c',10),('poc1a31c',5),('poc1a31c',8),
('poc1a31c',2),('poc1a33c',5),('poc1a33c',4),('poc1a33c',2),('poc1a15c',2),('poc1a15c',7),
('poc1a15c',4),('poc1a17c',5),('poc1a17c',8),('poc1a17c',4),('poc1a17c',15),
('poc1a13c',2),('poc1a13c',1),('poc1a13c',8),('poc1a13c',6),('poc1a13c',16),
('pom1a15c',13),('pom1a15c',2),('pom1a15c',5),('pom1a15c',4),('pom1a15c',17),
('pom1a13c',3),('pom1a13c',8),('pom1a13c',4),('pom1a13c',15);rank(名次)   school_id     user_id     user_pwd     最高级别
  1              1         pom1a11c      123          44
  2              1         pom1a15c      5850         17
  3              1         pom1a13c      555          15
  1              2         poc1a21c      96855        30
  1              2         poc1a23c      458          30
  3              2         poc1a25c      157          24
  4              2         poc1a19c      12           20
  5              2         poc1a29c      2168         19
  6              2         poc1a13c      555          16
  7              2         poc1a17c      564          15
  8              2         poc1a27c      13568        12
  9              2         poc1a31c      169578       8
  10             2         poc1a15c      455          7
  11             2         poc1a33c      2168         5

Answer 3

结果应该是这样：rank(名次)   school_id     user_id     user_pwd     最高级别
  1              1         pom1a11c      123          44
  2              1         pom1a15c      5850         17
  3              1         pom1a13c      555          15
  1              2         poc1a21c      96855        30
  1              2         poc1a23c      458          30
  3              2         poc1a25c      157          24
  4              2         poc1a19c      12           20
  5              2         poc1a29c      2168         19
  6              2         poc1a17c      564          15
  7              2         poc1a27c      13568        12
  8              2         poc1a31c      169578       8
  9              2         poc1a15c      455          7
  10             2         poc1a33c      2168         5

Answer 4

mysql> select (
    ->  select count(*) from
    ->  (
    ->          select school_id,max_level
    ->          from
    ->          sm_user a1 inner join (
    ->          select user_id,max(level) as max_level
    ->          from sm_user_log
    ->          group by user_id) b1 using(user_id)
    ->  ) t
    ->  where t.school_id=a.school_id and t.max_level>b.max_level
    -> )+1 as rank,a.school_id,a.user_id,a.user_pwd,b.max_level
    -> from sm_user a inner join (
    ->  select user_id,max(level) as max_level
    ->  from sm_user_log
    ->  group by user_id) b using(user_id)
    -> order by a.school_id,b.max_level desc;
+------+-----------+----------+----------+-----------+
| rank | school_id | user_id  | user_pwd | max_level |
+------+-----------+----------+----------+-----------+
|    1 |         1 | pom1a11c | 123      |        44 |
|    2 |         1 | pom1a15c | 5850     |        17 |
|    3 |         1 | pom1a13c | 555      |        15 |
|    1 |         2 | poc1a23c | 458      |        30 |
|    1 |         2 | poc1a21c | 96855    |        30 |
|    3 |         2 | poc1a25c | 157      |        24 |
|    4 |         2 | poc1a19c | 12       |        20 |
|    5 |         2 | poc1a29c | 2168     |        19 |
|    6 |         2 | poc1a17c | 564      |        15 |
|    7 |         2 | poc1a27c | 13568    |        12 |
|    8 |         2 | poc1a31c | 169578   |         8 |
|    9 |         2 | poc1a15c | 455      |         7 |
|   10 |         2 | poc1a33c | 2168     |         5 |
+------+-----------+----------+----------+-----------+
13 rows in set (0.00 sec)mysql>

Answer 5

版主，有一个问题，mysql4支持这种在select中写select的语法吗？我之前试了提示语法错误。
select (
    ->  select count(*) from
    ->  (
即这种写法。

Answer 6

MySQL４中有些难度了。一句的可能性不大。不过可以用一些变量@rank 来实现。　手上没有安装mysql4.你先试一下这句能不能用。
select a.school_id,a.user_id,a.user_pwd,b.max_level
from sm_user a inner join (
select user_id,max(level) as max_level 
from sm_user_log 
group by user_id) b using(user_id)
order by a.school_id,b.max_level desc

Answer 7

回版主，上面的语句mysql4不支持，看来是没办法了。

Answer 8

这个版下测试还可以啊。 4.0.26 没有环境，没办法帮你试了。不行的话，只能在程序中去想办法显示mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.20    |
+-----------+
1 row in set (0.00 sec)

Answer 9

create view aa as 
SELECT `a`.`school_id` AS `school_id`,`a`.`user_id` AS `user_id`,MAX(`b`.`level`) AS `ma` FROM (`sm_user` `a` LEFT JOIN `sm_user_log` `b` ON((`a`.`user_id` = `b`.`user_id`))) GROUP BY `a`.`school_id`,`a`.`user_id` ORDER BY `a`.`school_id`,`a`.`user_id`$$or 
将AA生成临时表SELECT a.school_id,a.user_id,a.ma,COUNT(b.ma)+1 AS rank FROM aa a LEFT JOIN aa b ON a.school_id=b.school_id AND a.ma<b.ma
GROUP BY a.school_id,a.user_id,a.ma
ORDER BY a.school_id,COUNT(b.ma)+1,a.ma