请问这样的sql语句应该怎么写？在线等！谢谢！

Question

我现在想取出这样的数据：去ip段（前两部分）和对应的ip段
应该是
我现在想取出这样的数据：取ip段（前两部分）和对应的ip段

Answer 1

declare @t table
([id] int,[ip] varchar(20))insert @t
select 1,'141.30.217.10' [union] all
select 2,'141.30.217.10' [union] all
select 3,'202.110.36.42' [union] all
select 4,'202.110.36.42' [union] all
select 5,'211.158.67.206' [union] all
select 6,'218.1.114.162' [union] all
select 7,'218.1.114.18' [union] all
select 8,'218.1.186.195'  [union] all
select 9,'218.12.3.114' [union] all
select 10,'218.13.24.80'
select * from @tselect [ip]=parsename(ip,4)+'.'+parsename(ip,3),count(*)
from @t
group by parsename(ip,4)+'.'+parsename(ip,3)ip                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 
141.30                                                                                                                                                                                                                                                           2
202.110                                                                                                                                                                                                                                                          2
211.158                                                                                                                                                                                                                                                          1
218.1                                                                                                                                                                                                                                                            3
218.12                                                                                                                                                                                                                                                           1
218.13                                                                                                                                                                                                                                                           1

Answer 2

不知道为什么,我不把union 打上[]就发不上来，只好打上了括号了！:)

Answer 3

--楼上的方法巧用PARSENAME，确实不错
--自己写了个复杂点的，也贴上好了。
select left(ip,charindex('.',ip))+left(right(ip,len(ip)-charindex('.',ip)),charindex('.',right(ip,len(ip)-charindex('.',ip)))-1),count(*)
from ipinfo
group by left(ip,charindex('.',ip))+left(right(ip,len(ip)-charindex('.',ip)),charindex('.',right(ip,len(ip)-charindex('.',ip)))-1)

Answer 4

select Ip
       ,count(1) as 'count'
from (
        select Id
               ,left(Ip,charindex('.',Ip,charindex('.',Ip)+1)-1) as 'Ip'
        from IpInfo
     )t
group by Ip