SQL从多个表中选择数据 [英] SQL select data from multiple tables

问题描述

我正在尝试使用mySQL从具有以下格式的多个表(10个以上)中进行选择.

I'm trying to select from multiples tables(10+) that are in the following format using mySQL.

+----------table(n)-----------+
+-----------------------------+
| url       | id        | ... |
+-----------+-----------+-----+
| url1.com  | 12345678  | ... |
| url2.com  | 45832458  | ... |

我需要做的是从给定URL的每个表中检索ID，并像这样在单行中将其返回.

What I need to do is retrieve the id from each table for a given URL and return it in a single row like so.

+--------------table(n)--------------+
+------------------------------------+
| url       | table(n)  | table(n+1) |
+-----------+-----------+------------+
| url1.com  | 12345678  | 8182735    |

但是给定表的URL可能不存在，所以我只需要从找到URL的表中检索所有ID.

But the URL might not exist for a given table, so I just need to retrieve all the ids from the tables where the URL is found.

例如，我有10个表，其中有4个URL/id.我需要在单行中检索这4个.我尝试将别名与各种JOIN组合一起用于列，但无济于事.

For example, I have 10 tables and a URL/id is in 4 of them. I need to retrieve these 4, in a single row. I've tried to use aliases for columns along with various JOIN combinations to no avail.

任何帮助将不胜感激.

推荐答案

您希望SQL语句生成可变数量的列，而MySQL无法做到这一点.

You want a variable number of columns generated by your SQL statement, and you cannot do that with MySQL.

您可以在此处查看一些解决方法: MySQL枢纽排成动态列数

You can see some techniques to get around it here: MySQL pivot row into dynamic number of columns

许多报告工具可以让您按自己的意愿做事，但是使用MySQL进行某种预备的语句破解将是唯一的方法:

Many reporting tools will let you do things like you want to, but using some sort of prepared statement hack with MySQL would be the only way to do it:

http://buysql.com/mysql/14 -how-to-automate-pivot-tables.html

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