sql查询问题！！

Question

用sql server的数据库，有两个表：字段都是字符型
v1:  NodeID   ParentID  NodeName
       1        0         aaa
       1.1      1         bc
       1.2      1         d1
       1.2.4    1.2       d3
       2        0         a2
       2.1      2         g4
       3        0         a3
       10       0         a4
       10.1     10        a5
       11       0         a6s1:   ID        Name       type
      1         h1          u1
      1.1       h2          u2
      1.2       h3          u3
      1.2.4     h5          u4
      2         h6          u5
      2.1       h7          u6
      10.1      h8          u7想得到如下查询结果：   NodeID     count
                         1          4
                         2          2
                         3          0
                         10         1 
                         11         0
即列出表v1中所有parentid为'0'的NodeID,并且计算出在s1中匹配的个数
如：s1中id为1 ， 1.1 ， 1.2 ， 1.2.4的各项都算是匹配v1中的1这项，因此选出的count为4这样的sql语句该怎么写呢？

Answer 1

select NodeID,[count]=(select count(*) from s1 where charindex(tmp.NodeID, NodeID)=1)
from v1
where ParentID='0'

Answer 2

CREATE TABLE v1(NodeID varchar(20), ParentID varchar(20), NodeName varchar(20))
insert v1 select        '1',        '0',         'aaa'
union all select        '1.1',      '1',         'bc'
union all select        '1.2',      '1',         'd1'
union all select        '1.2.4',    '1.2',       'd3'
union all select        '2',        '0',         'a2'
union all select        '2.1',      '2',         'g4'
union all select        '3',        '0',         'a3'
union all select        '10',       '0',         'a4'
union all select        '10.1',     '10',        'a5'
union all select        '11',       '0',         'a6'
create table s1(ID  varchar(20), Name varchar(20), type varchar(20))
insert s1 select       '1',         'h1',          'u1'
union all select       '1.1',       'h2',          'u2'
union all select       '1.2',       'h3',          'u3'
union all select       '1.2.4',     'h5',          'u4'
union all select       '2',         'h6',          'u5'
union all select       '2.1',       'h7',          'u6'
union all select       '10.1',      'h8',          'u7'select NodeID,[count]=(select count(*) from s1 
where (case when charindex('.', ID)=0 then ID else left(ID, charindex('.', ID)-1) end)=tmp.NodeID)
from v1 as tmp
where ParentID='0'--result
NodeID               count       
-------------------- ----------- 
1                    4
2                    2
3                    0
10                   1
11                   0(5 row(s) affected)

Answer 3

if object_id('pubs..v1') is not null
   drop table v1
gocreate table v1(NodeID   varchar(10),ParentID varchar(10),NodeName varchar(10))
insert into v1(NodeID,ParentID,NodeName) values('1'    ,    '0'  ,       'aaa')
insert into v1(NodeID,ParentID,NodeName) values('1.1'  ,    '1'  ,       'bc')
insert into v1(NodeID,ParentID,NodeName) values('1.2'  ,    '1'  ,       'd1')
insert into v1(NodeID,ParentID,NodeName) values('1.2.4',    '1.2',       'd3')
insert into v1(NodeID,ParentID,NodeName) values('2'    ,    '0'  ,       'a2')
insert into v1(NodeID,ParentID,NodeName) values('2.1'  ,    '2'  ,       'g4')
insert into v1(NodeID,ParentID,NodeName) values('3'    ,    '0'  ,       'a3')
insert into v1(NodeID,ParentID,NodeName) values('10'   ,    '0'  ,       'a4')
insert into v1(NodeID,ParentID,NodeName) values('10.1' ,    '10' ,       'a5')
insert into v1(NodeID,ParentID,NodeName) values('11'   ,    '0'  ,       'a6')
go
if object_id('pubs..s1') is not null
   drop table s1
gocreate table s1(ID varchar(10),Name varchar(10),type varchar(10))
insert into s1(ID,Name,type) values('1'    ,     'h1',          'u1')
insert into s1(ID,Name,type) values('1.1'  ,     'h2',          'u2')
insert into s1(ID,Name,type) values('1.2'  ,     'h3',          'u3')
insert into s1(ID,Name,type) values('1.2.4',     'h5',          'u4')
insert into s1(ID,Name,type) values('2'    ,     'h6',          'u5')
insert into s1(ID,Name,type) values('2.1'  ,     'h7',          'u6')
insert into s1(ID,Name,type) values('10.1' ,     'h8',          'u7')
goselect m.nodeid , isnull(n.count,0) as count from
(
  select * from v1 where parentid = '0'
) m
left join
(
  select id , count(*) as count from
  (select case when charindex('.',id) > 0 then left(id,charindex('.',id)-1) else id end as id from s1) t
  group by id
) n
on m.nodeid = n.iddrop table v1,s1
nodeid     count       
---------- ----------- 
1          4
2          2
3          0
10         1
11         0（所影响的行数为 5 行）