请进,求解一条SQL

这只是我表中的一部分数据。
列名：         TactCode,TactName,TactParentCode 01,计算机策略, 0
0101, 软件配置, 01
0102, Windows 配置, 01
010201 ,远程安装服务, 0102
010202, 脚本 ,0102
01020201, 脚本（注销/登录）, 010202
0102020101 ,注销, 01020201
0102020102, 登录, 01020201
010203, 安全设置, 0102
010204, Internet Explorer 维护, 0102
0103, 管理模板, 01
010301, Windows组件, 0103
01030101, NetMeeting, 010301
0103010101, 应用应用共享, 01030101
010301010101 ,禁用应用程序共享, 0103010101
010301010102 ,防止共享, 010301010101
010301010103 ,防止桌面共享, 0103010101
0103010102 ,音频和视频, 01030101
010301010201 ,防止发送视频, 0103010102
0103010103 ,选项页 ,01030101
010301010301 ,隐藏工具选项, 0103010103 

说明：求  TactCode的某一行数据是否是末级（增加一下 bInvCEnd ）.如果是为1 不是为0 
我想要的结果：列名：           TactCode,TactName,TactParentCode ,bInvCEnd 01,计算机策略,             0 ,   0
0101, 软件配置,         01  ,0
0102, Windows 配置,     01,   0
010201 ,远程安装服务,     0102  ,1
010202, 脚本            ,0102  ,0
01020201,脚本（注销/登录） ,010202      ,0
0102020101 ,注销,        01020201     ,1
0102020102, 登录,         01020201  ,1
010203, 安全设置,         0102,   1
010204, Internet Exp    , 0102         ,1
0103, 管理模板,         01  ,0
010301, Windows组件,      0103,   0
01030101, NetMeeting,     010301,   0
0103010101, 应用应用共享, 01030101,   0
010301010101,禁用应,     0103010101,   1
010301010102 ,防止共享, 010301010101, 1
010301010103 ,防止享, 0103010101,   1
0103010102 ,音频和视频, 01030101,     0
010301010201 ,防止频, 0103010102,   1
0103010103 ,选项页        ,01030101,     0
010301010301 ,隐藏工, 0103010103 ,  1
 

