sql - 给出所选成本中心的名称 例如：

Question

  CostCenterNo |  CostCenterName   | ParentCostCenterNo |     |     
 --------------|-------------------|--------------------|-----|----- 
           761 | Power Plant       | null               |     |     
           762 | Turbine           |                    | 761 |     
           763 | Coolent           |                    | 762 |     
           764 | Plant & Machinery | null               |     |     
           765 | Roller Machine    | 764                |     |     
           766 | Vehicle & Motors  | null               |     |     
           767 | Hywa              |                    |     | 766 

declare @CostCenterNo int
select  @CostCenterNo = 763

with CostCen as(Select * from @table where CostCenterNo=@CostCenterNo
union all
select one.* from @Table as one 
inner join  CostCen cc  on one.ParentCostCenterNo= cc.CostCenterNo)
select * from Costcen where CostcenterNo = @CostcenterNo

Select funcCostCenter(767) then it will Give Output- Vehicle & Motors

标签： sqlsql-servertsql

Answer 1

看起来你想要

with CostCen as(
  Select * 
  from costCenters 
  where CostCenterNo=@CostCenterNo
  union all
  select one.* 
  from costCenters as one 
  inner join CostCen cc on cc.ParentCostCenterNo = one.CostCenterNo
)
select * 
from Costcen 
where ParentCostCenterNo is null

编辑

标量函数使用上述查询从表中获取CostCenterName给定的顶级：CostCenterNocostCenters

create function funcCostCenter(@CostCenterNo int)
returns varchar(2000)
as
begin 
    declare @res varchar(2000);
    with CostCen as(
        select * 
        from costCenters 
        where CostCenterNo=@CostCenterNo
        union all
        select one.* 
        from costCenters as one 
        inner join CostCen cc on cc.ParentCostCenterNo = one.CostCenterNo
    )
    select @res = CostCenterName
    from Costcen 
    where ParentCostCenterNo is null;
    return @res;
end;