none
sql 分组的问题? RRS feed

  • 问题

  • 我有一张资产表(简化)

    id, assetName  sumpirce Deptid 
    编号    名字            总价       部门


    我想先按 部门分组,再以部门为单位按 大于200 为界限 分组     解释 : 低值易耗品>=200、消耗品<200

    效果类似如下
    部门名称     资产分类
    --------------------------------------
    部门a       |    低值易耗品 3个(总价1200.56)
                  |       消耗品   5个 (总价963..56)
    --------------------------------------
    部门b       |    低值易耗品 6个 (总价1269.56)
                   |      消耗品   7个   (总价500.56)
    --------------------------------------          
    部门b       |    低值易耗品 8个 (总价8600.56)
                   |      消耗品   3个  (总价5306.56)      

    sql如何实现   

    2018年5月2日 14:09

答案

  • 你好,

    可以加一行来识别。

    with cte1 as
    (
    	select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number, '消耗品' as SumType from TestT 
    	where sumpirce >=200 
    	group by Deptid
    ),
    cte2 as
    (
        select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number, '低值易耗品' as SumType  from TestT 
    	where sumpirce <200 
    	group by Deptid
    )
    (select Deptid, SumPrice,Number,SumType from cte1 union all 
    select Deptid, SumPrice,Number,SumType from cte2)

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 icod 2018年5月7日 0:42
    2018年5月4日 9:53
    版主

全部回复

  • 你好,

    可以使用子查询来分别分组,在Union ALL, 请参考下面的例子。

    with cte1 as
    (
    	select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number from TestT 
    	where sumpirce >=200 
    	group by Deptid
    ),
    cte2 as
    (
        select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number  from TestT 
    	where sumpirce <200 
    	group by Deptid
    )
    (select Deptid, SumPrice,Number from cte1 union all 
    select Deptid, SumPrice,Number from cte2) order by Deptid

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年5月3日 6:21
    版主
  • 谢谢,我想把他保存成视图调用,总提示语法错误!

    create view  tz_ViewAssets as

    (

    with cte1 as
    (
    select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number from ts 
    where sumpirce >=200 
    group by Deptid
    ),
    cte2 as
    (
        select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number  from ts
    where sumpirce <200 
    group by Deptid
    )
    (
    select Deptid, SumPrice,Number from cte1 
    union all 
    select Deptid, SumPrice,Number from cte2

         order by Deptid
    )

    如何修改,谢谢

    2018年5月4日 0:49
  • 你好,

    如果你要用试图的话,请去掉Order by 语句。 像这样,然后查询视图的时候在排序。

    CREATE VIEW [dbo].[tz_ViewAssets]
    	AS 
    	
    	WITH cte1 as
    	(
    		select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number from TestT 
    		where sumpirce >=200 
    		group by Deptid
    	),
    	cte2 as
    	(
    		select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number  from TestT 
    		where sumpirce <200 
    		group by Deptid
    	)
    	(select Deptid, SumPrice,Number from cte1 union all 
    	select Deptid, SumPrice,Number from cte2) 
    
    

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年5月4日 6:44
    版主
  • 你好,谢谢,结果如下,可是有一个问题,怎么才能区分出是 低值易耗品>=200  还是  消耗品<200?,现在表里面每个部门都以200为界,分成了两个组,有的可能是一组,可现在看不是到底哪行是低值易耗品,行是消耗品,因为这里的总价是经过sum过滤后的,如何修改sql来 区分?

    ---------------------------------------

    1006 部门A 200 2
    1006 部门A 1000 1
    1007 部门B 80 1
    1007 部门B 2282 4
    1008 部门C 652 4



    部门编号,部门,总价,数量
    • 已编辑 icod 2018年5月4日 9:34
    2018年5月4日 9:31
  • 你好,

    可以加一行来识别。

    with cte1 as
    (
    	select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number, '消耗品' as SumType from TestT 
    	where sumpirce >=200 
    	group by Deptid
    ),
    cte2 as
    (
        select Deptid, sum(sumpirce) as SumPrice, COUNT(*) as Number, '低值易耗品' as SumType  from TestT 
    	where sumpirce <200 
    	group by Deptid
    )
    (select Deptid, SumPrice,Number,SumType from cte1 union all 
    select Deptid, SumPrice,Number,SumType from cte2)

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 icod 2018年5月7日 0:42
    2018年5月4日 9:53
    版主
  • 感谢 Zhanglong WU
    2018年5月7日 0:42