基于列值的MySQL内部联接表 [英] MySQL Inner Join table based on column value
问题描述
假设我有一个具有以下结构的表"stats":
tableName | id | pageViews
tableName列对应于数据库中的单独表.
Suppose I have a table 'stats' with the following structure:
tableName | id | pageViews
The tableName column corresponds to separate tables in the database.
对"stats"运行查询时,对tableName
列结果进行内部联接以获取每个表的数据的最佳方法是什么?
我当时正在考虑在foreach中运行动态选择,然后合并结果.例如:
When running a query against "stats", what would be the best way to inner join against the tableName
column result to get each table's data?
I was thinking of running dynamic selects in a foreach and then merging the results. E.g.:
foreach($tableNames as $tableName) {
$sql = "SELECT *
FROM stats s
INNER JOIN $tableName tbl ON s.id = tbl.id
WHERE tableName = '$tableName'";
}
推荐答案
要获取所有表的统计信息,可以使用具有2个或更多选择的UNION,每个表一个:
To have all tables' statistics, you can use a UNION, with 2 or more selects, one for each table:
( SELECT s.*
, table1.title AS name --or whatever field you want to show
FROM stats s
JOIN $tableName1 table1
ON s.id = table1.id
WHERE tableName = '$tableName1'
)
UNION ALL
( SELECT s.*
, table2.name AS name --or whatever field you want to show
FROM stats s
JOIN $tableName2 table2
ON s.id = table2.id
WHERE tableName = '$tableName2'
)
UNION ALL
( SELECT s.*
, table3.lastname AS name --or whatever field you want to show
FROM stats s
JOIN $tableName3 table3
ON s.id = table3.id
WHERE tableName = '$tableName3'
)
;
在LEFT JOIN
中使用Winfred的想法.它产生不同的结果,例如其他表中的每个字段都在其自己的列中输出(并且会出现许多NULL).
Using Winfred's idea with LEFT JOIN
s. It produces different results, e.g. every field from the other tables is output in it's own column (and many NULLs occur).
SELECT s.*
, table1.title --or whatever fields you want to show
, table2.name
, table3.lastname --etc
FROM stats s
LEFT JOIN $tableName1 table1
ON s.id = table1.id
AND s.tableName = '$tableName1'
LEFT JOIN $tableName2 table2
ON s.id = table2.id
AND s.tableName = '$tableName2'
LEFT JOIN $tableName3 table3
ON s.id = table3.id
AND s.tableName = '$tableName3'
--this is to ensure that omited tables statistics don't appear
WHERE s.tablename IN
( '$tableName1'
, '$tableName2'
, '$tableName3'
)
;
这篇关于基于列值的MySQL内部联接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!