碰到一个问题,请教高手们。。有一张表如下数据现在想做一个统计。。
得到如下数据TaskID IsCall IsMsg IsFax IsEmail total
12 1/3 0/3 0/3 0/3 1
23 1/3 0/3 0/3 0/3 1
13 1/2 1/2 0/2 0/2 1这里 isCall 1/3 是根据taskID分组而来的,,这个任务有三条客户
IsCall 有一条 True 两条False 所以得出 1/3请教 这种统计有没有好的效率高点的查询,,难道真的只有动态SQl可以实现吗。
得到如下数据TaskID IsCall IsMsg IsFax IsEmail total
12 1/3 0/3 0/3 0/3 1
23 1/3 0/3 0/3 0/3 1
13 1/2 1/2 0/2 0/2 1这里 isCall 1/3 是根据taskID分组而来的,,这个任务有三条客户
IsCall 有一条 True 两条False 所以得出 1/3请教 这种统计有没有好的效率高点的查询,,难道真的只有动态SQl可以实现吗。
解决方案 »
- 有谁装的是ms sql server 2005的数据库啊?
- 高手請進,這句SQL 如何寫,回答正確立即給分.
- 一个简单的数据库问题,在线等待....
- 这个SQL应该怎么写
- **********两台数据库服务器的触发器问题?? ***********
- 两个小问题,搞定了80分全额送出。谢谢
- 请教,一个有关把MySql中一个表的数据倒到Ms SQL Server一个表中的sql语句,这个语句是在Ms SQL Server中执行的
- sqlserver2000可以在win2000pro下安装吗?
- 讨论一个关于JOIN的问题...
- 请深圳的程序员进来灌水
- SQL2008 openrowset() 只能读取标题,不能读取内容
- SQL server 2005 语句 高手请进
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-26 13:16:13
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([taskid] int,[iscall] varchar(5),[ismsg] varchar(5),[isfax] varchar(5),[isemail] varchar(5))
insert [huang]
select 12,'false','false','false','false' union all
select 12,'false','false','false','false' union all
select 12,'true','false','false','false' union all
select 23,'false','false','false','false' union all
select 23,'false','false','false','false' union all
select 23,'true','false','false','false'
--------------开始查询--------------------------select [taskid] ,CONVERT(VARCHAR(2),sum(CASE WHEN [iscall]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT(iscall))[iscall],
CONVERT(VARCHAR(2),sum(CASE WHEN [ismsg]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT([ismsg]))[ismsg],
CONVERT(VARCHAR(2),sum(CASE WHEN [isfax]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT([isfax]))[isfax],
CONVERT(VARCHAR(2),sum(CASE WHEN [isemail]='true' THEN 1 ELSE 0 END ))+'/'+CONVERT(VARCHAR(2),COUNT([isemail]))[isemail]
from [huang]
GROUP BY [taskid]
----------------结果----------------------------
/*
taskid iscall ismsg isfax isemail
----------- ------ ----- ----- -------
12 1/3 0/3 0/3 0/3
23 1/3 0/3 0/3 0/3
*/
TaskID IsCall IsMsg IsFax IsEmail total
select distinct TaskID ,(select sum(1) from 表 as a where a.TaskID=b.TaskID and IsCall=true) as 分子 ,(select sum(1) from 表 as a where a.TaskID=b.TaskID ) as 分母 from 表 as b
insert into #table
select * from (
select 1 as 'a',1 as 'b',1 as 'c'
union
select 1 as 'a',2 as 'b',2 as 'c'
union
select 2 as 'a',2 as 'b',2 as 'c'
union
select 2 as 'a',2 as 'b',1 as 'c'
) as wselect distinct a,(select convert(varchar(10),COUNT(1)) from #table as b where a.a=b.a and b.b=1)+'/'+(select convert(varchar(10),COUNT(1)) from #table as b where a.a=b.a)as w
from #table as a