在MySQL中结合多个查询结果(按列)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了在MySQL中结合多个查询结果(按列),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4382字,纯文字阅读大概需要7分钟。
内容图文
我有4个不同的查询,每个查询返回单独的结果集.
我需要将查询结果与使用单个查询相结合.
我的示例查询是:
1. select cls.* from (calls as cls inner join calls_users as clsusr on cls.id=clsusr.call_id) inner join users as usr on usr.id=cls.assigned_user_id where cls.assigned_user_id='seed_max_id'
2. select mtn.* from (meetings as mtn inner join meetings_users as mtnusr on mtn.id=mtnusr.meeting_id) inner join users as usr on usr.id=mtn.assigned_user_id where mtn.assigned_user_id='seed_max_id'
3. select tsk.* from tasks as tsk inner join users as usr on usr.id=tsk.assigned_user_id where tsk.assigned_user_id='seed_max_id'
4. select nts.* from (notes as nts inner join accounts as acnts on acnts.id=nts.parent_id) inner join users as usr on usr.id=acnts.assigned_user_id where acnts.assigned_user_id='seed_max_id'
我尝试了以下方式,但它没有用
Combine: SELECT tbl1.*, tbl2.*
from (select cls.* from (calls as cls inner join calls_users as clsusr on cls.id=clsusr.call_id) inner join users as usr on usr.id=cls.assigned_user_id where cls.assigned_user_id='seed_max_id') as tbl1
left outer join
(select mtn.* from (meetings as mtn inner join meetings_users as mtnusr on mtn.id=mtnusr.meeting_id) inner join users as usr on usr.id=mtn.assigned_user_id where mtn.assigned_user_id='seed_max_id') as tbl2
using(assigned_user_id)
我也尝试了右外连接和其他内连接
我真的被困了,如果有人知道解决方案那么请帮忙.
我需要像How can I join two tables with different number of rows in MySQL?这样的类似结果.
数据样本:
从查询1:
+-------------------------------------------+------------------+-
| Call Name | Call Description |
+-------------------------------------------+------------------+-
| Discuss Review Process | NULL |
| Get More information on the proposed deal | NULL |
| Left a message | NULL |
| Discuss Review Process | NULL |
+-------------------------------------------+------------------+
从查询2:
+-----------------------+-----------------------------------------------------------
| Meeting Name | Meeting Description
+-----------------------+-----------------------------------------------------------
| Review needs | Meeting to discuss project plan and hash out the details o
| Initial discussion | Meeting to discuss project plan and hash out the details o
| Demo | Meeting to discuss project plan and hash out the details o
| Discuss pricing | Meeting to discuss project plan and hash out the details o
| Review needs | Meeting to discuss project plan and hash out the details o
+-----------------------+-----------------------------------------------------------
我需要组合如下列:
+-------------------------------------------+------------------+-------------------+-------------------+
| Call Name | Call Description |Meeting Name |Meeting Description|
+-------------------------------------------+------------------+-------------------+-------------------+
| Discuss Review Process | NULL |Review needs |Meeting to discuss |
| Get More information on the proposed deal | NULL |Initial discussion |Meeting to discuss |
| Left a message | NULL |Demo |Meeting to discuss |
| NULL | NULL |Discuss pricing |Meeting to discuss |
| NULL | NULL |Review needs |Meeting to discuss |
+-------------------------------------------+------------------+-------------------+-------------------+
解决方法:
您可以做的最好的是UNION或UNION ALL,但这需要它们具有相同的类型和列数.例如:
SELECT 'Customer' AS type, id, name FROM customer
UNION ALL
SELECT 'Supplier', id, name FROM supplier
UNION ALL
SELECT 'Employee', id, full_name FROM employee
列名称不必匹配.第一部分的别名将用于其余部分.
我还要补充一点,而不是:
select cls.* from (calls as cls inner join calls_users as clsusr on cls.id=clsusr.call_id) inner join users as usr on usr.id=cls.assigned_user_id where cls.assigned_user_id='seed_max_id'
你应该删除不必要的子查询,只需:
SELECT c.*
FROM calls c
JOIN calls_users cu ONc.id = cu.call_id
WHERE c.assigned_user_id = 'seed_max_id'
不需要额外的复杂性,上面的内容显然更具可读性.
内容总结
以上是互联网集市为您收集整理的在MySQL中结合多个查询结果(按列)全部内容,希望文章能够帮你解决在MySQL中结合多个查询结果(按列)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。