关于查询分组的问题。。。。。。

Question

select s_ip ,count(*) as n from systemcount group by  s_ip
--需查询结果
192.168.0.1 1
192.168.0.106 1
192.168.1.116 1
192.168.2.116 161.125.137.5 1
61.125.1.5 1--需查询结果192.168.*.* 4
61.125.*.*      2这样的结果该如何来搞啊，谢谢！

Answer 1

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ip varchar(13),num int)
insert into #T
select '192.168.0.1',1 union all
select '192.168.0.106',1 union all
select '192.168.1.116',1 union all
select '192.168.2.116',1 union all
select '61.125.137.5',1 union all
select '61.125.1.5',1select parsename(ip,4)+'.'+parsename(ip,3)+'.*.*' as ip, count(*) as n from #T group by parsename(ip,4)+'.'+parsename(ip,3)+'.*.*'/*
ip          n
----------- -----------
192.168.*.* 4
61.125.*.* 2
*/

Answer 2

select s_ip ,count(*) as n from systemcount group by  s_ip 
--查询结果 
s_ip          n192.168.0.1   1 
192.168.0.106 1 
192.168.1.116 1 
192.168.2.116 1 61.125.137.5  1 
61.125.1.5    1 --需要的查询结果 192.168.*.*   4 
61.125.*.*    2 也就是按IP的前两端字符来分组统计N，谢谢解答！

Answer 3

declare @t table (s_ip varchar(100))insert @t select
'192.168.0.1' union all select
'192.168.0.106' union all select
'192.168.1.116' union all select
'192.168.2.116' union all select'61.125.137.5' union all select
'61.125.1.5 1'
select PARSENAME(s_ip,4)+'.'+PARSENAME(s_ip,3)+'.*.*' as s_ip ,count(*)  as n from @t
 group by  PARSENAME(s_ip,4)+'.'+PARSENAME(s_ip,3)+'.*.*'--结果 
s_ip                                                                                                                                                                                                                                                             n           
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 
192.168.*.*                                                                                                                                                                                                                                                      4
61.125.*.*                                                                                                                                                                                                                                                       2（所影响的行数为 2 行）

Answer 4

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (IP varchar(13),c2 int)
insert into #T
select '192.168.0.1',1 union all
select '192.168.0.106',1 union all
select '192.168.1.116',1 union all
select '192.168.2.116',1 union all
select '61.125.137.5',1 union all
select '61.125.1.5',1
go
select left(IP,charindex('.',IP,charindex('.',IP)+1))+'*.*',count(*) cnt
from #T
group by left(IP,charindex('.',IP,charindex('.',IP)+1))+'*.*'
go
drop table #T/*
                 cnt         
---------------- ----------- 
192.168.*.*      4
61.125.*.*       2（所影响的行数为 2 行）*/