这条语句count(*)为何得不到select出来的总行数???

billlyh 2012-06-07 09:17:59
这条语句count(*)为何得不到select出来的总行数???
SELECT count(*),
we.wip_entity_name,
MSI2.SEGMENT1,
sum(WRO.REQUIRED_QUANTITY) QTY,
MSI.PRIMARY_UNIT_OF_MEASURE UOM,
wro.QUANTITY_PER_ASSEMBLY
FROM WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO,
INV.MTL_SYSTEM_ITEMS MSI,
WIP.WIP_OPERATIONS WO,
INV.MTL_SYSTEM_ITEMS MSI2
,wip.wip_entities we
WHERE WDJ.STATUS_TYPE IN (1, 3, 6)
AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = 4
AND MSI.ORGANIZATION_ID = 4
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND nvl(WRO.QUANTITY_ISSUED, 0) = 0
and nvl(WRO.REQUIRED_QUANTITY, 0) > 0
AND MSI2.ORGANIZATION_ID = 4
AND WDJ.PRIMARY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
and wdj.wip_entity_id=we.wip_entity_id
and substr(MSI.SEGMENT1,1,1) in ('7')
and MSI.SEGMENT1 like '7190-020160%'
group by
we.wip_entity_name,
MSI2.SEGMENT1,
MSI.PRIMARY_UNIT_OF_MEASURE ,
wro.QUANTITY_PER_ASSEMBLY
结果如下:
count(*) wip_entity_name SEGMENT1 qty uom QUANTITY_PER_ASSEMBLY
1 FJ2012060501 3459-403180 18.6 kg 9.3
1 FJ2012060502 3459-403180 18.6 kg 9.3
明明是行数是两行,我希望能得到行数2,但count(*)却是1,应该怎么改语句啊
...全文
180 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
caoleione 2012-06-07
  • 打赏
  • 举报
回复
在group by中count取得是分组的统计行数 可以改为count(count(*))over() 就能取出分组后数据的行数和
  • 打赏
  • 举报
回复
提供下表结构和数据
  • 打赏
  • 举报
回复
你把你用到的所有的表的表结构发出来吧。。最好还能提供数据
billlyh 2012-06-07
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 的回复:]
over属于开窗函数 如果要加东西 可用partition by col order by col 你的SQL直接改成count(count(*))over()就行 不要加we.wip_entity_name
[/Quote]
SELECT count(count(*))over(),
--count(*),
we.wip_entity_name,
MSI2.SEGMENT1,
--count(MSI2.SEGMENT1),
MSI2.DESCRIPTION,

WDJ.PRIMARY_ITEM_ID P_ITEM_ID,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1 segment3,
MSI.DESCRIPTION description1,
wdj.START_QUANTITY,
WO.ATTRIBUTE1,
wo.attribute2,
WRO.DATE_REQUIRED N_DATE,
--sum(WRO.REQUIRED_QUANTITY) QTY,
MSI.PRIMARY_UNIT_OF_MEASURE UOM,
wro.QUANTITY_PER_ASSEMBLY,
'JOB' TYPE
FROM WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO,
INV.MTL_SYSTEM_ITEMS MSI,
WIP.WIP_OPERATIONS WO,
INV.MTL_SYSTEM_ITEMS MSI2
,wip.wip_entities we
WHERE WDJ.STATUS_TYPE IN (1, 3, 6)
--AND substr(WDJ.CLASS_CODE,1,1) in ( 'J','B')
AND (WDJ.CLASS_CODE || '' like 'J%' OR WDJ.CLASS_CODE || '' like 'B%' OR
WDJ.CLASS_CODE || '' LIKE 'FXJ%' OR
WDJ.CLASS_CODE || '' LIKE 'FXB%')
AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = 4
AND MSI.ORGANIZATION_ID = 4
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ((length(msi.segment1) < 12 and
(substr(MSI.SEGMENT1, 1, 1) in ('4', '7'))) or
(length(msi.segment1) = 12 and
substr(MSI.SEGMENT1, 1, 1) in ('5', '7')))

AND WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND nvl(WRO.QUANTITY_ISSUED, 0) = 0
and nvl(WRO.REQUIRED_QUANTITY, 0) > 0
--AND WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED>0
AND MSI2.ORGANIZATION_ID = 4
AND WDJ.PRIMARY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
--AND (WRO.DATE_REQUIRED >= :P_DATE and WRO.DATE_REQUIRED <= :P_DATE1)
and wdj.wip_entity_id=we.wip_entity_id
--AND (WRO.DATE_REQUIRED >= to_date('&P_DATE', 'yymmdd') and WRO.DATE_REQUIRED < to_date('&P_DATE1', 'yymmdd'))+1
--&P_TIME
--and WE.WIP_ENTITY_NAME||'' LIKE :P_ENTITY||'%'
and WE.WIP_ENTITY_NAME||'' LIKE '&P_ENTITY'||'%'||''
--&P_CONDITION
--and WE.WIP_ENTITY_NAME LIKE 'FJ2012060501%'
and substr(MSI.SEGMENT1,1,1) in ('7')
--&p_1
and MSI.SEGMENT1||'' like '&p_2'||'%'
--&P_AD
--ORDER BY 1
group by
we.wip_entity_name,
MSI2.SEGMENT1,
MSI2.DESCRIPTION,

WDJ.PRIMARY_ITEM_ID ,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1 ,
MSI.DESCRIPTION ,
wdj.START_QUANTITY,
WO.ATTRIBUTE1,
wo.attribute2,
WRO.DATE_REQUIRED ,
MSI.PRIMARY_UNIT_OF_MEASURE ,
wro.QUANTITY_PER_ASSEMBLY
pl sql developer报错:
ORA-00923:未找到预期form关键字

billlyh 2012-06-07
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 的回复:]
over属于开窗函数 如果要加东西 可用partition by col order by col 你的SQL直接改成count(count(*))over()就行 不要加we.wip_entity_name
[/Quote]
pl sql developer报错:
ORA-00923:未找到预期form关键字
caoleione 2012-06-07
  • 打赏
  • 举报
回复
over属于开窗函数 如果要加东西 可用partition by col order by col 你的SQL直接改成count(count(*))over()就行 不要加we.wip_entity_name
老黎 2012-06-07
  • 打赏
  • 举报
回复
一个分组是行数是1,两个分组加在一起不就是2了嘛
billlyh 2012-06-07
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 的回复:]
count(count(*))over() 是分析函数
[/Quote]
我这样写
SELECT count(count(*))over(we.wip_entity_name)
pl sql developer报错:
ORA-00923:未找到预期form关键字
billlyh 2012-06-07
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 的回复:]
count(count(*))over() 是分析函数
[/Quote]
我们公司用的是oracle9i,count(count(*))over() 是9i以上版本才有的吧
caoleione 2012-06-07
  • 打赏
  • 举报
回复
count(count(*))over() 是分析函数
billlyh 2012-06-07
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 的回复:]
在group by中count取得是分组的统计行数 可以改为count(count(*))over() 就能取出分组后数据的行数和
[/Quote]
count(count(*))over() ???看不懂

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