MySQL查询不返回所有记录

我在我的数据库中有几个表,并使用左外连接来连接表并执行查询.问题是,并非所有匹配的记录都被返回,第一个记录总是因某种原因从结果中被跳过.

这是SQL Fiddle的链接,

架构测试数据：

mysql> select * from main;
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+
| username | date       | water_quantity | water_chilled | smoked_what | smoke_count | sleep_duration | study_duration | screen_duration | loud_level | heat_level | humidity_level | stress_physical | stress_mental | stress_notes             | menstruation |
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+
| test123  | 2012-09-16 |              1 | no            | cigarettes  |          20 |            480 |              0 |             420 |          2 |          7 |              7 |               6 |             4 | Roamed a lot on the bike | no           |
| test123  | 2012-09-13 |              2 | no            | cigarettes  |          12 |            300 |              0 |               0 |          1 |          1 |              1 |               6 |             3 | met friends              | no           |
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+
2 rows in set (0.00 sec)

mysql> select * from food;
+----------+------------+--------------+
| username | date       | food         |
+----------+------------+--------------+
| test123  | 2012-09-16 | rice         |
| test123  | 2012-09-16 | pizza        |
| test123  | 2012-09-16 | french fries |
| test123  | 2012-09-16 | burger       |
| test123  | 2012-09-13 | naan         |
| test123  | 2012-09-13 | fried rice   |
| test123  | 2012-09-13 | lemon juice  |
+----------+------------+--------------+
7 rows in set (0.00 sec)

mysql> select * from alcohol;
+----------+------------+--------------+------------------+
| username | date       | alcohol_type | alcohol_quantity |
+----------+------------+--------------+------------------+
| test123  | 2012-09-16 | beer         |                0 |
| test123  | 2012-09-16 | beer_ale     |                0 |
| test123  | 2012-09-16 | absinthe     |                0 |
| test123  | 2012-09-13 | rum          |                0 |
| test123  | 2012-09-13 | tequila      |                0 |
+----------+------------+--------------+------------------+
5 rows in set (0.00 sec)

mysql> select * from headache;
+----------+------------+-----------+----------+---------------------+
| username | date       | intensity | duration | notes               |
+----------+------------+-----------+----------+---------------------+
| test123  | 2012-09-16 |         6 |       12 | something something |
+----------+------------+-----------+----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from headache_areas;
+----------+------------+-----------------+
| username | date       | area            |
+----------+------------+-----------------+
| test123  | 2012-09-16 | left_temple     |
| test123  | 2012-09-16 | right_temple    |
| test123  | 2012-09-16 | behind_left_ear |
+----------+------------+-----------------+
3 rows in set (0.00 sec)

mysql> select * from headache_symptoms;
+----------+------------+-----------+
| username | date       | symptoms  |
+----------+------------+-----------+
| test123  | 2012-09-16 | aura      |
| test123  | 2012-09-16 | vertigo   |
| test123  | 2012-09-16 | dizziness |
+----------+------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from alcohol;
+----------+------------+--------------+------------------+
| username | date       | alcohol_type | alcohol_quantity |
+----------+------------+--------------+------------------+
| test123  | 2012-09-16 | beer         |                0 |
| test123  | 2012-09-16 | beer_ale     |                0 |
| test123  | 2012-09-16 | absinthe     |                0 |
| test123  | 2012-09-13 | rum          |                0 |
| test123  | 2012-09-13 | tequila      |                0 |
+----------+------------+--------------+------------------+
5 rows in set (0.00 sec)

mysql> select * from drugs;
+----------+------------+----------+
| username | date       | drug     |
+----------+------------+----------+
| test     | 2012-08-21 | crocin   |
| test     | 2012-08-21 | azithral |
| test     | 2012-08-21 | crocin   |
| test     | 2012-08-21 | azithral |
| test     | 2012-08-21 | crocin   |
| test     | 2012-08-21 | azithral |
| test123  | 2012-09-13 | ching    |
| test123  | 2012-09-13 | chong    |
| test123  | 2012-09-13 | blah1    |
| test123  | 2012-09-13 | blurg2   |
+----------+------------+----------+
10 rows in set (0.00 sec)

查询我尝试了结果：

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!