MySQL : Fetch records from multiple table by arithmetic operations

Question

我有以下表格:

table_1
------------------
id      uid     category    balance 
1       1       A           100 
2       2       B           80

table_2
------------------
id      uid     name
1       1       ABC
2       2       XYZ

table_2
------------------
id      uid     last_pay
1       1       10
2       1       10
3       1       10
4       2       80

我想从三个表中抓取记录，条件如下:

a) table_1.category = something
b) ( table_1.balance - table_3.SUM(`last_pay`) ) > 0

我想要 table_1.category = 'A' 因为 (100 - (10 + 10 + 10)) > 0

尝试这个查询但它不工作:

SELECT t1.uid,t1.category,t1.balance,t2.uid,t2.name FROM table_1 as t1 
LEFT JOIN table_2 as t2 ON t1.uid = t2.uid
WHERE t1.category = 2
AND t1.balance - (SELECT SUM(`last_pay`) FROM table_3 WHERE uid = ? )

Answer 1

好的，我用规范化表格给你一个完整的答案:

您可以规范化 table_2 并将列 name 插入到表 table_1 中。请参阅以下新表结构(使用 table_1 和 table_2):

table_1
--------------------------------------------
id      uid     category    balance     name
1       1       A           100         ABC
2       2       B           80          XYZ

table_2
------------------------
id      uid     last_pay
1       1       10
2       1       10
3       1       10
4       2       80

要创建这些表，您可以使用以下脚本:

CREATE TABLE table_1 (
    `id` INT, 
    `uid` INT, 
    `category` VARCHAR(1), 
    `balance` INT,
    `name` VARCHAR(3)
);

INSERT INTO table_1 VALUES
    (1, 1, 'A', 100, 'ABC'),
    (2, 2, 'B', 80, 'XYZ');

CREATE TABLE table_2 (
    `id` INT, 
    `uid` INT, 
    `last_pay` INT
);

INSERT INTO table_2 VALUES
    (1, 1, 10),
    (2, 1, 10),
    (3, 1, 10),
    (4, 2, 80);

获取预期结果的查询:

SELECT t1.uid, t1.category, t1.balance, t2.uid, t1.name
FROM table_1 t1 
   LEFT JOIN (
       SELECT uid, SUM(last_pay) AS last_pay 
       FROM table_2
       GROUP BY uid
    ) t2 ON t1.uid = t2.uid
WHERE (t1.balance - t2.last_pay) > 0

You can find a working example here: http://sqlfiddle.com/#!9/a2e27/3/0

您想使用您原来的表格吗？ [原始问题的答案]

如果可能，我建议对表格进行规范化!如果无法更改表结构，您可以使用以下查询来获得预期结果:

SELECT t1.uid, t1.category, t1.balance, t2.uid, t2.name
FROM table_1 AS t1 LEFT JOIN table_2 AS t2 ON t1.uid = t2.uid
    LEFT JOIN (
        SELECT uid, SUM(last_pay) AS last_pay 
        FROM table_3 
        GROUP BY uid
    ) t3 ON t1.uid = t3.uid
WHERE (t1.balance - t3.last_pay) > 0

You can find a working example here: http://sqlfiddle.com/#!9/f22024/7/0