MySQL 链接 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7324845/
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
Chaining SQL queries
提问by Charles
Right now I'm running
现在我正在跑步
SELECT formula('FOO') FROM table1
WHERE table1.foo = 'FOO' && table1.bar = 'BAR';
but I would like to run this not on the constant FOObut on each value from the query
但我想不是在常量上FOO而是在查询中的每个值上运行它
SELECT foos FROM table2
WHERE table2.bar = 'BAR';
How can I do this?
我怎样才能做到这一点？
Edit:Important change: added FOO to the arguments of function.
编辑：重要更改：将 FOO 添加到函数的参数中。
Illustration:
插图：
SELECT foo FROM table1 WHERE foo = 'FOO' && table1.bar = 'BAR';
gives a column with FOOa, FOOb, FOOc.
给出带有 FOOa、FOOb、FOOc 的列。
SELECT formula('FOO') FROM table1
WHERE table1.foo = 'FOO' && table1.bar = 'BAR';
gives a single entry, say sum(FOO) (actually much more complicated, but it uses aggregates at some point to combine the results).
给出一个条目，比如 sum(FOO) （实际上要复杂得多，但它在某些时候使用聚合来组合结果）。
I want some query which gives a column with sum(FOO1), sum(FOO2), ... where each FOOn is computed in like manner to FOO. But I'd like to do this with one query rather than nqueries (because nmay be large and in any case the particular values of FOOn vary from case to case).
我想要一些查询，它给出一个包含 sum(FOO1)、sum(FOO2)、...的列，其中每个 FOOn 的计算方式与 FOO 类似。但是我想用一个查询而不是n 个查询来做到这一点（因为n可能很大，而且在任何情况下 FOOn 的特定值因情况而异）。
回答by Brian Glaz
Try this one:
试试这个：
SELECT formula FROM table1
WHERE table1.foo IN(SELECT foos FROM table2
WHERE table2.bar = 'BAR';
) AND table1.bar = 'BAR';
回答by Travesty3
SELECT formula FROM table1 WHERE bar = 'BAR' AND foo IN (SELECT foos FROM table2 WHERE bar = 'BAR');
EDIT:
编辑：
This isn't tested, but perhaps this will work?
这没有经过测试，但也许这会奏效？
SELECT formula(q1.foo) FROM table1 INNER JOIN (SELECT foo, bar FROM table2) q1 ON table1.foo = q1.foo WHERE table1.bar = 'BAR';
回答by Johan
You will have to use dynamic SQL statements.
The way this works is that you just string the SQL statement together using parameters.
您将不得不使用动态 SQL 语句。
其工作方式是您只需使用参数将 SQL 语句串在一起。
You will need to be careful though not to allow users to feed that data, because escaping cannot protect you against SQL-injection. You will need to check every column name against a whitelist.
尽管不允许用户提供该数据，但您需要小心，因为转义不能保护您免受 SQL 注入。您需要根据白名单检查每个列名。
Here's example code in a stored procedure.
下面是存储过程中的示例代码。
The way this works, is that you have a (temporary) table with column names and the stored procedure builds this into a query:
它的工作方式是，您有一个带有列名的（临时）表，并且存储过程将其构建到查询中：
dynamic /*holds variable parts of an SQL statement
-----------
id integer PK
column_name varchar(255)
operation ENUM('what','from','where','group by','having','order by','limit')
function_name varchar(255) /*function desc with a '@' placeholder where */
/* the column-name goes */
whitelist /*holds all allowed column names*/
-----------
id integer PK
allowed varchar(255) /*allowed column of table name*/
item ENUM('column','table')
Dynamic SQL stored procedure. Expects two tables: dynamicand whitelistto be prefilled.
动态 SQL 存储过程。需要两个表：dynamic和whitelist预填充。
DELIMITER $$
CREATE PROCEDURE dynamic_example;
BEGIN
DECLARE vwhat VARCHAR(65000);
DECLARE vfrom VARCHAR(65000);
DECLARE vwhere VARCHAR(65000);
DECLARE vQuery VARCHAR(65000);
SELECT group_concat(REPLACE(function_name,'@',column_name)) INTO vwhat
FROM dynamic
INNER JOIN whitelist wl ON (wl.allowed LIKE column_name
AND wl.item = 'column')
WHERE operation = 'what' AND
SELECT group_concat(REPLACE(function_name,'@',column_name)) INTO vfrom
FROM dynamic
INNER JOIN whitelist wl ON (wl.allowed LIKE column_name
AND wl.item = 'table')
WHERE operation = 'from';
SELECT group_concat(REPLACE(function_name,'@',column_name)) INTO vwhere
FROM dynamic
INNER JOIN whitelist wl ON (wl.allowed LIKE column_name
AND wl.item = 'column')
WHERE operation = 'where';
IF vwhat IS NULL THEN SET vwhat = ' * ';
IF vwhere IS NOT NULL THEN SET vwhere = CONCAT(' WHERE ',vwhere); END IF;
SET vQuery = CONCAT(' SELECT ',vwhat,' FROM ',vfrom,IFNULL(vwhere,''));
PREPARE dSQL FROM vQuery;
EXECUTE dSQL;
DEALLOCATE PREPARE dSQL;
END $$
DELIMITER ;
回答by bfavaretto
Try:
尝试：
SELECT formula FROM table1, table2
WHERE table1.foo = table2.foo AND table1.bar = table2.bar;
