示例数据库表(鸣谢鞠老师)
mysql> show tables;
+-----------------+
| Tables_in_jol |
+-----------------+
| compileinfo |
| contest |
| contest_problem |
| custominput |
| loginlog |
| mail |
| news |
| online |
| privilege |
| problem |
| reply |
| runtimeinfo |
| sim |
| solution |
| source_code |
| topic |
| users |
+-----------------+
17 rows in set (0.00 sec)
mysql> select * from sim;
+------+----------+------+
| s_id | sim_s_id | sim |
+------+----------+------+
| 5235 | 5229 | 85 |
| 5239 | 5230 | 100 |
| 5243 | 5231 | 85 |
| 5358 | 5339 | 100 |
| 5365 | 5271 | 98 |
| 5383 | 5339 | 100 |
| 5386 | 5339 | 100 |
| 5425 | 5339 | 100 |
| 5514 | 5339 | 100 |
| 5531 | 5518 | 100 |
| 5537 | 5339 | 100 |
| 5539 | 5518 | 100 |
| 5549 | 5339 | 100 |
| 5569 | 5518 | 100 |
| 5601 | 5586 | 100 |
| 5607 | 5602 | 92 |
| 5620 | 5602 | 92 |
| 5674 | 5673 | 100 |
| 5677 | 5466 | 100 |
| 5680 | 5663 | 100 |
+------+----------+------+
20 rows in set (0.00 sec)
mysql> describe solution;
+-------------+-----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------------------+----------------+
| solution_id | int(11) | NO | PRI | NULL | auto_increment |
| problem_id | int(11) | NO | MUL | 0 | |
| user_id | char(48) | NO | MUL | NULL | |
| time | int(11) | NO | | 0 | |
| memory | int(11) | NO | | 0 | |
| in_date | datetime | NO | | 0000-00-00 00:00:00 | |
| result | smallint(6) | NO | MUL | 0 | |
| language | int(10) unsigned | NO | | 0 | |
| ip | char(15) | NO | | NULL | |
| contest_id | int(11) | YES | MUL | NULL | |
| valid | tinyint(4) | NO | | 1 | |
| num | tinyint(4) | NO | | -1 | |
| code_length | int(11) | NO | | 0 | |
| judgetime | timestamp | YES | | CURRENT_TIMESTAMP | |
| pass_rate | decimal(2,2) unsigned | NO | | 0.00 | |
| lint_error | int(10) unsigned | NO | | 0 | |
+-------------+-----------------------+------+-----+---------------------+----------------+
16 rows in set (0.00 sec)
mysql> select a1.user_id as "学号(copy)", a2.user_id as "学号(copied)", sim as "相似度(%)" from sim, solution as a1, solution as a2 where s_id=a1.solution_id and sim_s_id=a2.solution_id;
+--------------+----------------+--------------+
| 学号(copy) | 学号(copied) | 相似度(%) |
+--------------+----------------+--------------+
| 2013201410 | heu_songxu | 85 |
| 2013201410 | heu_songxu | 100 |
| 2013201410 | heu_songxu | 85 |
| 2014201104 | 2014201130 | 100 |
| 2014201314 | 2014201321 | 98 |
| 2014201102 | 2014201130 | 100 |
| 2014201101 | 2014201130 | 100 |
| 2014201402 | 2014201130 | 100 |
| 2014201204 | 2014201130 | 100 |
| 2014201213 | 2014201228 | 100 |
| 2014201204 | 2014201130 | 100 |
| 2014201205 | 2014201228 | 100 |
| 2014201106 | 2014201130 | 100 |
| 2014201220 | 2014201228 | 100 |
| 2014201314 | 2014201321 | 100 |
| 2014201213 | 2014201228 | 92 |
| 2014201205 | 2014201228 | 92 |
| 2014201106 | 2014201118 | 100 |
| 2014201417 | 2014201406 | 100 |
| 2014201110 | 2014201221 | 100 |
+--------------+----------------+--------------+
20 rows in set (0.00 sec)