如何统计不同部门的人数???????????????
我用一条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,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID) =
HE.EMPLOYEE_NUM(+)
--AND mmt.SUBINVENTORY_CODE = :P_SUBINVENTORY
AND MMT.TRANSFER_SUBINVENTORY = 'F060个人'
and BZ_ID is not null
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_2 = 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 NVL(YI.BZ_NAME, '没有定义班组'),
ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID)
UNION
SELECT NVL(cev.DEPT, '没有定义部门') 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,
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 ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID) =
rtrim(cev.EMP_NUM(+))
AND MMT.TRANSACTION_TYPE_ID = 101
AND ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID) =
HE.EMPLOYEE_NUM(+)
--AND mmt.SUBINVENTORY_CODE = :P_SUBINVENTORY
AND MMT.TRANSFER_SUBINVENTORY = 'F060个人' --and BZ_ID is not null
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_4 = 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 NVL(cev.DEPT, '没有定义部门'),
ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID)
我用一条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,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID) =
HE.EMPLOYEE_NUM(+)
--AND mmt.SUBINVENTORY_CODE = :P_SUBINVENTORY
AND MMT.TRANSFER_SUBINVENTORY = 'F060个人'
and BZ_ID is not null
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_2 = 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 NVL(YI.BZ_NAME, '没有定义班组'),
ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID)
UNION
SELECT NVL(cev.DEPT, '没有定义部门') 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,
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 ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID) =
rtrim(cev.EMP_NUM(+))
AND MMT.TRANSACTION_TYPE_ID = 101
AND ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID) =
HE.EMPLOYEE_NUM(+)
--AND mmt.SUBINVENTORY_CODE = :P_SUBINVENTORY
AND MMT.TRANSFER_SUBINVENTORY = 'F060个人' --and BZ_ID is not null
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_4 = 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 NVL(cev.DEPT, '没有定义部门'),
ZD_PUB_GET.GET_EMPLOYEE_NAME(MMT.TRANSFER_LOCATOR_ID,
MMT.TRANSFER_SUBINVENTORY,
MMT.ORGANIZATION_ID)
解决方案 »
- 急招Oracle ERP实施工程师
- 怎么与一个数组里的值比较,想知道如何用pl/sql写
- vs2003访问oracle数据库时出现 ORA-12560: TNS: 协议适配器错误
- 911 如果向oracle varchar2字段中插入&#的组合特殊字符
- oracle 存储过程如何返回 数组
- 数据库SQL 语句 sum() 和count() 查询速度问题???????
- 请问Oracle是否支持将XML保存至某一列中,并且可以用XQuery查询?
- 一条简单的语句
- 已经用exp命令导出数据,我想导入另外一台服务器,那台机器上已经安装了oracle,并且已经有数据库服务了,接下来我该怎么办?
- 请教!为何我把VFP中的表导入到oracle中后,在sql*plus用select看不到数据?在线等待
- 求MTL_DEMAND表的PRIMARY_UOM_QUANTITY、COMPLETED_QUANTITY详解
- 关于oracle登入问题
我看ls的就可以,还一种就是分析函数select distinct bz, sum(case when 1=1 then 1 else 0 end) num from tableName;
create table temp(
bz varchar2(10),
flag int
)insert into temp values('供应部',3675);
insert into temp values('供应部',3675);
insert into temp values('供应部',4007);
insert into temp values('拓展',3801);
insert into temp values('拓展',3862);
insert into temp values('工艺室',0313);
insert into temp values('工艺室',1327);
insert into temp values('工艺室',4140);
insert into temp values('工艺室',4142);select distinct t.bz,(select count(*) from temp where temp.bz=t.bz ) as total from temp t order by bz
结果:
BZ TOTAL
---------- ----------
工艺室 4
供应部 3
拓展 2
这个可能有点繁琐,不如1楼的来的那么简捷。另外2楼的语句那样写有点小错误,应该加上group by,正确写法应为:select distinct bz, sum(case when 1=1 then 1 else 0 end) num from temp group by bz;完毕!!
GROUP BY bz
SELECT bz, COUNT(DISTINCT flag) FROM (你的查询语句) GROUP BY bz