MySQL 在没有连接的情况下从多个表中选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12095109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select from multiple tables without a join?
提问by Nicholas Hazen
What is the easiest way to select data from two tables and rather than join them, have them appear as separate rows. Both tables have similar or matching fields and I want to run some aggregate function on them such as avg all the rows that occurred in the same month, from both tables.
从两个表中选择数据而不是连接它们，让它们显示为单独的行的最简单方法是什么。两个表都有相似或匹配的字段，我想对它们运行一些聚合函数，例如对两个表中同一个月发生的所有行进行平均。
for example I have two tables, one that is shows transactions from one system and another with transactions from a different system. Is there a way to grab all the transactions from both tables as separate rows? if table 1 had twenty records and table 2 have thirty records, I'd like there to be 50 rows on the return.
例如，我有两个表，一个显示来自一个系统的事务，另一个显示来自不同系统的事务。有没有办法将两个表中的所有事务作为单独的行获取？如果表 1 有 20 条记录，表 2 有 30 条记录，我希望返回有 50 行。
回答by Marco
You could try something like this:
你可以尝试这样的事情：
SELECT f1,f2,f3 FROM table1
UNION
SELECT f1,f2,f3 FROM table2
回答by spencer7593
The UNION ALLoperator may be what you are looking for.
该UNION ALL运营商可能是你在找什么。
With this operator, you can concatenate the resultsets from multiple queries together, preserving all of the rows from each. Note that a UNIONoperator (without the ALLkeyword) will eliminate any "duplicate" rows which exist in the resultset. The UNION ALLoperator preserves all of the rows from each query (and will likely perform better since it doesn't have the overhead of performing the duplicate check and removal operation).
使用此运算符，您可以将多个查询的结果集连接在一起，保留每个查询的所有行。请注意，UNION运算符（不带ALL关键字）将消除结果集中存在的任何“重复”行。该UNION ALL运算符保留每个查询中的所有行（并且可能会表现得更好，因为它没有执行重复检查和删除操作的开销）。
The number of columns and data type of each column must match in each of the queries. If one of the queries has more columns than the other, we sometimes include dummy expressions in the other query to make the columns and datatypes "match". Often, it's helpful to include an expression (an extra column) in the SELECT list of each query that returns a literal, to reveal which of the queries was the "source" of the row.
每个查询中的列数和每列的数据类型必须匹配。如果其中一个查询的列数多于另一个，我们有时会在另一个查询中包含虚拟表达式以使列和数据类型“匹配”。通常，在每个返回文字的查询的 SELECT 列表中包含一个表达式（一个额外的列）会很有帮助，以揭示哪个查询是该行的“源”。
SELECT 'q1' AS source, a, b, c, d FROM t1 WHERE ...
UNION ALL
SELECT 'q2', t2.fee, t2.fi, t2.fo, 'fum' FROM t2 JOIN t3 ON ...
UNION ALL
SELECT 'q3', '1', '2', buckle, my_shoe FROM t4
You can wrap a query like this in a set of parenthesis, and use it as an inline view (or "derived table", in MySQL lingo), so that you can perform aggregate operations on all of the rows.
您可以将这样的查询包装在一组括号中，并将其用作内联视图（或 MySQL 行话中的“派生表”），以便您可以对所有行执行聚合操作。
SELECT t.a
, SUM(t.b)
, AVG(t.c)
FROM (
SELECT 'q1' AS source, a, b, c, d FROM t1
UNION ALL
SELECT 'q2', t2.fee, t2.fi, t2.fo, 'fum' FROM t2
) t
GROUP BY t.a
ORDER BY t.a
回答by wojciechz
You could try this notattion:
你可以试试这个符号：
SELECT * from table1,table2
More complicated one :
更复杂的一个：
SELECT table1.field1,table1.field2, table2.field3,table2.field8 from table1,table2 where table1.field2 = something and table2.field3 = somethingelse
回答by Vinay Kumar
If your question was this -- Select ename, dname FROM emp, dept without using joins..
如果您的问题是这样 - 选择 ename, dname FROM emp, dept 而不使用连接..
Then, I would do this...
那么，我会这样做...
SELECT ename, (SELECT dname
FROM dept
WHERE dept.deptno=emp.deptno)dname
FROM EMP
Output:
输出：
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
14 rows selected.
回答by Bhaskar Dutt Sharma
You should try this
你应该试试这个
SELECT t1.*,t2.* FROM t1,t2
回答by kontashi35
Union will fetch data by row not column,So If your are like me who is looking for fetching column data from two different table with no relation and without join.
In my case I am fetching state name and country name by id. Instead of writing two query you can do this way.
Union 将按行而不是列获取数据，所以如果您像我一样正在寻找从两个没有关系且没有连接的不同表中获取列数据。
就我而言，我通过 id 获取州名和国家名。您可以这样做，而不是编写两个查询。
select
(
select s.state_name from state s where s.state_id=3
) statename,
(
select c.description from country c where c.id=5
) countryname
from dual;
where dual is a dummy table with single column--anything just require table to view
其中 dual 是一个带有单列的虚拟表——任何东西都只需要表来查看
回答by Fred Ondieki
In this case we are assuming that we have two tables:
SMPPMsgLogand SMSServicewith common column serviceid:
在这种情况下，我们假设我们有两个表：
SMPPMsgLog并且SMSService具有公共列serviceid：
SELECT sp.SMS,ss.CMD
FROM vas.SMPPMsgLog AS sp,vas.SMSService AS ss
WHERE sp.serviceid=5431
AND ss.ServiceID = 5431
AND Receiver ="232700000"
AND date(TimeStamp) <='2013-08-07'
AND date(TimeStamp) >='2013-08-06' \G;
