主页 / user-192442

Dave's questions

Martin Hope
Dave
Asked: 2019-10-06 12:27:45 +0800 CST
  • 0

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

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

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