mysql统计查询问题

[阿_布]

数据库为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

多谢指点！！！！

[ACMAIN_CHM]

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[阿_布]

数据和期望结果如下：

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

[阿_布]

结果应该是这样：

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

[观弈道童]

每天回帖即可获得10分可用分！小技巧：教您如何更快获得可用分

[ACMAIN_CHM]

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>

[阿_布]

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

[ACMAIN_CHM]

MySQL4 不支持子查询。

[阿_布]

那这个查询没有办法一条语句实现了 。

[ACMAIN_CHM]

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

[阿_布]

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

[ACMAIN_CHM]

这个版下测试还可以啊。 4.0.26 没有环境，没办法帮你试了。不行的话，只能在程序中去想办法显示

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.20    |
+-----------+
1 row in set (0.00 sec)

[wwwwb]

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

[阿_布]

多谢！