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
-> ;
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+
| 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 | food | alcohol | alcohol_quantity | drug | headache_intensity | headache_duration | headache_areas | headache_symptoms | headache_notes |
+----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+
| 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)
它没有向我显示表中的其他记录.有人可以帮我解决这个问题吗？提前致谢.
最佳答案
它完全输出你“要求它”做的事情(使用group concat)：
USERNAME |...| FOOD | ALCOHOL ...
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
test123 |...| rice,lemon juice | beer,tequila ...
使用组concat时,它将分组列的所有值连接到一个输出字段.
更新：
我想我知道你想要实现什么,因为它在表main的两个记录中都是相同的用户名,它将显示为一列,解决你需要找到第一个字段,这两个记录不相同 – 这是约会.所以你要做的就是将GROUP BY m.username,date添加到end of your query.
（编辑：威海站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
