mysql - WITH ROLLUP 不计算某些列的总计

Question

我有一个 SQL 语句，它给出了工作债权人的列表。在图像中的示例中，两个债权人都属于同一份工作。所以在显示 TOTALS 的最后一行，作业数是 1，这是正确的，因为它是同一个作业。同样，表示 NotPOD 的列也是正确的，仅表示 1。但对于所有其他列，它正在添加总计。就像写着 FL 的专栏(它是一 block 类型)。此作业只有 1 个 FL 件类型。但由于该工作有两个债权人，即使只有一个债权人，总数也显示为两个。

我不知道作业和 NotPOD 的总数是如何正确的。

这是sql语句:

SET group_concat_max_len=10000000;
set @sql = null;
select
  group_concat(distinct
    concat(
       'sum((select sum(jp1.AdvisedQty)
                from job_pieces jp1
                left join piecestype pt1 on jp1.TypeID = pt1.ID
                where jp1.jobid=jn.id and pt1.code=''', pt.Code , '''
            ))  AS `', pt.Code, '`'
    )
  ) into @sql
FROM creditor c
LEFT JOIN invoicedetail i on c.ID = i.creditorId
LEFT JOIN job_new jn ON i.JobID = jn.ID
LEFT JOIN job_pieces jp ON jp.JobID = jn.ID
LEFT JOIN piecestype pt on jp.TypeID = pt.ID
WHERE c.Company_ID = ?compid;

set @sql = concat('select 
        ifnull(c.Name,''TOTAL'') as Name,
        COUNT(distinct jn.ID) as Jobs,
        sum(case when (select count(jbs.status) from jobstat jbs
                where jbs.jobid=jn.id and jbs.Status=''DEL'')>0
                      then 1 else 0 end) as Delivered,
        sum(case when (select count(jbs.status) from jobstat jbs
                where jbs.jobid=jn.id and jbs.Status=''POD'')>0
                      then 1 else 0 end) as POD,
        SUM((select COUNT(ID) from job_debriefs
                WHERE JobID = jn.ID)) as Debriefs,
        sum(jn.OutTurn) as Outturn,
        SUM(jn.ActualWeight) as GrossWt,
        ', @sql, ' 
    FROM creditor c
    LEFT JOIN invoicedetail i on c.ID = i.creditorId
    LEFT JOIN job_new jn ON i.JobID = jn.ID
    LEFT JOIN job_pieces jp ON jp.JobID = jn.ID
    LEFT JOIN piecestype pt on jp.TypeID = pt.ID
    WHERE c.Company_ID = ', ?compid, GROUP BY c.Name WITH ROLLUP

Answer 1

当您使用 count(distinct) 和 sum 时，with rollup 标志会做不同的事情。

对于COUNT(distinct jn.ID)，它将为您提供所有显示数据中不同Jobids 的数量。这可能就是您想要的。

对于像 sum(case...) as POD 这样的总和列，它只会为您提供所有显示值的总和 - 如果作业具有状态“POD”。

至于 NotPOD-Column:我假设您只是在您的客户端中计算它(可能作为 Jobs - POD)，并且由于 POD 为 0(虽然可能是 2)，它看起来不错。我假设您也在您的客户中计算“单位”(作为 4 种类型的总和)。如果不是，则您的代码不适合您的图片。

那么怎么解决呢？

嗯，基本上你不能使用with rollup，主要是因为你不想得到显示值的总和，这就是with rollup > 是为了。

最简单的解决方案是手动计算总行并将它们与 union 合并，例如像这样:

select c.Name as Name,
    COUNT(distinct jn.ID) as Jobs,
    ...
    sum(jn.OutTurn) as Outturn,
    SUM(jn.ActualWeight) as GrossWt,
    ...
    FROM creditor c
    LEFT JOIN invoicedetail i on c.ID = i.creditorId
    LEFT JOIN job_new jn ON i.JobID = jn.ID
    -- !!! DO NOT USE LEFT JOIN job_pieces jp ON jp.JobID = jn.ID !!!
    -- !!! DO NOT USE LEFT JOIN piecestype pt on jp.TypeID = pt.ID !!!
    WHERE c.Company_ID = ', ?compid, GROUP BY c.Name, '   
UNION
    ''Total'',
    COUNT(distinct jn.ID) as Jobs,
    ...
    sum(jn.OutTurn) as Outturn,
    SUM(jn.ActualWeight) as GrossWt,
    ...
    -- you have to figure out what number you need here for the pieces
    -- you probably want to seperate that part too (as you did with @sql)
    -- (select sum(jp1.AdvisedQty) from job_pieces jp1 ... )  as FL
    ... 
    from job_new jn 
    WHERE exists(select 1 
                 FROM creditor c
                 JOIN invoicedetail i on c.ID = i.creditorId
                 where i.JobID = jn.ID and c.Company_ID = ', ?compid, '
                ) 

第二部分没有group by!你必须弄清楚你想要的每件总和的值，你可能想用类似于你的 select 的语句来准备它们
group_concat(不同的...

还有一个警告:如果我正确理解你的数据模型，你绝对必须删除

LEFT JOIN job_pieces jp ON jp.JobID = jn.ID
LEFT JOIN piecestype pt on jp.TypeID = pt.ID

语句，因为当您在一份工作中有多个工作件或件类型时，它们会破坏您的值(value)观(您可以尝试向工作添加第二个值(value)并查看)。如果您有可能在一个 invoicedetail 上有两次相同的工作，您也必须修复该部分。

请注意，在联合部分，您不能加入客户(您会多次计算所有内容)。