求一个sql语句！！着急！！在线等！！

Question

问一个问题：
TABLE1
---------------------
id |email 
---------
1  |[email protected]
2  |[email protected]
3  |[email protected]
4  |[email protected]
5  |[email protected]
6  |[email protected]如何用select语句得到这样的
---------------
id|type  |number
1 |msn   |3
2 |gmail |2
3 |hotmail|1

Answer 1

select type,count(1) from (select SUBSTRING(email,CHARINDEX('@',mail)+1,CHARINDEX('.',mail)-CHARINDEX('@',mail)-1) type from table1) a
group by type
order by count(1) desc

Answer 2

SELECT 
SUBSTRING(
Email,
CHARINDEX('@',Email)+1,
len(Email)-CHARINDEX('@',Email)
) ,
       count(SUBSTRING(
Email,
CHARINDEX('@',Email)+1,
len(Email)-CHARINDEX('@',Email)
))
FROM Tabcustomer
group by (
SUBSTRING(
Email,
CHARINDEX('@',Email)+1,
len(Email)-CHARINDEX('@',Email)
)
)