解决方案 »

  1.   

    说明：求  TactCode的某一行数据是否是末级（增加一下 bInvCEnd ）.如果是为1 不是为0 
    这句没看明白，TactCode的某一行是什么意思？
      

  2.   

    select t.* , bInvCEnd = (case when exists(select 1 from tb where TactParentCode = t.TactCode) then 1 else 0 end) from tb t 
      

  3.   

    --> 测试数据: #tb
    if object_id('tempdb.dbo.#tb') is not null drop table #tb
    go
    create table #tb(TactCode varchar(20),TactName varchar(50),TactParentCode varchar(20) )
    insert into #tb
    select '01','计算机策略','0' union all
    select '0101','软件配置','01' union all
    select '0102','Windows 配置','01' union all
    select '010201','远程安装服务','0102'  union all
    select '010202','脚本','0102'  union all
    select '01020201','脚本(注销/登录)','010202'  union all
    select '0102020101','注销','01020201' union all
    select '0102020102','登录','01020201'  union all
    select '010203','安全设置','0102' union all
    select '010204','Internet Explorer 维护','0102' union all
    select '0103','管理模板','01' union all
    select '010301','Windows组件','0103' union all
    select '01030101','NetMeeting','010301' union all
    select '0103010101','应用应用共享','01030101' union all
    select '010301010101','禁用应用程序共享','0103010101' union all
    select '010301010102','防止共享','010301010101' union all
    select '010301010103','防止桌面共享','0103010101' union all
    select '0103010102','音频和视频','01030101' union all
    select '010301010201','防止发送视频','0103010102' union all
    select '0103010103','选项页','01030101'  union all
    select '010301010301','隐藏工具选项','0103010103' alter table #tb add  bInvCEnd int
    update a 
    set bInvCEnd=0
    from #tb a
    where exists(select 1 from #tb b where left(b.TactParentCode,len(a.TactCode))= a.TactCode)
     update a 
    set bInvCEnd=1
    from #tb a
    where not exists(select 1 from #tb b where left(b.TactParentCode,len(a.TactCode))= a.TactCode)
     
     select * from #tb
    TactCode             TactName                                           TactParentCode       bInvCEnd
    -------------------- -------------------------------------------------- -------------------- -----------
    01                   计算机策略                                              0                    0
    0101                 软件配置                                               01                   1
    0102                 Windows 配置                                         01                   0
    010201               远程安装服务                                             0102                 1
    010202               脚本                                                 0102                 0
    01020201             脚本(注销/登录)                                          010202               0
    0102020101           注销                                                 01020201             1
    0102020102           登录                                                 01020201             1
    010203               安全设置                                               0102                 1
    010204               Internet Explorer 维护                               0102                 1
    0103                 管理模板                                               01                   0
    010301               Windows组件                                          0103                 0
    01030101             NetMeeting                                         010301               0
    0103010101           应用应用共享                                             01030101             0
    010301010101         禁用应用程序共享                                           0103010101           0
    010301010102         防止共享                                               010301010101         1
    010301010103         防止桌面共享                                             0103010101           1
    0103010102           音频和视频                                              01030101             0
    010301010201         防止发送视频                                             0103010102           1
    0103010103           选项页                                                01030101             0
    010301010301         隐藏工具选项                                             0103010103           1(21 行受影响)
      

  4.   

    create table tb(TactCode varchar(30),TactName varchar(30),TactParentCode varchar(30))
    insert into tb values('01' ,'计算机策略' , '0')
    insert into tb values('0101' ,'软件配置' , '01')
    insert into tb values('0102' ,'Windows 配置' , '01')
    insert into tb values('010201' ,'远程安装服务' , '0102')
    insert into tb values('010202' ,'脚本' ,'0102')
    insert into tb values('01020201' ,'脚本（注销/登录）' , '010202')
    insert into tb values('0102020101' ,'注销' , '01020201')
    insert into tb values('0102020102' ,'登录' , '01020201')
    insert into tb values('010203' ,'安全设置' , '0102')
    insert into tb values('010204' ,'Internet Explorer 维护', '0102')
    insert into tb values('0103' ,'管理模板', '01')
    insert into tb values('010301' ,'Windows组件', '0103')
    insert into tb values('01030101' ,'NetMeeting', '010301')
    insert into tb values('0103010101' ,'应用应用共享', '01030101')
    insert into tb values('010301010101' ,'禁用应用程序共享', '0103010101')
    insert into tb values('010301010102' ,'防止共享', '010301010101')
    insert into tb values('010301010103' ,'防止桌面共享', '0103010101')
    insert into tb values('0103010102' ,'音频和视频', '01030101')
    insert into tb values('010301010201' ,'防止发送视频', '0103010102')
    insert into tb values('0103010103' ,'选项页' ,'01030101')
    insert into tb values('010301010301' ,'隐藏工具选项', '0103010103')
    go 
    select t.* , bInvCEnd = (case when not exists(select 1 from tb where TactParentCode = t.TactCode) then 1 else 0 end) from tb t drop table tb/*
    TactCode                       TactName                       TactParentCode                 bInvCEnd    
    ------------------------------ ------------------------------ ------------------------------ ----------- 
    01                             计算机策略                          0                              0
    0101                           软件配置                           01                             1
    0102                           Windows 配置                     01                             0
    010201                         远程安装服务                         0102                           1
    010202                         脚本                             0102                           0
    01020201                       脚本（注销/登录）                      010202                         0
    0102020101                     注销                             01020201                       1
    0102020102                     登录                             01020201                       1
    010203                         安全设置                           0102                           1
    010204                         Internet Explorer 维护           0102                           1
    0103                           管理模板                           01                             0
    010301                         Windows组件                      0103                           0
    01030101                       NetMeeting                     010301                         0
    0103010101                     应用应用共享                         01030101                       0
    010301010101                   禁用应用程序共享                       0103010101                     0
    010301010102                   防止共享                           010301010101                   1
    010301010103                   防止桌面共享                         0103010101                     1
    0103010102                     音频和视频                          01030101                       0
    010301010201                   防止发送视频                         0103010102                     1
    0103010103                     选项页                            01030101                       0
    010301010301                   隐藏工具选项                         0103010103                     1（所影响的行数为 21 行）
    */
      

  5.   

    前面我自己也写了一个。不过来对。我现在发出来给大家看看。。
    select *,
     case LEN(TactCode)/ 2  when (select max(LEN(TactCode)/ 2) from tb ti 
      where substring(ti.TactCode, 1, 2) = substring(tp.TactCode, 1, 2))
      then 1  else 0  end  as bInvCEnd  from tb tp
          order by TactCode