34,590
社区成员
发帖
与我相关
我的任务
分享
select 月份,姓名
,a=0-sum(abs(a1)/a1+abs(a2)/a2+abs(a3)/a3-3)/2
,b=0-sum(abs(b1)/a1+abs(b2)/a2+abs(b3)/a3-3)/2
from tb
declare @tb table (月份 int,姓名 varchar(20),A1 int, A2 int, A3 int,B1 int,B2 int,B3 int)
insert into @tb select 1,'张三',-1,4,-1,-1,4,-1
insert into @tb select 1,'李四',2,4,4,2,-5,-6
insert into @tb select 1,'张三',-3,2,4,-3,2,4
insert into @tb select 1,'李四',-5,-1,7,-5,-1,7
insert into @tb select 1,'张三',4,-9,-11,-4,-9,-11
insert into @tb select 1,'王五',-6,-4,-2,-6,4,-2
insert into @tb select 1,'王五',-1,-4,66,1,-4,66
select 月份,姓名,
sum(case when a1<0 then 1 else 0 end+case when a2<0 then 1 else 0 end +case when a3<0 then 1 else 0 end) as 'a',
sum(case when b1<0 then 1 else 0 end+case when b2<0 then 1 else 0 end +case when b3<0 then 1 else 0 end) as 'b'
from @tb
group by 月份,姓名
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (月份 int,姓名 varchar(4),A1 int,A2 int,A3 int,B1 int,B2 int,B3 int)
insert into #T
select 1,'张三',-1,4,-1,-1,4,-1 union all
select 1,'李四',2,4,4,2,-5,-6 union all
select 1,'张三',-3,2,4,-3,2,4 union all
select 1,'李四',-5,-1,7,-5,-1,7 union all
select 1,'张三',4,-9,-11,-4,-9,-11 union all
select 1,'王五',-6,-4,-2,-6,4,-2 union all
select 1,'王五',-1,-4,66,1,-4,66
select 月份,姓名,A=sum((1-sign(A1))/2+(1-sign(A2))/2+(1-sign(A3))/2), B=sum((1-sign(B1))/2+(1-sign(B2))/2+(1-sign(B3))/2) from #T group by 月份,姓名
/*
月份 姓名 A B
----------- ---- ----------- -----------
1 李四 2 4
1 王五 5 3
1 张三 5 6
*/
/******************************************************************************/
/*回复:20080526002总:00049 */
/*主题:求字段统计 */
/*作者:二等草 */
/******************************************************************************/
set nocount on
--数据--------------------------------------------------------------------------
declare @tb table([月份] int,[姓名] varchar(4),[A1] int,[A2] int,[A3] int,[B1] int,[B2] int,[B3] int)
insert into @tb select 1,'张三',-1,4,-1,-1,4,-1
insert into @tb select 1,'李四',2,4,4,2,-5,-6
insert into @tb select 1,'张三',-3,2,4,-3,2,4
insert into @tb select 1,'李四',-5,-1,7,-5,-1,7
insert into @tb select 1,'张三',4,-9,-11,-4,-9,-11
insert into @tb select 1,'王五',-6,-4,-2,-6,4,-2
insert into @tb select 1,'王五',-1,-4,66,1,-4,66
--代码--------------------------------------------------------------------------
select [月份],[姓名]
,a=sum(case when a1 >=0 then 0 else 1 end)+sum(case when a2 >=0 then 0 else 1 end)+sum(case when a3 >=0 then 0 else 1 end)
,a=sum(case when b1 >=0 then 0 else 1 end)+sum(case when b2 >=0 then 0 else 1 end)+sum(case when b3 >=0 then 0 else 1 end)
from @tb
group by [月份],[姓名] order by [月份],[姓名] desc
go
/*结果--------------------------------------------------------------------------
月份 姓名 a a
----------- ---- ----------- -----------
1 张三 5 6
1 王五 5 3
1 李四 2 4
--清除------------------------------------------------------------------------*/
/*
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(月份 int,姓名 varchar(6),A1 int,A2 int,A3 int,B1 int,B2 int,B3 int)
Go
Insert into ta
select 1,'张三',-1,4,-1,-1,4,-1 union all
select 1,'李四',2,4,4,2,-5,-6 union all
select 1,'张三',-3,2,4,-3,2,4 union all
select 1,'李四',-5,-1,7,-5,-1,7 union all
select 1,'张三',4,-9,-11,-4,-9,-11 union all
select 1,'王五',-6,-4,-2,-6,4,-2 union all
select 1,'王五',-1,-4,66,1,-4,66
Go
--Start
select 月份, 姓名, A = sum(a1), B = sum(b1)
from( select 月份, 姓名,
case when a1 < 0 then 1 else 0 end +
case when a2 < 0 then 1 else 0 end +
case when a3 < 0 then 1 else 0 end as a1,
case when b1 < 0 then 1 else 0 end +
case when b2 < 0 then 1 else 0 end +
case when b3 < 0 then 1 else 0 end as b1 from ta) c
group by 月份, 姓名
--Result:
/*
月份 姓名 A B
----------- ------ ----------- -----------
1 李四 2 4
1 王五 5 3
1 张三 5 6
(所影响的行数为 3 行)
*/
--End