mysql union不同列数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15867152/
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
mysql union different number of columns
提问by user892134
I know union queries have to have the same number of columns. I'm trying to get results from the table commentsand results from the table strings, which has multiple joins. How do i this correctly? I haven't tested yet because i know i'll get an error with different number of columns. Here are the two queries that i'm trying to combine.
我知道联合查询必须具有相同数量的列。我正在尝试从 table 中获取结果，comments并从 table 中获取结果strings，它有多个连接。我如何正确？我还没有测试，因为我知道我会收到不同列数的错误。这是我试图组合的两个查询。
query 1(strings)
查询 1（字符串）
SELECT sub.actionid as usersub,
ftable.`last-time` as lastvisited, updatetable.recent as mostrecent, parent.* FROM `strings` as parent
LEFT JOIN subscribe sub on sub.actionid=parent.id AND sub.userid=:userid
JOIN followers ftable on ((ftable.sid=parent.sid AND ftable.page='1') OR
(ftable.sid=parent.sid AND ftable.position=parent.position AND ftable.page='0')
OR (ftable.profile=parent.starter AND parent.guideline='1')) AND ftable.userid=:userid
JOIN `update-recent` updatetable on
((updatetable.sid=parent.sid AND updatetable.position=parent.position AND updatetable.pageid='0')
OR (updatetable.profile=parent.starter) OR (updatetable.pageid=parent.pageid AND parent.pageid!=0))
WHERE ftable.userid=:userid AND parent.deleted='0' GROUP BY parent.id
query 2(comments)
查询 2（评论）
SELECT * FROM comments WHERE viewed='0' AND (`starter-id`=:userid OR respondid=:userid)
I'd like to order the results by the timestamp column posted(most recent) ORDER BY posted DESC
我想按时间戳列posted（最新）对结果进行排序ORDER BY posted DESC
How do i union these queries?
我如何联合这些查询？
回答by What have you tried
You would want to select columns as NULLto take up for the empty space in certain tables.
您可能希望选择列NULL以占用某些表中的空白空间。
Table A: (id, column1)
表 A: (id, column1)
Table B: (id, column1, column2)
表 B: (id, column1, column2)
Select id, column1, null as column2 from tableA
UNION
Select id, column1, column2 from tableB
回答by Mchl
Add empty columns (null as columnName) to the query that has fewer columns. You should avoid using *in this case, to have better control on order of columns in both queries.
null as columnName向具有较少列的查询添加空列 ( )。*在这种情况下，您应该避免使用，以便更好地控制两个查询中的列顺序。
回答by Latro666
Another very dodgy way to get around the issue is to CONCAT_WS columns not shared between the tables e.g.
解决这个问题的另一种非常狡猾的方法是在表之间不共享 CONCAT_WS 列，例如
SELECT `r_id`, `r_added`, CONCAT_WS('###',`otherfield1`,`otherfield2`) as r_other FROM table1
UNION
SELECT `r_id`, `r_added`, CONCAT_WS('###',`otherfield3`,`otherfield4`,`otherfield5`) as r_other FROM table2
You can then programmatically reconstruct that data e.g.
然后您可以以编程方式重建该数据，例如
$rother = explode("###", $row['r_other']);
Very bad way to get at this databut might get someone out of a fix. You could even change the separator e.g. #t1# #t2# search the string for that to workout what table the concatenated data is from.
获取此数据的方法非常糟糕，但可能会使某人无法解决。您甚至可以更改分隔符，例如 #t1# #t2# 搜索该字符串以计算连接数据来自哪个表。
