用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语句该怎么写呢?
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语句该怎么写呢?
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)