mysql 查询不返回结果_MySQL查询不返回所有记录

最新推荐文章于 2024-07-26 03:47:23 发布

邱锡鹏

最新推荐文章于 2024-07-26 03:47:23 发布

阅读量737

点赞数

文章标签： mysql 查询不返回结果

本文链接：https://blog.csdn.net/weixin_29251567/article/details/113221078

版权

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

架构测试数据：

mysql> select * from main;

| 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;

+----------+------------+--------------+------------------+

+----------+------------+--------------+------------------+

| 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;

+----------+------------+-----------+----------+---------------------+

+----------+------------+-----------+----------+---------------------+

| 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;

+----------+------------+--------------+------------------+

+----------+------------+--------------+------------------+

| 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)

查询我尝试了结果：

mysql> SELECT m.*,

-> GROUP_CONCAT(DISTINCT f.food SEPARATOR ',') AS food,

-> GROUP_CONCAT(DISTINCT a.alcohol_type SEPARATOR ',') AS alcohol,

-> a.alcohol_quantity,

-> GROUP_CONCAT(DISTINCT d.drug SEPARATOR ',') AS drug,

-> h.intensity AS headache_intensity,

-> h.duration AS headache_duration,

-> GROUP_CONCAT(DISTINCT ha.area) AS headache_areas,

-> GROUP_CONCAT(DISTINCT hs.symptoms) AS headache_symptoms,

-> h.notes AS headache_notes

-> FROM main AS m

-> LEFT OUTER JOIN food AS f ON f.username = m.username AND f.date = m.date

-> LEFT OUTER JOIN headache AS h ON h.username = m.username AND h.date = m.date

-> LEFT OUTER JOIN headache_symptoms AS hs ON hs.username = m.username AND hs.date = m.date

-> LEFT OUTER JOIN headache_areas AS ha ON ha.username = m.username AND ha.date = m.date

-> LEFT OUTER JOIN drugs AS d ON d.username = m.username AND d.date = m.date

-> LEFT OUTER JOIN alcohol AS a ON a.username = m.username AND a.date = m.date

-> ;

+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+

| test123 | 2012-09-16 | 1 | no | cigarettes | 20 | 480 | 0 | 420 | 2 | 7 | 7 | 6 | 4 | Roamed a lot on the bike | no | rice,pizza,french fries,burger,naan,fried rice,lemon juice | beer,beer_ale,absinthe,rum,tequila | 0 | ching,chong,blah1,blurg2 | 6 | 12 | left_temple,right_temple,behind_left_ear | aura,vertigo,dizziness | something something |

1 row in set (0.00 sec)

它没有向我显示表中的其他记录.有人可以帮我解决这个问题吗？提前致谢.