使用mysql别名从2个表中选择列 [英] Using mysql aliases to select columns from 2 tables

问题描述

我有2个表：table_a和table_b。两者都包含一个名为 open的列。

I have 2 tables: table_a and table_b. Both contain a column named 'open'.

table_a
+-------+
| open  |
+-------+
| 36.99 |
| 36.85 |
| 36.40 |
| 36.33 |
| 36.33 |
+-------+

table_b 
+------+
| open |
+------+
| 4.27 |
| 4.46 |
| 4.38 |
| 4.22 |
| 4.18 |
+------+

我想写一个查询返回以下内容

I'd like to write a query that returns the following

+-------++------+
| open  || open |
+-------++------+
| 36.99 || 4.27 |
| 36.85 || 4.46 |
| 36.40 || 4.38 |
| 36.33 || 4.22 |
| 36.33 || 4.18 |
+-------++------+

我尝试以下查询：

select a.open, b.open from  table_a a, table_b b;

这将返回一个表，其中table_b.open的每个值对应于table_a.open的每个值

This returns a table with every value of table_b.open for each value of table_a.open

+-------++------+
| open  || open |
+-------++------+
| 36.99 || 4.27 |
| 36.99 || 4.46 |
| 36.99 || 4.38 |
| 36.99 || 4.22 |
|   ... || 4.18 |
+   ... ++------+

我可以看到我我误解了别名的正确用法。有任何想法吗？

I can see I'm misunderstanding proper usage of aliases here. Any ideas?

推荐答案

这不是别名问题。您正在执行 交叉加入 在创建笛卡尔结果集的表格上。

It is not an alias problem that you have. You are performing a CROSS JOIN on the table which creates a cartesian result set.

这会乘以结果集，因此 table_a 中的每一行都直接与 table_b 。

This multiplies your result set so every row from table_a is matched directly to every row in table_b.

如果您想一起 JOIN 个表，则需要一些列来连接表格。

If you want to JOIN the tables together, then you need some column to join the tables on.

如果您有要加入 JOIN 的列，则查询将为：

If you have a column to JOIN on, then your query will be:

select a.open as a_open,
  b.open as b_open
from table_a a
inner join table_b b
  on a.yourCol = b.yourCol

如果您没有可使用的列要加入，则可以创建一个用户定义的变量来执行此操作，该变量将为每行创建一个行号。

If you do not have a column that can be used to join on, then you can create a user-defined variable to do this which will create a row number for each row.

select 
   a.open a_open, 
   b.open b_open
from
(
  select open, a_row
  from
  (
    select open,
      @curRow := @curRow + 1 AS a_row
    from table_a
    cross join (SELECT @curRow := 0) c
  ) a
) a
inner join
(
  select open, b_row
  from 
  (
    select open,
      @curRow := @curRow + 1 AS b_row
    from table_b 
    cross join (SELECT @curRow := 0) c
  ) b
) b
  on a.a_row = b.b_row;

请参见带有演示的SQL提琴

这篇关于使用mysql别名从2个表中选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！