100分急求一条SQL语句！！！！！！！！！！！！！！！！！！！

[dopsop110]

表结构如下：
id 主键
groupiD 编码键

id groupiD
1 1
2 1.1
3 1.2
4 1.1.1
5 2
6 2.1
7 2.1.1
8 2.2

如上所示，其实groupId里面就是拼成个树，现在要查询出 跟2.1同级的节点有多少。同级的应该有2.1和2.2这两条
这SQL语句该如何写呢？
用like查询 select * from tb where groupid like '2.%' 这样就把2.1.1也查询出来了。

[修改一下昵称]

select * from tb where groupid like '2.%' and len(groupid)=len('2.1')

[永生天地]

select * from tb where groupid like '2.[0-9]'

[Mr_Nice]

select * from tb where groupid like '2.[0-9]'

[SQL77]

[Quote=引用 2 楼 xys_777 的回复:]
select * from tb where groupid like '2.[0-9]'
[/Quote]
...

[PxxxP]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[groupiD] varchar(5))
insert [TB]
select 1,'1' union all
select 2,'1.1' union all
select 3,'1.2' union all
select 4,'1.1.1' union all
select 5,'2' union all
select 6,'2.1' union all
select 7,'2.1.1' union all
select 8,'2.2'

select * from tb where groupid like '2.%' and len([groupiD])-len(replace([groupiD],'.',''))=1

/*
id          groupiD
----------- -------
6           2.1
8           2.2

(2 行受影响)

*/

drop table [TB]

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[groupiD] varchar(5))
insert [tb]
select 1,'1' union all
select 2,'1.1' union all
select 3,'1.2' union all
select 4,'1.1.1' union all
select 5,'2' union all
select 6,'2.1' union all
select 7,'2.1.1' union all
select 8,'2.2'
go

select *,len(groupiD)-len(replace('.',groupiD,'')) from tb
where len(groupiD)-len(replace('.',groupiD,''))=len('2.1')-len(replace('.','2.1',''))

/**
id          groupiD 
----------- ------- -----------
2           1.1     2
3           1.2     2
6           2.1     2
8           2.2     2

(4 行受影响)
**/

[Mr_Nice]

--> 测试数据：[A1]
if object_id('[A1]') is not null drop table [A1]
create table [A1]([card] int,[company_id] varchar(5))
insert [A1]
select 123456,'2.1' union all
select 234567,'2.2' union all
select 456567,'2.1.1'

select * from [A1]


SELECT * FROM A1 WHERE company_id LIKE '2.[0-9]'
/*
card        company_id
----------- ----------
123456      2.1
234567      2.2

(2 行受影响)*/

[百年树人]

declare @gid varchar(10)
set @gid='2.1'
select * from tb
where len(groupiD)-len(replace('.',groupiD,''))=len(@gid)-len(replace('.',@gid,''))

/**
id          groupiD
----------- -------
2           1.1
3           1.2
6           2.1
8           2.2

(4 行受影响)

**/

[永生天地]

select * from tb where groupid like '2.%' and groupid not like '2.%.%'

[中国风]

這樣?

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#') is null
	drop table #
Go
Create table #([id] int,[groupiD] nvarchar(5))
Insert #
select 1,N'1' union all
select 2,N'1.1' union all
select 3,N'1.2' union all
select 4,N'1.1.1' union all
select 5,N'2' union all
select 6,N'2.1' union all
select 7,N'2.1.1' union all
select 8,N'2.2'
Go
;WITH c
AS
(
Select 
	*,
	level=LEN([groupiD])-LEN(REPLACE([groupiD],'.','') )+1
from # 
)
SELECT COUNT(*) AS 記錄數 
FROM c a 
WHERE EXISTS(SELECT 1 FROM c WHERE [groupiD]='2.1' AND level=a.level)

[修改一下昵称]

[Quote=引用 9 楼 xys_777 的回复:]
select * from tb where groupid like '2.%' and groupid not like '2.%.%'
[/Quote]

+1

[dopsop110]

[Quote=引用 9 楼 xys_777 的回复:]
select * from tb where groupid like '2.%' and groupid not like '2.%.%'
[/Quote]
呵呵 这句跟我想的一样呀。。
效率是不是不高？

[永生天地]

[Quote=引用 12 楼 dopsop110 的回复:]
引用 9 楼 xys_777 的回复:
select * from tb where groupid like '2.%' and groupid not like '2.%.%'

呵呵 这句跟我想的一样呀。。
效率是不是不高？
[/Quote]

要得到这样结果的查询，效率估计都不高

[guye99]

[Quote=引用 2 楼 xys_777 的回复:]
select * from tb where groupid like '2.[0-9]'
[/Quote]
这个好.我顶