22,209
社区成员
发帖
与我相关
我的任务
分享一个统计的SQL语句
大家看个SQL问题：
有以下字段：
gid 组号
isVisited 是否访问
isover 是否结束
如何用一个SQL语句得到如下的结果
分组号 总量 访问比例 结束比例
20110911 4685 25/3% 723/14%
...
可以不要比例，也可以不需要分页，仅得一条特定分组号也可；
有以下字段：
gid 组号
isVisited 是否访问
isover 是否结束
如何用一个SQL语句得到如下的结果
分组号 总量 访问比例 结束比例
20110911 4685 25/3% 723/14%
...
可以不要比例，也可以不需要分页，仅得一条特定分组号也可；
...全文
请发表友善的回复…
发表回复
可西哥 2011-09-20
- 打赏
- 举报
感谢大家！
几个朋友非常热心，甚至模拟了数据，其中的几个语句 给了我很大的启发。
几个朋友非常热心，甚至模拟了数据，其中的几个语句 给了我很大的启发。
--小F-- 2011-09-20
- 打赏
- 举报
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-09-20 10:44:32
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([gid] int,[fid] int,[isVisited] int,[isover] int)
insert [tb]
select 154,23,0,0 union all
select 154,34,0,1 union all
select 154,134,0,1 union all
select 154,54,1,1 union all
select 154,254,1,1
--------------开始查询--------------------------
select
gid,
count(1) as 总量,
ltrim(cast((sum(case when isVisited=1 then 1 else 0 end)*100.0/(select count(1) from tb)) as dec(18,2)))+'%',
ltrim(cast((sum(case when isover=1 then 1 else 0 end)*100.0/(select count(1) from tb)) as dec(18,2)))+'%'
from
tb
group by
gid
----------------结果----------------------------
/*gid 总量
----------- ----------- ------------------------------------------ ------------------------------------------
154 5 40.00% 80.00%
(1 行受影响)
*/--小F-- 2011-09-20
- 打赏
- 举报
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-09-20 10:44:32
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([gid] int,[fid] int,[isVisited] int,[isover] int)
insert [tb]
select 154,23,0,0 union all
select 154,34,0,1 union all
select 154,134,0,1 union all
select 154,54,1,1 union all
select 154,254,1,1
--------------开始查询--------------------------
select
gid,
count(1) as 总量,
ltrim(sum(case when isVisited=1 then 1 else 0 end)*100.0/(select count(1) from tb))+'%',
ltrim(sum(case when isover=1 then 1 else 0 end)*100.0/(select count(1) from tb))+'%'
from
tb
group by
gid
----------------结果----------------------------
/* gid 总量
----------- ----------- ------------------------------------------ ------------------------------------------
154 5 40.000000000000% 80.000000000000%
(1 行受影响)
*/
-晴天 2011-09-20
- 打赏
- 举报
select gid,count(*)总量,sum(case when isvisited =1 then 1 else 0 end)访问量,sum(case when isover=1 then 1 else 0 end) 结束量 from tb group by gid
dawugui 2011-09-20
- 打赏
- 举报
create table tb(gid int,fid int,isVisited int,isover int)
insert into tb values(154 ,23 ,0 ,0)
insert into tb values(154 ,34 ,0 ,1)
insert into tb values(154 ,134 ,0 ,1)
insert into tb values(154 ,54 ,1 ,1)
insert into tb values(154 ,254 ,1 ,1)
go
select gid , count(1) 总量 ,
sum(case when isVisited = 1 then 1 else 0 end) [isVisited = 1的记录数],
sum(case when isover = 1 then 1 else 0 end) [isover=1的记录数],
sum(isVisited) [isVisited = 1的记录数，其实这个最简单],
sum(isover) [isover=1的记录数，其实这个最简单],
访问比例 = cast((select count(1) from tb where gid = t.gid and isVisited = 1) * 100.0 / (select count(1) from tb where gid = t.gid) as decimal(18,2)) ,
结束比例 = cast((select count(1) from tb where gid = t.gid and isover = 1) * 100.0 / (select count(1) from tb where gid = t.gid) as decimal(18,2))
from tb t
group by gid
drop table tb
/*
gid 总量 isVisited = 1的记录数 isover=1的记录数 isVisited = 1的记录数，其实这个最简单 isover=1的记录数，其实这个最简单 访问比例 结束比例
----------- ----------- ----------------- ------------ ------------------------- -------------------- -------------------- --------------------
154 5 2 4 2 4 40.00 80.00
（所影响的行数为 1 行）
*/
chuanzhang5687 2011-09-20
- 打赏
- 举报
select gid,count(gid),cast(count(isVisited)*1.0/(select sum(gid) from tb)as decimal(16,2))
,cast(count(isover)*1.0/(select sum(gid) from tb)as decimal(16,2))
from tb group by gid修改一下
-晴天 2011-09-20
- 打赏
- 举报
select gid,count(*),sum(case when isvisited =1 then 1 else 0 end)访问量,sum(case when isover=1 then 1 else 0 end) 结束量 from tb-晴天 2011-09-20
- 打赏
- 举报
真搞不明白,上面那些大神们是怎么得出那么一大串一大串的语句,我连题目都没看懂!
NBDBA 2011-09-20
- 打赏
- 举报
select
gid,
count(1) as 总量,
ltrim(sum(case when isVisited=1 then 1 else 0 end) + '/' + ltrim(sum(case when isVisited=1 then 1 else 0 end)*100.0/count(1))+'%',
ltrim(sum(case when isover=1 then 1 else 0 end) + '/' + ltrim(sum(case when isover=1 then 1 else 0 end)*100.0/count(1))+'%'
from
tb
group by
gid可西哥 2011-09-20
- 打赏
- 举报
补充一下：
数据是这样子的：
gid fid isVisited isover
154 23 0 0
154 34 0 1
154 134 0 1
154 54 1 1
154 254 1 1
fid是唯一号
楼上的语句有点问题，一是gid不能sum相加，这个没意义；
二是要统计的是isvisited=1的以及isover=1的记录数，谢谢大家
数据是这样子的：
gid fid isVisited isover
154 23 0 0
154 34 0 1
154 134 0 1
154 54 1 1
154 254 1 1
fid是唯一号
楼上的语句有点问题，一是gid不能sum相加，这个没意义；
二是要统计的是isvisited=1的以及isover=1的记录数，谢谢大家
-晴天 2011-09-20
- 打赏
- 举报
另外,你的表中是组号,你的查询中是"分组号",它们是什么关系?
-晴天 2011-09-20
- 打赏
- 举报
你的访问比例是怎么定义的?
dawugui 2011-09-20
- 打赏
- 举报
create table tb(gid int,isVisited varchar(1) ,isover varchar(1))
insert into tb values(1,'Y','Y')
insert into tb values(1,'Y','Y')
insert into tb values(1,'Y','Y')
insert into tb values(1,'N','Y')
insert into tb values(1,'Y','N')
insert into tb values(2,'Y','Y')
insert into tb values(2,'Y','Y')
insert into tb values(2,'Y','Y')
go
select gid , count(1) 总量 ,
访问比例 = cast((select count(1) from tb where gid = t.gid and isVisited = 'Y') * 100.0 / (select count(1) from tb where gid = t.gid) as decimal(18,2)) ,
结束比例 = cast((select count(1) from tb where gid = t.gid and isover = 'Y') * 100.0 / (select count(1) from tb where gid = t.gid) as decimal(18,2))
from tb t
group by gid
drop table tb
/*
gid 总量 访问比例 结束比例
----------- ----------- -------------------- --------------------
1 5 80.00 80.00
2 3 100.00 100.00
（所影响的行数为 2 行）
*/
叶子 2011-09-20
- 打赏
- 举报
select
分组号 =gid,
总量=count(1),
访问数=sum(case isVisited then 1 then 1 else 0 end),
访问结束数=sum(case isover then 1 then 1 else 0 end)
from tablename group by gid--小F-- 2011-09-20
- 打赏
- 举报
select
gid,
count(1) as 总量,
ltrim(sum(case when isVisited=1 then 1 else 0 end)*100.0/(select count(1) from tb))+'%',
ltrim(sum(case when isover=1 then 1 else 0 end)*100.0/(select count(1) from tb))+'%'
from
tb
group by
gid
dawugui 2011-09-20
- 打赏
- 举报
--假设isVisited = 'Y',isover = 'Y'代表访问和结束
select gid , count(1) 总量 ,
访问比例 = cast((select count(1) from tb where gid = t.gid and isVisited = 'Y') * 100.0 / (select count(1) from tb) as decimal(18,2)) ,
结束比例 = cast((select count(1) from tb where gid = t.gid and isover = 'Y') * 100.0 / (select count(1) from tb) as decimal(18,2))
from tb t
group by gidchuanzhang5687 2011-09-20
- 打赏
- 举报
select gid,count(gid),count(isVisited)/sum(gid),count(isover)/sum(gid)
from tb group by gid,isVisited,isover
--这样试试
