mysql - 选择查询问题不是我想要的数据
问题描述
我有两张桌子,father
并且grand_father
CREATE TABLE IF NOT EXISTS father (
id INT AUTO_INCREMENT,
father_name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS grand_father (
id INT AUTO_INCREMENT,
father_id int(6) NOT NULL,
grand_father_name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO father (id, father_name) VALUES
('1','Abid'),
('2','Hasan'),
('3','Jabed');
INSERT INTO grand_father (id, father_id,grand_father_name) VALUES
('1','1','Jon'),
('2','1','James'),
('3','2','Jakir'),
('4','2','Jamir'),
('5','3','Tom');
select f.father_name,g.grand_father_name
from grand_father g
left join father f
on g.father_id = f.id;
+-------------+-------------------+
| father_name | grand_father_name |
+-------------+-------------------+
| Abid | Jon |
| Abid | James |
| Hasan | Jakir |
| Hasan | Jamir |
| Jabed | Tom |
+-------------+-------------------+
代码链接:http ://sqlfiddle.com/#!9/8be4c/3 查询问题
我想要这样的结果:
+--------------+-------------------+
| father_name | grand_father_name |
+--------------+-------------------+
| Abid | Jon |
| | James |
+--------------+-------------------+
| Abid | Jon |
| | James |
+--------------+-------------------+
| Jabed | Tom |
+--------------+-------------------+
有没有可能?
我怎么能得到这样的结果。
我尝试:
select f.father_name, g.grand_father_name
from grand_father g
left join father f
on g.father_id = f.id
解决方案
利用group_concat()
select f.father_name,group_concat(g.grand_father_name SEPARATOR '\r\n')
from grand_father g
left join father f
on g.father_id = f.id
group by f.father_name
推荐阅读
- google-analytics - 我可以对 Google Analytics 的数据进行聚合吗(直接调用 Google Analytics API)
- python-3.x - Python 特征选择
- vue.js - Vuejs无法读取未定义错误的属性“使用”
- ios - Firebase/Crashlytics 在提交崩溃报告时遇到问题
- mysql - 从包含许多记录的 MySQL 集中选择一个分布式样本记录集
- javascript - 如何显示由 PHP 页面的按钮单击触发的 Selenium 脚本的进度条?
- python - Python sqlite3 conn.commit() 未将数据保存到 db 文件
- python - 熊猫:如何个性化 dataframe.boxplot()?
- angular - Angular 6:如何设置路由应用程序路由导航到子项目应用程序路由
- php - PHP array_unique 不返回唯一值