mysql联合问题 [英] mysql union question

问题描述

mysql>从 id=194582 的帖子中选择 job_desc_title；

<前>+-------------------------------------+|job_desc_title |+-------------------------------------+|言语/语言病理学家 |+-------------------------------------+1 行(0.00 秒)

mysql>从帐户中选择 email_address,first_name,last_name,home_phone_area,home_phone_num->在profiles.account_id=accounts.id上左加入配置文件->其中accounts.id=5；

<前>+--------------------+------------+-----------+-----------------+----------------+|电子邮件地址 |名字|姓氏 |home_phone_area |home_phone_num |+--------------------+------------+-----------+-----------------+----------------+|[e-mail] |建华 |他|第425话3584396 |+--------------------+------------+-----------+-----------------+----------------+1 行(0.00 秒)

我需要合并以上 2 个查询，但我的试验失败了:

mysql>从 id=194582 的帖子中选择 job_desc_title->联合所有->从帐户中选择 email_address,first_name,last_name,home_phone_area,home_phone_num->在profiles.account_id=accounts.id上左加入配置文件->其中accounts.id=5；

<块引用>

ERROR 1222 (21000): 使用的 SELECT 语句具有不同的列数

完成这项工作的正确版本是什么?

解决方案

您正在从第一个中选择 job_desc_title，然后从第二个中选择电子邮件地址、名字、姓氏等.这不是工会.

您要做的是加入，我建议您仔细阅读这些内容.联合获取两个查询的结果并垂直组合它们.连接获取两个表的结果并水平组合它们.联合添加行，连接添加列.您要做的是添加一列(job_desc_title)，而不是行.合并行(即联合)需要相同的列才能工作.

我还认为您在应该使用内连接时使用了左连接.

选择a.email_address,a.名字，a.姓氏，a.home_phone_area,a.home_phone_num,post.job_desc_title从帐户 a内连接配置文件 p ona.id=p.account_id内部连接帖子发布于--我不知道这里的关系是什么，所以我猜p.posting_id = post.id在哪里a.id=5

希望这能让您走上正轨.

mysql> select job_desc_title from postings where id=194582;

+-----------------------------+
| job_desc_title              |
+-----------------------------+
| Speech/Language Pathologist |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select email_address,first_name,last_name,home_phone_area,home_phone_num from accounts
->  left join profiles on profiles.account_id=accounts.id  
->  where accounts.id=5;

+--------------------+------------+-----------+-----------------+----------------+
| email_address      | first_name | last_name | home_phone_area | home_phone_num |
+--------------------+------------+-----------+-----------------+----------------+
| [e-mail] | Jianhua    | He        | 425             | 3584396        |
+--------------------+------------+-----------+-----------------+----------------+
1 row in set (0.00 sec)

I need to union the above 2 queries,but my trial failed:

mysql> select job_desc_title from postings where id=194582
->                           union all
->                     select email_address,first_name,last_name,home_phone_area,home_phone_num from accounts
->                      left join profiles on profiles.account_id=accounts.id
->                      where accounts.id=5;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

What's the right version to do this job?

解决方案

You're selecting job_desc_title form the first one and then email address, first name, last name, etc etc from the second one. This is not a union.

What you're looking to do is a join, and I suggest you read up on these. A union takes the results of two queries and combines them vertically. A join takes the results of two tables and combines them horizontally. Unions add rows, joins add columns. What you're trying to do is add a column (job_desc_title), not rows. Combining rows (i.e.-a union) takes the same columns to work.

I also think that you're using a left join when you should be using an inner join.

select 
    a.email_address,
    a.first_name,
    a.last_name,
    a.home_phone_area,
    a.home_phone_num,
    post.job_desc_title
from 
    accounts a
    inner join profiles p on 
        a.id=p.account_id
    inner join postings post on
        --I have no idea what the relationship is here, so I'm guessing
        p.posting_id = post.id
where 
    a.id=5

Hopefully this will get you somewhere near the right track.

这篇关于mysql联合问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！