SQL查找不同的多个值

I have a table that holds information for rooms, it contains amongst others a column for BLDGCODE and another for COSTCODE. Each room in the building (BLDGCODE) can only be assigned a single costcode and these can be all be the same value or different values.

我有一个包含房间信息的表格，其中包含BLDGCODE栏目和COSTCODE栏目。建筑物中的每个房间（BLDGCODE）只能分配一个成本代码，这些代码可以是相同的值或不同的值。

Am trying to write a script that only shows buildings with more than one distinct cost code assigned to its many rooms, for example several rooms could have cost code ABC and others 999 which is what I want to identify and report on. I have tried with the below thinking a CTE would get me the results, however I am still seeing buildings with only the one cost code that are repeated for multiple rooms in the building.

我试图编写一个脚本，只显示分配给其多个房间的多个不同成本代码的建筑物，例如，几个房间可能有成本代码ABC和其他999这是我想要识别和报告的内容。我已经尝试过以下思考CTE会得到结果，但是我仍然看到建筑物只有一个成本代码，这些代码在建筑物中的多个房间重复出现。

with CTE
as (
    select rtrim(fmb0.reg_code) as Region,
        rtrim(Country) as Country,
        rtrim(fmb0.BLDGCODE) as BLDGCODE,
        (
            case 
                when FMB0.BLDGSTATUS = 'CAD'
                    then 'Yes'
                else 'No'
                end
            ) as CAD,
        group_ as GROUP_,
        fma0.usable,
        fmb0.nia,
        fmb0.niahprev
    from fmb0
    left join fma0 on fmb0.bldgcode = fma0.bldgcode
    left join fmey on fmb0.propsubtyp = fmey.ey_key
    left join fme2 on fmb0.country = fme2.descrip
    where fme2.is_live = 1
        and fmey.bau = 1
        and fmb0.bldgcode not like 'xx%'
        and fma0.bldgcode like 'cn%'
    )
select CTE.Region,
    CTE.Country,
    CTE.BLDGCODE,
    CTE.GROUP_,
    sum(cte.usable) as AREA,
    cte.nia,
    cte.niahprev,
    CTE.CAD
from CTE
where CTE.CAD = 'No'
group by CTE.BLDGCODE,
    cte.group_,
    cte.country,
    cte.region,
    cte.nia,
    cte.cad,
    cte.niahprev
having count(CTE.GROUP_) > 1
order by 1, 2, 3

How do I remove those buildings that despite having multiple occurances the cost code is the same, and only show those that have more than one cost code within which are different?

如何删除那些尽管有多次出现但成本代码相同的建筑物，并且只显示那些具有多个成本代码的建筑物不同的建筑物？

So here is the code referencing a single table:

所以这是引用单个表的代码：

with CTE as (
select 
rtrim(fma0.BLDGCODE) as BLDGCODE, 
group_ as GROUP_,
fma0.usable
from fma0
where 
fma0.bldgcode like 'cn%'
)
select 
CTE.BLDGCODE,
CTE.GROUP_ AS COSTCODE,
sum(cte.usable) as AREA
from CTE
group by CTE.BLDGCODE, cte.group_
having count(CTE.GROUP_) > 1
order by 1

Example data set would be:

示例数据集将是：

RMID      BLDGCODE    COSTCODE    AREA
01.01     01          AA-01       10
01.02     01          AS-05       20
01.03     01          XY-99       30
01.04     01          XY-99       70
02.01     02          AA-01       10
02.02     02          AA-01       20
02.03     02          AA-01       20

Expected results would be:

预期结果将是：

BLDGCODE     COSTCODE     AREA
01           AA-01        10
01           AS-05        20
01           XY-99        100

BLDGCODE 02 would not be shown as it only has a single cost code

BLDGCODE 02不会显示，因为它只有一个成本代码

Thanks

谢谢

1 个解决方案

#1

You want all those rows with multiple costcodes, so a simple aggregation doesn't work. You can to do the same logic using another cte with Windowed Aggregates instead:

您希望所有这些行都具有多个代码，因此简单的聚合不起作用。您可以使用另一个带有窗口聚合的cte来执行相同的逻辑：

with CTE as (
select 
   rtrim(fma0.BLDGCODE) as BLDGCODE, 
   group_ as GROUP_,
   fma0.usable
from fma0
where fma0.bldgcode like 'cn%'
)
,counts as
 (
   select 
      CTE.BLDGCODE,
      CTE.GROUP_ AS COSTCODE,
      sum(cte.usable) as AREA,
      case when min(CTE.GROUP_) over (partition by CTE.BLDGCODE) 
             <> max(CTE.GROUP_) over (partition by CTE.BLDGCODE)
           then 1
           else 0
      end as flag
   from CTE
   group by CTE.BLDGCODE, cte.group_
 ) 
select *
from counts
where flag = 1