我正在尝试通过SC_FRAMES.GROUPPRODUCTTYPE将一个组添加到此语句:
SELECT
SC_JOBS.CREATIONDATE,
(SELECT SUM(SC_JOBS.GROSSEXCLVAT) FROM SC_JOBS WHERE SC_FRAMES.GROUPPRODUCTTYPE = 'ABC' AND SC_FRAMES.JOBID = SC_JOBS.JOBSID AND SC_JOBS.INVOICEDATE < '1990-01-01') AS Product1,
(SELECT SUM(SC_JOBS.GROSSEXCLVAT) FROM SC_JOBS WHERE SC_FRAMES.GROUPPRODUCTTYPE = 'XYZ' AND SC_FRAMES.JOBID = SC_JOBS.JOBSID AND SC_JOBS.INVOICEDATE < '1990-01-01') AS Product2
FROM
SC_JOBS
INNER JOIN
SC_FRAMES ON SC_FRAMES.JOBID = SC_JOBS.JOBSID
WHERE
SC_JOBS.CREATIONDATE BETWEEN :StartDate AND :EndDate
ORDER BY
SC_JOBS.CREATIONDATE
有什么建议吗?
最佳答案
我认为您想要这样的查询:
SELECT f.GROUPPRODUCTTYPE,
MIN(j.CREATIONDATE),
SUM(case when j.INVOICEDATE < '1990-01-01' then j.GROSSEXCLVAT else 0
end) as Product1
FROM SC_JOBS INNER j JOIN
SC_FRAMES f
ON f.JOBID = j.JOBSID
WHERE j.CREATIONDATE BETWEEN :StartDate AND :EndDate
GROUP BY f.GROUPPRODUCTTYPE
ORDER BY min(j.CREATIONDATE);
它根据发票日期用条件汇总替换子查询。
关于mysql - 如何使用具有多个子查询的sql分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18892081/