MySQL 使用的 SELECT 语句具有不同的列数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3622072/
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
The used SELECT statements have a different number of columns
提问by good_evening
For examples I don't know how many rows in each table are and I try to do like this:
例如，我不知道每个表中有多少行，我尝试这样做：
SELECT * FROM members
UNION
SELECT * FROM inventory
What can I put to the second SELECTinstead of * to remove this error without adding NULL's?
在SELECT不添加NULL's 的情况下，我可以将什么放在第二个而不是 * 来消除此错误？
采纳答案by RedFilter
Put the columns names explicitly rather than *, and make sure the number of columns and data types match for the same column in each select.
明确输入列名称而不是 *，并确保每个选择中的同一列的列数和数据类型匹配。
Update:
更新：
I really don't think you want to be UNIONing those tables, based on the tables names. They don't seem to contain related data. If you post your schema and describe what you are trying to achieve it is likely we can provide better help.
我真的不认为你想根据表名联合这些表。它们似乎不包含相关数据。如果您发布您的架构并描述您要实现的目标，我们可能会提供更好的帮助。
回答by FrustratedWithFormsDesigner
you could do
你可以
SELECT *
from members
UNION
SELECT inventory.*, 'dummy1' AS membersCol1, 'dummy2' AS membersCol2
from inventory;
Where membersCol1, membersCol12, etc... are the names of columns from membersthat are not in inventory. That way both queries in the union will have the same columns (Assuming that all the columns in inventoryare the same as in memberswhich seems very strange to me... but hey, it's your schema).
其中membersCol1、membersCol12等...members是不在 中的列的名称inventory。这样，联合中的两个查询都将具有相同的列（假设其中的所有列inventory都与members其中的列相同，这对我来说似乎很奇怪……但是，嘿，这是您的架构）。
UPDATE:
更新：
As HLGEM pointed out, this will onlywork if inventoryhas columns with the same names as members, and in the same order. Naming all the columns explicitly is the best idea, but since I don't know the names I can't exactly do that. If I did, it might look something like this:
正如 HLGEM 所指出的，这仅在inventory具有与 同名members且顺序相同的列时才有效。明确命名所有列是最好的主意，但由于我不知道名称，因此我无法完全做到这一点。如果我这样做了，它可能看起来像这样：
SELECT id, name, member_role, member_type
from members
UNION
SELECT id, name, '(dummy for union)' AS member_role, '(dummy for union)' AS member_type
from inventory;
I don't like using NULL for dummy values because then it's not always clear which part of the union a record came from - using 'dummy' makes it clear that the record is from the part of the union that didn't have that record (though sometimes this might not matter). The very idea of unioning these two tables seems very strange to me because I very much doubt they'd have more than 1 or 2 columns with the same name, but you asked the question in such a way that I imagine in your scenario this somehow makes sense.
我不喜欢将 NULL 用于虚拟值，因为这样并不总是清楚记录来自联合的哪个部分 - 使用“虚拟”可以清楚地表明该记录来自没有该记录的联合部分（尽管有时这可能无关紧要）。联合这两个表的想法对我来说似乎很奇怪，因为我非常怀疑它们会有超过 1 或 2 个同名的列，但是你问这个问题的方式是我在你的场景中想象的说得通。
回答by HLGEM
Are you sure you don't want a join instead? It is unlikely that UNOIN will give you what you want given the table names.
你确定你不想加入吗？鉴于表名，UNOIN 不太可能给你你想要的东西。
回答by ErikE
I don't know how many rows in each table
我不知道每个表有多少行
Are you sure this isn't what you want?
你确定这不是你想要的吗？
SELECT 'members' AS TableName, Count(*) AS Cnt FROM members
UNION ALL
SELECT 'inventory', Count(*) FROM inventory
回答by user9862225
Each SELECT statement within the MySQL UNION ALL operator must have the same number of fields in the result sets with similar data types Visit https://www.techonthenet.com/mysql/union_all.php
MySQL UNION ALL 运算符中的每个 SELECT 语句必须具有相同数据类型的结果集中相同数量的字段访问https://www.techonthenet.com/mysql/union_all.php
