邮箱分组差比列

菜菜菜菜鸟 2012-12-17 04:44:22

先上图

怎么根据邮箱类型(QQ/网易/新浪等)分组
计算出数量比列最后20分求帮助

...全文

146 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

發糞塗牆 2012-12-17

打赏
举报

--create table tb(email nvarchar(20))
--insert into tb select 'aaa@sina.com'
--insert into tb select 'abb@sina.com'
--insert into tb select 'acc@sina.com'
--insert into tb select 'da@163.com'
--insert into tb select '213435243@qq.com'
--insert into tb select 'ddfasfa@hotmail.com'
--insert into tb select '64545243@qq.com'
--go

SELECT  REVERSE(SUBSTRING(REVERSE(Email), 1,
                          PATINDEX('%@%', REVERSE(Email)) - 1)) ,
        SUBSTRING(CONVERT(VARCHAR(20), ( CONVERT(DECIMAL(4, 3), COUNT(SUBSTRING(REVERSE(Email),
                                                              1,
                                                              PATINDEX('%@%',
                                                              REVERSE(Email))
                                                              - 1)))
                                         / ( SELECT COUNT(1)
                                             FROM   TB
                                           ) ) * 100), 1, 5) + '%'
FROM    TB
GROUP BY REVERSE(SUBSTRING(REVERSE(Email), 1,
                           PATINDEX('%@%', REVERSE(Email)) - 1))
                           
/*
                     
-------------------- -----------
163.com              14.28%
hotmail.com          14.28%
qq.com               28.57%
sina.com             42.85%

(4 行受影响)

*/

我腫了 2012-12-17

打赏
举报

Declare @EMailtype table(name nvarchar(50),value nvarchar(50))
Insert into @EMailtype(name,value)
Select N'QQ',N'qq.com' Union all
Select N'网易',N'163.com' Union all 
Select N'新浪'N'sina.com' Union 
.......
Select N'雅虎',N'yahoo.com.cn'

Declare @EMail_cnt int

Select @EMail_cnt=Count(1) from EMailList

Select 
        b.name As 邮箱类型,Count(1) As 数量,ltrim(Convert(numeric(5,2),Count(1) * 1.0 / @EMail_cnt * 100))+'%' As 比例 
    from EMailList As a
        Inner join @EMailtype As b On Stuff(a.EMail,1,patindex('%@%',a.EMail),'')=b.value
Group by b.name

我腫了 2012-12-17

打赏
举报

Declare @EMailtype table(name nvarchar(50),value nvarchar(50))
Insert into @EMailtype(name,value)
Select N'QQ',N'qq.com' Union all
Select N'网易',N'163.com' Union all 
Select N'新浪'N'sina.com' Union 
.......
Select N'雅虎',N'yahoo.com.cn'


Select 
        b.name As 邮箱类型,Count(1) As 数量,Convert(numeric(5,2),Count(1) * 1.0 / (Select Count(1) from EMailList) * 100) As 比例 
    from EMailList As a
        Inner join @EMailtype As b On Stuff(a.EMail,1,patindex('%@%',a.EMail),'')=b.value

我腫了 2012-12-17

打赏
举报

Declare @EMailtype table(name nvarchar(50),value nvarchar(50)) Insert into @EMailtype(name,value) Select N'QQ',N'qq.com' Union all Select N'网易',N'163.com' Union all Select N'新浪'N'sina.com' Union ....... Select N'雅虎',N'yahoo.com.cn' Select b.name As 邮箱类型,Count(1) As 数量,Count(1)*1.0 / (Select Count(1) from EMailList) As 比例 from EMailList As a Inner join @EMailtype As b On Stuff(a.EMail,1,patindex('%@%',a.EMail),'')=b.value

哥眼神纯洁不 2012-12-17

打赏
举报


select RIGHT(email,len(email)-charindex('@',email,0)),COUNT(RIGHT(email,len(email)-charindex('@',email,0))) from 表名
group by RIGHT(email,len(email)-charindex('@',email,0))

上面那个不对...这个应该是对的了

-晴天 2012-12-17

打赏
举报

create table tb(email nvarchar(20)) insert into tb select 'aaa@sina.com' insert into tb select 'abb@sina.com' insert into tb select 'acc@sina.com' insert into tb select 'da@163.com' insert into tb select '213435243@qq.com' insert into tb select 'ddfasfa@hotmail.com' insert into tb select '64545243@qq.com' go ;with c1 as( select substring(email,charindex('@',email)+1,len(email)-charindex('@',email))e from tb ),c2 as( select e,count(*)ct from c1 group by e ),c3 as( select count(*)ct from tb )select a.e,convert(varchar,convert(decimal(10,2),100.*a.ct/b.ct))+'%' as r from c2 a,c3 b /* e r -------------------- ------------------------------- 163.com 14.29% hotmail.com 14.29% qq.com 28.57% sina.com 42.86% (4 行受影响) */ go drop table tb

哥眼神纯洁不 2012-12-17