日期:2014-05-17  浏览次数:20873 次

修改一个SQL语句,count()的使用方法
SELECT T1.TEAMID, --机构代码  
  T1.PERSONID, --需要统计的字段  
  (select AGESECTIONCODE from ANAREPORT11.CD_ZK_PERSON_AGE_SEG A where DECODE(T2.BIRTHDAY,
NULL,
0,
SUBSTR('2012-03-12', 1, 4) - TO_CHAR(T2.BIRTHDAY, 'YYYY'))>= A.Min
And
DECODE(T2.BIRTHDAY,
NULL,
0,
SUBSTR('2012-03-12', 1, 4) - TO_CHAR(T2.BIRTHDAY, 'YYYY'))<A.Max ) AGESECTIONCODE, --年龄分段代码  
  T2.EDUCATIONCODE, --学历代码  
  DECODE(T3.PERSONID, NULL, 0, 1) ISLEADER, --是否主管  
  T2.SEXCODE, --性别代码  
  (select WORKYEARSECTIONCODE from ANAREPORT11.cd_zk_person_workyear_seg B where DECODE (T2.BIRTHDAY,
NULL,
0,
SUBSTR('2012-03-12', 1, 4) - TO_CHAR(T2.BIRTHDAY, 'YYYY'))>= B.Min
And
DECODE(T2.BIRTHDAY,
NULL,
0,
SUBSTR('2012-03-12', 1, 4) - TO_CHAR(T2.BIRTHDAY, 'YYYY'))<B.Max ) WORKYEARSECTIONCODE, --从业年限分段代码  
  T2.RECRUITORIGINCODE -- 招募来源代码  
  FROM ANAREPORT11.ODS_SAL_TEAM_MEMBER T1  
  LEFT JOIN (SELECT *  
  FROM ANAREPORT11.ODS_SAL_PERSON  
  WHERE PERSONSTATUSCODE = '1') T2 ON T1.PERSONID =  
  T2.PERSONID  
  LEFT JOIN ANAREPORT11.ODS_SAL_TEAM_LEADER T3 ON T1.TEAMID = T3.TEAMID  
  AND T1.PERSONID =  
  T2.PERSONID  


红色字段是从另一个表中查询出来的数据或者表达式,我现在想统计count(T1.PERSONID),但是红色字段无法group by 怎么解决这个问题啊?

------解决方案--------------------
SQL code
count(T1.PERSONID)over(partition by 字段 order by 字段) 就不用在group by !

------解决方案--------------------
红色好多啊,看晕了,说你要实现的功能吧
分析函数可以代替group by的功能,是不是你需要的
比如楼上写的
count(T1.PERSONID)over(partition by 字段1 order by 字段2)
实际上就是以字段1进行了group by了