oracle按部门人数最少的名称,怎么统计不同部门的人数?

当前位置:我的异常网» Oracle管理 » 怎么统计不同部门的人数?

怎么统计不同部门的人数?

www.myexceptions.net  网友分享于：2013-08-22  浏览：20次

如何统计不同部门的人数???????????????

如何统计不同部门的人数???????????????

我用一条sql语句得到的数据如下:(BZ为部门名称，FLAG为员工编号)

BZ FLAG

A1供应部 3675

A1供应部 3675

A1供应部 4007

D42拓展 3801

D42拓展 3862

H3工艺室 0313

H3工艺室 1327

H3工艺室 4140

H3工艺室 4142

但是我希望能得到下面的这个结果:增加一列，统计出不同部门的人数

BZ tcount

A1供应部 3

D42拓展 2

H3工艺室 4

应该如何改写我写的这条sql语句呢?????????????

sql语句如下：

SELECT NVL(YI.BZ_NAME, '没有定义班组') BZ,

NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001') FLAG

FROM INV.MTL_MATERIAL_TRANSACTIONS MMT,

BOM.CST_ITEM_COSTS CIC,

APPS.HR_EMPLOYEES HE,

YUANGONG_INFO YI

WHERE MMT.ORGANIZATION_ID = 4

AND NVL(CIC.ORGANIZATION_ID, 4) = 4

AND NVL(CIC.COST_TYPE_ID, 1) = 1

AND NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001') = YI.YG_ID(+)

AND (MMT.TRANSACTION_TYPE_ID = 31 OR MMT.TRANSACTION_TYPE_ID = 41)

and NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001') =

HE.EMPLOYEE_NUM(+)

--AND mmt.SUBINVENTORY_CODE = :P_SUBINVENTORY

AND MMT.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID(+)

--AND MMT.TRANSACTION_DATE < :P_DATE_E + 1

--AND MMT.TRANSACTION_DATE >= :P_DATE_F

--AND :P_1 = 1

AND mmt.SUBINVENTORY_CODE = '&P_SUBINVENTORY'

AND MMT.TRANSACTION_DATE < to_date('&P_DATE_E','yymmdd') + 1

AND MMT.TRANSACTION_DATE >= to_date('&P_DATE_F','yymmdd')

GROUP YI.BZ_NAME,

NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001')

UNION

SELECT NVL(cev.DEPT, '没有定义部门') BZ,

NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001') FLAG

FROM INV.MTL_MATERIAL_TRANSACTIONS MMT,

BOM.CST_ITEM_COSTS CIC,

APPS.HR_EMPLOYEES HE,

cux_employee_v@hrca CEV

WHERE MMT.ORGANIZATION_ID = 4

AND NVL(CIC.ORGANIZATION_ID, 4) = 4

AND NVL(CIC.COST_TYPE_ID, 1) = 1

AND NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001') =

rtrim(cev.EMP_NUM(+))

AND (MMT.TRANSACTION_TYPE_ID = 31 OR MMT.TRANSACTION_TYPE_ID = 41)

and NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001') =

HE.EMPLOYEE_NUM(+)

--AND mmt.SUBINVENTORY_CODE = :P_SUBINVENTORY

AND MMT.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID(+)

--AND MMT.TRANSACTION_DATE < :P_DATE_E + 1

--AND MMT.TRANSACTION_DATE >= :P_DATE_F

--AND :P_3 = 1

AND mmt.SUBINVENTORY_CODE = '&P_SUBINVENTORY'

AND MMT.TRANSACTION_DATE < to_date('&P_DATE_E','yymmdd') + 1

AND MMT.TRANSACTION_DATE >= to_date('&P_DATE_F','yymmdd')

GROUP BY cev.DEPT,

NVL(SUBSTR(TRANSACTION_REFERENCE, 14, 4), '001')

UNION

SELECT NVL(YI.BZ_NAME, '没有定义班组') BZ,

ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,

MMT.TRANSFER_SUBINVENTORY,

MMT.ORGANIZATION_ID) FLAG

FROM INV.MTL_MATERIAL_TRANSACTIONS MMT,

BOM.CST_ITEM_COSTS CIC,

APPS.HR_EMPLOYEES HE,

YUANGONG_INFO YI

WHERE MMT.ORGANIZATION_ID = 4

AND NVL(CIC.ORGANIZATION_ID, 4) = 4

AND NVL(CIC.COST_TYPE_ID, 1) = 1

AND ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,

MMT.TRANSFER_SUBINVENTORY,

MMT.ORGANIZATION_ID) = YI.YG_ID(+)

AND MMT.TRANSACTION_TYPE_ID = 101

AND ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,

文章评论