主页 / dba / 问题 / 250338
Accepted
Dave
Asked: 2019-10-06 12:27:45 +0800 CST

MYSQL 返回一个字段的总和等于另一个表字段的行

  • 772

我有一张工作付款表，当与交易匹配时，需要为同一客户匹配当天的付款，这等于当天的单笔交易。我已经能够匹配，但只返回汇总总数，而不是每一行。

我知道需要加入，但无法正确加入。

D B：

create table jobs(
  jobid int(7),
  datein date,
  total numeric(5,2),
  chargeto int(5)
);
create table payments(
  payid int(7),
  paymentamount numeric(5,2),
  paymentjobno int(5),
  paymentdate date,
  paymenttype int(1)
);

insert into jobs (jobid, total) values (1000, 100, 4);
insert into jobs (jobid, total) values (1001, 50, 4);
insert into jobs (jobid, total) values (1002, 25, 4);
insert into jobs (jobid, total) values (1003, 220, 6);
insert into jobs (jobid, total) values (1004, 200, 7);

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 25, 1002, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 200, 1004, 01/10/2019, 1);

我有代码：

$groupmatches = $con->query("
SELECT j1.*, j2.*
FROM jobs AS j1
JOIN (SELECT jobs.jobid, payments.paymentamount, payments.paymentjobno, COUNT(*) AS count
  FROM (jobs LEFT JOIN payments ON payments.paymentjobno = jobs.jobid)
  WHERE payments.paymentdate = '$dt' AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)
  GROUP BY jobs.chargeto
  HAVING SUM(payments.paymentamount) = '$at' AND COUNT(*) > 1) AS j2
ON j1.jobid = j2.paymentjobno;
");

它正在匹配并返回一行，其中包含多少行加起来匹配的计数值，但我无法返回每一行。我究竟做错了什么？

mysql sum

2 个回答

  1. 我想我已经理解了你的问题——我是这样解决的（见这里的小提琴）：

    您的数据在上面的示例中不太正确。我稍微修改了一下：

    工作表：

    insert into jobs values (1000, '2019-10-01', 100, 4);
insert into jobs values (1001, '2019-10-01', 50, 4);
insert into jobs values (1002, '2019-10-01', 25, 4);
insert into jobs values (1003, '2019-10-01', 220, 6);
insert into jobs values (1004, '2019-10-01', 200, 7);

    datein和是相同的paymentjobdate-您问题的当天付款：-need to match up payments on the same day for the same customer that equal a single transaction for that day.

    付款表：

    insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);

    作业 1000 - 请注意，有 4 个单独的付款 25 = jobs.total 是 100，所以SUM匹配。

    insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, '2019-10-01', 1);

    工作 1001，一笔与总额相符的付款。

    insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 10, 1002, '2019-10-01', 1);

    工作 1002 - 总数为 25，但付款仅为 10 - 不匹配。

    insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, '2019-10-01', 1);

    jobid 1003的单一匹配付款。

    insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 100, 1004, '2019-10-01', 1);

    最后，工作 1004 - 总额为 200，付款仅为 100 - 与 不匹配SUM。

    然后，我执行了以下 SQL（第一步）：

    SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
FROM payments p
-- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
-- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
GROUP BY p.paymentjobno;

    结果：

    p_jobid the_sum
1000    100.00
1001    50.00
1002    10.00
1003    220.00
1004    100.00

    现在我同时拥有 jobid和SUM，所以现在可以使用它作为子查询加入 jobid和那个 SUM上的付款。查询语句：

    SELECT j.*, p.*  -- choose your fields as required!
FROM jobs j 
JOIN
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p
  -- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
  -- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
  GROUP BY p.paymentjobno;    
) AS t1
  ON j.jobid = t1.p_jobid 
AND j.total = t1.the_sum
JOIN payments p
ON j.jobid = p.paymentjobno;

    结果：

    jobid   datein  total   chargeto    payid   paymentamount   paymentjobno    paymentdate paymenttype
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1001    2019-10-01  50.00   4   2   50.00   1001    2019-10-01  1
1003    2019-10-01  220.00  6   4   220.00  1003    2019-10-01  1


    也就是说，如果我没听错的话，就是你要找的东西。您在 jobid 1000 的 4 笔付款总额以及 jobid 1001 和 1003 的单笔付款总额中匹配，但在 1002 和 1004 当天尚未全额付款的情况下不匹配！

    如果这不符合您的要求，请告诉我，我会尽力解决。我可以建议您使用下划线，即 python_case_for_variables 将您的变量（表名和列名）分隔成清晰的部分，并对 SQL 使用大写 - 它使事情更容易阅读 - 如果你不喜欢，搜索“SQL编码标准”，找到一个有吸引力的标准，然后坚持下去！我还可以建议您对表格使用单数名称 - 表格是一组东西！

    最后，请始终在您的问题中包含一个版本号 - 特别是对于 MySQL，它可以产生巨大的不同！此外，可以极大帮助的是一个有效的小提琴 - 像这样的事情可以快速为您提供高质量的答案！帮助我们帮助你。我的个人资料中有几篇关于如何在此处提问的文章 - 您可能想看一看？最后，正如我在评论中所说 - 欢迎来到论坛！:-)

    根据 OP 的评论和小提琴进行编辑：

    你想要的是GROUP_CONCAT！在这里摆弄。

    查询语句：

    SELECT 
      j.jobid, 
      GROUP_CONCAT(p.paymentamount SEPARATOR ', ') AS payment_hist
