大家看个SQL问题:
有以下字段:
gid 组号
isVisited 是否访问
isover 是否结束如何用一个SQL语句得到如下的结果
分组号 总量 访问比例 结束比例
20110911 4685 25/3% 723/14%
...可以不要比例,也可以不需要分页,仅得一条特定分组号也可;
有以下字段:
gid 组号
isVisited 是否访问
isover 是否结束如何用一个SQL语句得到如下的结果
分组号 总量 访问比例 结束比例
20110911 4685 25/3% 723/14%
...可以不要比例,也可以不需要分页,仅得一条特定分组号也可;
from tb group by gid,isVisited,isover
--这样试试
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 gid
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,
总量=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
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')
goselect 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 giddrop table tb/*
gid 总量 访问比例 结束比例
----------- ----------- -------------------- --------------------
1 5 80.00 80.00
2 3 100.00 100.00(所影响的行数为 2 行)
*/
数据是这样子的:gid fid isVisited isover
154 23 0 0
154 34 0 1
154 134 0 1
154 54 1 1
154 254 1 1fid是唯一号楼上的语句有点问题,一是gid不能sum相加,这个没意义;
二是要统计的是isvisited=1的以及isover=1的记录数,谢谢大家
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
,cast(count(isover)*1.0/(select sum(gid) from tb)as decimal(16,2))
from tb group by gid
修改一下
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)
goselect 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 giddrop table tb/*
gid 总量 isVisited = 1的记录数 isover=1的记录数 isVisited = 1的记录数,其实这个最简单 isover=1的记录数,其实这个最简单 访问比例 结束比例
----------- ----------- ----------------- ------------ ------------------------- -------------------- -------------------- --------------------
154 5 2 4 2 4 40.00 80.00(所影响的行数为 1 行)
*/
-- 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 行受影响)*/
-- 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 行受影响)
*/