在SQL Server中按二级分组 [英] Two level group by in sql server

问题描述

我有一个查询，用于检索从工厂收到的零件装运清单.收据栏存储为当天的总收据，但一天中有多次导入.这导致我们当前的查询多次报告一些发货.(我不会更改数据在数据库中的存储方式，而不会引起其他报告的重大问题)

I have a query that retrieves a list of received part shipments from plants. The receipt column is stored as total receipts for the day but there are multiple imports through out the day. This is causing our current query to report some shipments several times. (I can't change how the data is stored in the database without causing major issues with other reports)

当前查询:

    select ppl.plant_id, ppl.part_id, sum(pol.receipt) reciept
        from purchase_order_levels pol
    join plant_part_levels ppl
        on ppl.id = pol.plant_part_level_id
    join imports im
        on im.id = ppl.import_id
    where im.active = 1 and im.level_date <= '12-17-2010 10:26'
    group by ppl.plant_id, ppl.part_id

我试图从一天结束时只检索收据级别

My attempt to retrieve only receipt levels from the end of the day

    select ppl.plant_id, ppl.part_id, DATEADD(dd, DATEDIFF(dd,0,ppl.level_date), 0) as level_date, max(pol.receipt) reciept
        from mc_cup_purchase_order_levels pol
    join mc_cup_plant_part_levels ppl
        on ppl.id = pol.plant_part_level_id
    join mc_cup_imports im
        on im.id = ppl.import_id
    where im.active = 1 and im.level_date <= '12-17-2010 10:26'
    group by ppl.plant_id, ppl.part_id, DATEADD(dd, DATEDIFF(dd,0,ppl.level_date), 0)

上面的查询有效，但是当我尝试得到语法错误时，我需要添加额外的分组级别以汇总收据.似乎不喜欢像表一样对待子查询.

The above query works but I need to add an additional level of grouping to sum up the receipts when I tried doing that I got a syntax error. It didn't seem to like treating a subquery like a table.

select plant_id, part_id, sum(pol.receipt) from
(
        select ppl.plant_id, ppl.part_id, DATEADD(dd, DATEDIFF(dd,0,ppl.level_date), 0) as level_date, max(pol.receipt) reciept
            from mc_cup_purchase_order_levels pol
        join mc_cup_plant_part_levels ppl
            on ppl.id = pol.plant_part_level_id
        join mc_cup_imports im
            on im.id = ppl.import_id
        where im.active = 1 and im.level_date <= '12-17-2010 10:26'
        group by ppl.plant_id, ppl.part_id, DATEADD(dd, DATEDIFF(dd,0,ppl.level_date), 0)
)
group by ppl.plant_id, ppl.part_id

在数据上添加第二级分组的正确方法是什么?我试图将这种逻辑保留在数据库中，所以不必重写应用程序以对内存中的数据求和.

What is the proper way to add a second level of grouping on my data? I'm trying to keep this logic in the database so I don't have to rewrite the application to sum the data in memory.

推荐答案

使用公用表表达式(CTE):

Use a Common Table Expression (CTE):

;WITH My_CTE (plant_id, part_id, level_date, receipt) AS
(
select ppl.plant_id, ppl.part_id, DATEADD(dd, DATEDIFF(dd,0,ppl.level_date), 0) as level_date, max(pol.receipt) reciept
                from mc_cup_purchase_order_levels pol
            join mc_cup_plant_part_levels ppl
                on ppl.id = pol.plant_part_level_id
            join mc_cup_imports im
                on im.id = ppl.import_id
            where im.active = 1 and im.level_date <= '12-17-2010 10:26'
            group by ppl.plant_id, ppl.part_id, DATEADD(dd, DATEDIFF(dd,0,ppl.level_date), 0)
    )
select plant_id, part_id, sum(receipt) from My_CTE
group by plant_id, part_id

这篇关于在SQL Server中按二级分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！