FROM jobs j 
JOIN 
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p GROUP BY p.paymentjobno
) AS t1 ON 
  j.jobid = t1.p_jobid AND j.total = t1.the_sum 
JOIN payments p ON
  j.jobid = p.paymentjobno 
GROUP BY j.jobid;

    结果：

    jobid.  payment_hist
1000    25.00, 25.00, 25.00, 25.00
1001    50.00
1003    220.00

    我强烈建议您在使用 SQL 时避免与逗号分隔列表有关的任何事情。它们与关系模型相反，违反了第一范式。尽管存在 GROUP_CONCAT、 STRING_AGG和LIST_AGG等构造，但它们也很难处理。如果你打算做很多这类工作并且你有可能，我会改用 PostgreSQL——它对于这类工作要强大得多。

    虽然有一个“美中不足”！

    我希望以上内容能回答您的问题，但我不确定它是否与您自己的小提琴相对应。例如，你有

    insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, '2019-10-01', 1);

    对于 jobid 1000，你有

    insert into jobs values (1000, '2019-10-01', 100, 4);

    但是，上面的付款是与jobid 1000的全额对应的唯一一次付款，但是在您的结果集中，您有

    jobid     payment_hist
1000,     100, 175

    第一笔付款是 100，但我不知道 175 是从哪里来的——它不是来自其他付款，而且您的所有 jobid 似乎都已在一次付款中全额支付，因此没有部分付款我以为你的问题是什么？

    • 0
  2. Best Answer

    我已经（尝试）在这里回答这个问题，但我不会删除它，这样我的（诚然有时是乱码的）思维过程就很明显，并提醒我始终阅读这个问题！

    因此，在这里使用 OP 的小提琴中的数据（对我的第一个答案的评论），我首先运行了下面的 SQL - 我自己的这个（最终）答案的小提琴在这里。它适用于 MySQL 版本 5.6、5.7 和 8：

    SELECT j.chargeto AS ct, SUM(p.paymentamount) AS p_total
FROM jobs j
JOIN payments p
  ON j.jobid = p.paymentjobno
-- WHERE j.chargeto IN (1, 2, 4)  -- you have AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)
-- AND <add predicate here...> -- add as many as you like
GROUP BY j.chargeto

    结果：

    ct  p_total
4   175.00
6   220.00
7   200.00

    然后，我必须将此与每种收费类型的每次付款的付款结合起来。

    期望的结果 - 只想要 ct = 4

    1000, 100, 175
1001, 50, 175
1002, 25, 175

    ct = 4 来自：

    AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)

    我将其更改为IN (1, 2, 4)- 更具可读性。

    SELECT j.jobid, p.paymentamount, t1.p_total,
CONCAT(j.jobid, ', ', p.paymentamount, ', ', t1.p_total) AS csv  -- I advise against this!
FROM jobs j
JOIN
(
  SELECT j.chargeto AS ct, SUM(p.paymentamount) AS p_total
  FROM jobs j
  JOIN payments p
    ON j.jobid = p.paymentjobno
  WHERE j.chargeto IN (1, 2, 4)
  -- AND <add predicate here...> -- add as many as you like
  GROUP BY j.chargeto
) AS t1
ON j.chargeto = t1.ct
JOIN payments p
  ON j.jobid = p.paymentjobno

    结果：

    jobid   paymentamount   p_total     csv
1000           100.00    175.00     1000, 100.00, 175.00
1001            50.00    175.00     1001, 50.00, 175.00
1002            25.00    175.00     1002, 25.00, 175.00

    结果desired是csv列，但就我而言，desirable结果在前三个单独的字段中——恕我直言，任何类型的 .csv 字段在使用 SQL 时都是一个坏主意——请参阅我之前的回答（关于 GROUP_CONCAT、STRING_AGG 的位& LIST_AGG）。

    • 0

相关问题