公安项目的一些统计语句

dolphin_ygj

浏览: 1194263 次
性别:
来自: 上海

最近访客更多访客>>

knight_black_bob

morelily

llshxy

liyanwei66

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

sql2000

C C++C#

-- 统计报名
select
sum(case
      WHEN aa.A0107='1' and people.applied_state='1' THEN 1
      ELSE 0
    END
   )AS appliedPassedManCount,

sum(case
      WHEN aa.A0107='2' and people.applied_state='1' THEN 1
      ELSE 0
    END
   )AS appliedPassedWomanCount,

sum(case
      WHEN people.applied_state='1' THEN 1
      ELSE 0
    END
   )AS appliedPassedCount,

sum(case
      WHEN aa.a0107='1' and people.applied_state='2' THEN 1
      ELSE 0
    END
   )AS waitManCount,

sum(case
      WHEN aa.a0107='2' and people.applied_state='2' THEN 1
      ELSE 0
    END
   )AS waitWomanCount,
sum(case
      WHEN people.applied_state='2' THEN 1
      ELSE 0
    END
   )AS waitCount,

sum(case
      WHEN aa.a0107='1' and people.applied_state='0' THEN 1
      ELSE 0
    END
   )AS refusedManCount,

sum(case
      WHEN aa.a0107='2' and people.applied_state='0' THEN 1
      ELSE 0
    END
   )AS refusedWomanCount,
sum(case
      WHEN people.applied_state='0' THEN 1
      ELSE 0
    END
   )AS refusedCount
from A001A001 aa , train_people people where aa.B0110=people.B0110 and aa.A0100=people.A0100 and   aa.B0110 like '324%' and people.train_id=4

--统计培训结果
select
sum(case
      WHEN aa.A0107='1' and people.check_state='1' THEN 1
      ELSE 0
    END
   )AS passedManCount,

sum(case
      WHEN aa.A0107='2' and people.check_state='1' THEN 1
      ELSE 0
    END
   )AS passedWomanCount,

sum(case
      WHEN people.check_state='1' THEN 1
      ELSE 0
    END
   )AS resultPassedCount,

sum(case
      WHEN aa.a0107='1' and people.check_state='2' THEN 1
      ELSE 0
    END
   )AS waitManCount,

sum(case
      WHEN aa.a0107='2' and people.check_state='2' THEN 1
      ELSE 0
    END
   )AS waitWomanCount,
sum(case
      WHEN people.check_state='2' THEN 1
      ELSE 0
    END
   )AS waitCount,

sum(case
      WHEN aa.a0107='1' and people.check_state='0' THEN 1
      ELSE 0
    END
   )AS refusedManCount,

sum(case
      WHEN aa.a0107='2' and people.check_state='0' THEN 1
      ELSE 0
    END
   )AS refusedWomanCount,
sum(case
      WHEN people.check_state='0' THEN 1
      ELSE 0
    END
   )AS refusedCount,
sum(case
      WHEN aa.a0107='1' and people.check_state='3' THEN 1
      ELSE 0
    END
   )AS transientManCount,

sum(case
      WHEN aa.a0107='2' and people.check_state='3' THEN 1
      ELSE 0
    END
   )AS transientWomanCount,
sum(case
      WHEN people.check_state='3' THEN 1
      ELSE 0
    END
   )AS transientCount
from A001A001 aa , train_people people where aa.B0110=people.B0110 and aa.A0100=people.A0100 and people.applied_state=1 and aa.B0110 like '324%' and people.train_id=4

---------------------------------------------

select b.id,a.B0110,a.A0100,a.A0101,a.A0107,a.A0177,a.E0122,b.applied_state
from A001A001 a,train_people b ,
(SELECT A001A001.B0110,A001A001.A0100 FROM (A001A001 WITH(NOLOCK) Left Join A002A001 WITH(NOLOCK) ON A002A001.A0100=A001A001.A0100 AND A002A001.B0110=A001A001.B0110 AND Left(A002A001.Status,1)='1' ) WHERE (A001A001.E0122 LIKE '32402%') AND ((ISNULL(A001A001.A0107,'') = '2' AND ISNULL(A002A001.A0405,'') = '21' )) ) c
where a.B0110=b.B0110 and a.A0100=b.A0100 and c.B0110=b.B0110 and c.A0100=b.A0100 and b.train_id=9 and b.B0110 like '%32402%' and ( b.applied_state=0 or b.applied_state=2 )

分享到：