<select id="queryProducingPlanData" resultType="hashmap">
SELECT T3.NAME AS PROJECT_NAME,
T0.DOC_ITEMATTR2 DOC_ITEMATTR2,
T0.ENG_SECTION,
T0.TOTAL,
T0.TOTAL_WEIGHT,
NVL(T2.FINISH_NUM,0) AS FINISH_NUM,
(NVL(T2.FINISH_NUM,0) - NVL(T1.STOCK_NUM,0) - NVL(T1.SEND_NUM,0)) as NO_TAG_NUM,
NVL(T1.STOCK_NUM,0) AS STOCK_NUM,
NVL(T1.SEND_NUM,0) AS SEND_NUM,
NVL(T1.STOCK_WEIGHT,0) AS STOCK_WEIGHT,
T0.PROJECT_ID
FROM (SELECT DI.PROJECT_ID,
DI.ENG_SECTION, MAX(DI.DOC_ITEMATTR2) DOC_ITEMATTR2,
SUM(DI.UNIT_QTY) AS TOTAL, SUM(DI.WEIGHT*DI.UNIT_QTY) AS TOTAL_WEIGHT
FROM DN_DOCUMENT_ITEMS DI
INNER JOIN
DN_DOCUMENT D
ON D.DOC_NUM = DI.DOC_NUM
AND D.DOC_SUBNDX = DI.DOC_SUBNDX
WHERE D.CLIENT_ID = #{clientId,jdbcType=VARCHAR}
AND D.LOCKED = 'Y'
AND D.TYPECODE = 'RQ08'
<if test="subType !=null and subType != ''">
AND DI.SUB_TYPECODE = #{subType,jdbcType=VARCHAR}
</if>
<if test="projectId !=null and projectId != ''">
AND DI.PROJECT_ID = #{projectId,jdbcType=DECIMAL}
</if>
<if test="engSection !=null and engSection != ''">
AND DI.ENG_SECTION like '%'||#{engSection,jdbcType=VARCHAR}||'%'
</if>
GROUP BY DI.PROJECT_ID, DI.ENG_SECTION) T0
LEFT JOIN (SELECT PP.PROJECT_ID,
P.ATTR3,
SUM(CASE WHEN P.STATUS_CODE
IN('1010','1020') THEN P.WEIGHT ELSE 0 END) AS STOCK_WEIGHT,
SUM(CASE WHEN P.STATUS_CODE IN('1010','1020') THEN 1 ELSE
0 END) AS STOCK_NUM,
SUM(CASE WHEN P.STATUS_CODE IN('1051','1052','1070','1261') THEN 1 ELSE 0 END) AS SEND_NUM
FROM DN_PRODUCT
P
LEFT JOIN DN_PROJECT_PRODUCT PP
ON P.ID = PP.PRODUCT_ID
WHERE P.CLIENT_ID= #{clientId,jdbcType=VARCHAR}
<if test="subType !=null and subType != ''">
AND P.SUB_TYPECODE = #{subType,jdbcType=VARCHAR}
</if>
<if test="projectId !=null and projectId != ''">
AND PP.PROJECT_ID = #{projectId,jdbcType=DECIMAL}
</if>
<if test="engSection !=null and engSection != ''">
AND P.ATTR3 like '%'||#{engSection,jdbcType=VARCHAR}||'%'
</if>
GROUP BY PP.PROJECT_ID, P.ATTR3) T1 ON T0.PROJECT_ID = T1.PROJECT_ID AND T0.ENG_SECTION=T1.ATTR3
LEFT JOIN (SELECT
PR.PROJECT_ID, PR.ENG_SECTION, COUNT(1) FINISH_NUM
FROM DN_PRODUCT_REQUEST PR LEFT JOIN DN_PRODUCT P ON PR.PRODUCT_ID =
P.ID
WHERE PR.CLIENT_ID = #{clientId,jdbcType=VARCHAR}
AND PR.REQ_TYPECODE = 'RQ11'
AND PR.STATUS_CODE in('1010','1020')
<if test="subType !=null and subType != ''">
AND P.SUB_TYPECODE = #{subType,jdbcType=VARCHAR}
</if>
<if test="projectId !=null and projectId != ''">
AND PR.PROJECT_ID = #{projectId,jdbcType=DECIMAL}
</if>
<if test="engSection !=null and engSection != ''">
AND PR.ENG_SECTION like '%'||#{engSection,jdbcType=VARCHAR}||'%'
</if>
GROUP BY PR.PROJECT_ID, PR.ENG_SECTION) T2 ON T0.PROJECT_ID = T2.PROJECT_ID AND T0.ENG_SECTION=T2.ENG_SECTION
LEFT JOIN
DN_PROJECT T3 ON T0.PROJECT_ID = T3.ID ORDER BY PROJECT_NAME,ENG_SECTION
</select>
分享到:
相关推荐
SQL复杂SQL语句SQL复杂SQL语句SQL复杂SQL语句SQL复杂SQL语句
jsqlparser解析复杂sql 获取where条件字段 select字段 表名table 查询join 查询 group by 查询order by 子查询 判断是否为多级子查询
复杂sql语句,关于oracle数据库的复杂查询,复杂逻辑关系等等。是个不错的工具
常用复杂sql语句.txt 常用复杂sql语句.txt 常用复杂sql语句.txt
复杂sql说明大全
描述复杂SQL语句的文档,是有关java的学习文档文档文档是有关java的学习文档文档文档
此文件包含长期以来对查询统计的全面总结:包含多表的复杂查询条件,级索引创建,触发器的使用,存储过程的编写,复杂sql的优化。是查询效率提高到小于0.1s
Oracle复杂查询和索引优化,函数的使用,行列的转换,去重等等
复杂sql查询语句,非常详细
今天小编就为大家分享一篇关于MySQL常用SQL语句总结包含复杂SQL查询,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
工作中常用的SQL + 超复杂SQL 交叉连接查询corss.txt 公交线路问题.txt 。。。。 充储过程--sql0.sql 分割字符串.txt 分布式查询.txt 分组排列进行更新.txt 别名.txt 动态SQL语句.txt 动态语句.txt 区分大小写.txt ...
数据库实验二复杂SQL数据操作.doc
主要介绍了JPA多条件复杂SQL动态分页查询功能,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
基于复杂SQL查询自动汇总表的应用研究.pdf
基于复杂SQL查询的有效审计PPT学习教案.pptx
PostGresql工作中一条复杂SQL解析业务背景订单表结构实现SQLSQL解析子查询SQL:查询订单最近7天付费的用户数辅助SQL:查询连续7天的日期连接SQL:使用Left Join 连接查询连续的日期付费人数格式化SQL:付费人数为...
sql复杂查询例子大全sql复杂查询例子大全
用DAO在数据库中执行SQL语句sql.rar,很不错的vc源码,希望能对大家有所帮助
SQLServer数据库复杂查询示例,带数据库脚本。希望能帮助学习SQL语言的读者。