使用left join后添加判断会出错

最新推荐文章于 2024-07-22 02:54:03 发布
原创 最新推荐文章于 2024-07-22 02:54:03 发布 · 2.6w 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #sql #filter

本文讲解了在使用leftjoin语句时，如何正确放置过滤条件以确保结果的准确性。强调左表过滤应置于where条件中，右表过滤则应在on条件中实现。

使用left join后条件判断会不准怎么办？

请注意

在left join语句中，左表过滤必须放where条件中，右表过滤必须放on条件中，这样结果才能不多不少，刚刚好。

zhige@
关注
终极笔记：MySQL N个连接：left join、inner join等，效率飙升300%？深度解析与实战
java专栏
09-11 1046
MySQL 8.0连接操作深度解析 MySQL 8.0中，连接（JOIN）操作是数据处理的核心。本文解析7种连接类型的本质差异、性能影响（高达300%效率差距）及实战优化方案： 连接类型对比 INNER JOIN：仅返回匹配行（高效） LEFT JOIN：保留左表全部数据（需注意NULL值） CROSS JOIN：笛卡尔积（高风险） FULL JOIN：需用UNION模拟（性能差） 性能关键 索引命中率决定查询速度 小表驱动大表原则 大数据量避免CROSS JOIN 实战陷阱 LEFT JOIN后需显式处理
在Windows和Linux系统下安装并配置JDK环境
m0_72758098的博客
04-12 462
1 各平台JDK的下载首先进行的是JDK的下载，Oracle官网的下载实在是太慢了，而且很难找到以往版本的JDK，所以这里我推荐一个博主自己搭的网站下载，里面有各个版本、各个平台的JDK。下载好的文件如下图：2 后续步骤1）Windows系统2.1 安装JDK在Windows下安装JDK的话只需要根据引导安装，不需要Linux下相对复杂的步骤，但要注意的一点是要记住自己安装好的Java目录。比如我安装的位置就是安装好的目录如下图所示：2.2 配置环境变量。
错误使用LEFT JOIN 导致错误数据释疑
sql server的专栏
12-10 2973
昨天，公司的某位熟悉业务和SQL的人员，着急忙慌的找我，说必须要解决一个线上的SQL的问题，发现数据对不上了，搞不定挨批事小，但数据错误事关重大。责无旁贷的赶紧和他一起分析这个SQL。其...
Left Join error
街有三旬的博客
08-14 1463
之前写链接查询的时候遇到了个小错误,记录下:  SELECT *  FROM ilike_class_sign as s1     right  join (SELECT *  FROM ilike_student  where 1=1 AND name like CONCAT("%",1,"%")  and status =0)s2     on    s1.student_id = ...
left join on多条件深度理解
热门推荐
cxywangshun的博客
04-28 3万+
left join on多条件深度理解 核心：理解左连接的原理！ 左连接不管怎么样，左表都是完整返回的 当只有一个条件a.id=b.id的时候： 左连接就是相当于左边一条数据，匹配右边表的所有行，满足on后面的第一个条件a.id=b.id的进行返回 当有两个条件的时候a.id=b.id and a.age>100（当第二个条件进行左表筛选时） 就是左边这张表只有a.age>100的行，才会参与右表的每行匹配（但是a.age<100的行也会返回，只不过age<100的行是不可能匹配到
mysql left join 用法说明
weixin_30312563的博客
05-23 240
left join中关于where和on条件的几个知识点： 1.多表left join是会生成一张临时表，并返回给用户 2.where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。 3.on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL 4.on条件中如果有对...
mysql left jion 中怎么使用条件判断
weixin_40567813的博客
07-22 197
MySQL LEFT JOIN 条件判断使用方案 在数据库查询中，LEFT JOIN 是一种常用的操作，用于将两个表根据某个条件连接起来。然而，有时我们可能需要在 LEFT JOIN 中加入额外的条件判断，以满足特定的查询需求。本文将通过一个具体问题，展示如何在 LEFT JOIN 中使用条件判断，并使用饼状图和类图来辅助...
ORACLE库中LEFT JOIN 与字段中写子查循是否结果一样
09-19
首先，理解用户的问题：用户想知道在ORACLE数据库中，使用LEFT JOIN和在WHERE子句中使用子查询是否会得到相同的结果。用户明确提到LEFT JOIN和字段中写子查询，所以我需要比较这两种方法。 回顾用户提供的引用： -...
USE [jnjhoco_202506] GO /****** Object: View [dbo].[V_BCODE_DETAIL] Script Date: 2025/9/10 11:44:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[V_BCODE_DETAIL] ( ID, CODE, REF_CODE, REF_TYPE, COMPANY_CODE, COMPANY_FULL_NAME, BUDGET_YEAR, BUDGET_MONTH, CREATOR_USER_NAME, CREATOR_REAL_NAME, PROPOSER_USER_NAME, PROPOSER_REAL_NAME, CREATE_DATE, EXP_LV3, EXP_LV2, EXP_LV1, TP_CODE, TP_NAME, CP_CODE, CP_NAME, PAYER_CODE, PAYER_NAME_CN, VENDOR_CODE, VENDOR_NAME_CN, SOLDTO_CODE, SOLDTO_NAME_CN, IS_CLOSE, BLOCK, IS_NEED_FD, REMARK, CURRENCY_CODE, BC_PLAN, BC_ACT, BC_REV, BC_IN_PP, BC_PP, BC_IN_PC, BC_IN_NPC, BC_IN_REV, DIR_CUS_CODE, CUS_ACCOUNT, BU, LINE_FROM_DATE, LINE_TO_DATE, SHIPMENT_BEGIN_DATE, SHIPMENT_END_DATE, BRAND, APPROVE_DATE, LOCK_ALLOC, LOCK_AUTO, LOCK_FD, LOCK_KC, LOCK_SA, LOCK_DEBATABLE_TP, LOCK_OFFINV, LOCK_NEG, LOCK_DZ, CUS_CODE, CUS_NAME, CUS_NKA_NAME, BC_STATUS, PR_APP_TYPE, PAYLINK_CODE, PAYLINK_NAME, USER_FD_CODE, KC_TYPE, DUG_CODE, HYPER_ACT_CODE, HYPER_ACT_NAME, BC_FROM_DATE, BC_TO_DATE, FD_COST_TYPE, FD_BRANDS, TZ_CODE, ISVALID, MATCH_AMOUNT, MATCH_STATUS, AMOUNT_APPROVE_DATE, TZ_BRAND_CODE, TZ_EXPENSE_TYPE_CODE, TZ_SKU_CODE, TZ_BEGIN_DATE, TZ_END_DATE, SKU_CODE ) AS SELECT –ROW_NUMBER() OVER (ORDER BY CODE) AS ROW_ID, TEMP.ID AS ID, TEMP.CODE, TEMP.REF_CODE, TEMP.REF_TYPE, TEMP.COMPANY_CODE, TEMP.COMPANY_FULL_NAME, TEMP.BUDGET_YEAR, TEMP.BUDGET_MONTH, TEMP.CREATOR_USER_NAME, TEMP.CREATOR_REAL_NAME, TEMP.PROPOSER_USER_NAME, TEMP.PROPOSER_REAL_NAME, TEMP.CREATE_DATE, TEMP.EXP_LV3, TEMP.EXP_LV2, TEMP.EXP_LV1, TEMP.TP_CODE, TEMP.TP_NAME, TEMP.CP_CODE, TEMP.CP_NAME, TEMP.PAYER_CODE, TEMP.PAYER_NAME_CN, TEMP.VENDOR_CODE, TEMP.VENDOR_NAME_CN, TEMP.SOLDTO_CODE, TEMP.SOLDTO_NAME_CN, TEMP.IS_CLOSE, TEMP.BLOCK, TEMP.IS_NEED_FD, TEMP.REMARK, TEMP.CURRENCY_CODE, TEMP.BC_PLAN, TEMP.BC_ACT, TEMP.BC_REV, TEMP.BC_IN_PP, TEMP.BC_PP, TEMP.BC_IN_PC, TEMP.BC_IN_NPC, TEMP.BC_IN_REV, TEMP.DIR_CUS_CODE, TEMP.CUS_ACCOUNT, TEMP.BU, TEMP.LINE_FROM_DATE, TEMP.LINE_TO_DATE, TEMP.SHIPMENT_BEGIN_DATE, TEMP.SHIPMENT_END_DATE, TEMP.BRAND, TEMP.APPROVE_DATE, TEMP.LOCK_ALLOC, TEMP.LOCK_AUTO, TEMP.LOCK_FD, TEMP.LOCK_KC, TEMP.LOCK_SA, TEMP.LOCK_DEBATABLE_TP, TEMP.LOCK_OFFINV, TEMP.LOCK_NEG, TEMP.LOCK_DZ, TEMP.CUS_CODE, TEMP.CUS_NAME, TEMP.CUS_NKA_NAME, (CASE WHEN IS_CLOSE = 1 THEN ‘Closed’ WHEN BLOCK = 1 THEN ‘Locked’ WHEN BC_IN_NPC+BC_IN_PC+BC_IN_PP+BC_IN_REV =0 THEN ‘Open’ ELSE ‘In Process’ END) AS BC_STATUS ,TEMP.PR_APP_TYPE ,TEMP.PAYLINK_CODE ,TEMP.PAYLINK_NAME ,TEMP.USER_FD_CODE ,TEMP.KC_TYPE ,TEMP.DUG_CODE ,TEMP.HYPER_ACT_CODE ,TEMP.HYPER_ACT_NAME ,TEMP.BC_FROM_DATE ,TEMP.BC_TO_DATE ,TEMP.FD_COST_TYPE ,TEMP.FD_BRANDS ,TEMP.TZ_CODE ,TEMP.ISVALID ,TEMP.MATCH_AMOUNT ,(CASE WHEN TEMP.BU not in(select CKEY from T_BI_CHOOSE_OPTION where code=‘PAYMENT_MATCH_CUSTOMER’) THEN ‘无需匹配’ WHEN MATCH_AMOUNT = 0 THEN ‘未匹配’ WHEN BC_PLAN-BC_REV-BC_IN_REV = MATCH_AMOUNT THEN ‘匹配完成’ WHEN MATCH_AMOUNT < BC_PLAN THEN ‘部分匹配’ END) as MATCH_STATUS ,TEMP.AMOUNT_APPROVE_DATE ,TEMP.TZ_BRAND_CODE AS TZ_BRAND_CODE ,TEMP.TZ_EXPENSE_TYPE_CODE AS TZ_EXPENSE_TYPE_CODE ,TEMP.TZ_SKU_CODE AS TZ_SKU_CODE ,TEMP.TZ_BEGIN_DATE AS TZ_BEGIN_DATE ,TEMP.TZ_END_DATE AS TZ_END_DATE ,TEMP.SKU_CODE AS SKU_CODE FROM ( –normal TP bcode, with TP appline SELECT bc.ID, bc.CODE, bc.REF_CODE as REF_CODE, bc.REF_TYPE as REF_TYPE, company.CODE as COMPANY_CODE, company.NAME_FULL as COMPANY_FULL_NAME, bc.BUDGET_YEAR, bc.BUDGET_MONTH, sys.USERNAME as CREATOR_USER_NAME, sys.realName as CREATOR_REAL_NAME, sysu.USERNAME as PROPOSER_USER_NAME, sysu.realName as PROPOSER_REAL_NAME, bc.CREATE_DATE, expLV3.CODE as EXP_LV3, expLV2.CODE as EXP_LV2, expLV1.CODE as EXP_LV1, case when hact.CODE is null then act.CODE else null end as TP_CODE, case when hact.CODE is null then act.NAME else null end as TP_NAME, case when hact.CODE is null then cps.CODE else null end as CP_CODE, case when hact.CODE is null then cps.NAME else null end as CP_NAME, payer.CODE as PAYER_CODE, payer.NAME_CN as PAYER_NAME_CN, vendor.CODE as VENDOR_CODE, vendor.NAME_CN as VENDOR_NAME_CN, soldto.CODE as SOLDTO_CODE, soldto.NAME_CN as SOLDTO_NAME_CN, bc.IS_CLOSE, bc.BLOCK, bc.IS_NEED_FD, bc.REMARK, currency.CODE as CURRENCY_CODE, bc.BC_PLAN, bc.BC_ACT, bc.BC_REV, bc.BC_IN_PP, bc.BC_PP, bc.BC_IN_PC, bc.BC_IN_NPC, bc.BC_IN_REV, cus.SOLDTO_CODE as DIR_CUS_CODE, acc.CODE as CUS_ACCOUNT, cus.BU as BU, line.LINE_BEGIN_DATE as SHIPMENT_BEGIN_DATE, line.LINE_END_DATE as SHIPMENT_END_DATE, line.SHIPMENT_BEGIN_DATE as LINE_FROM_DATE, line.SHIPMENT_END_DATE as LINE_TO_DATE, brandtp.CODE as BRAND, head.APPROVE_DATE, bc.LOCK_ALLOC, bc.LOCK_AUTO, bc.LOCK_FD, bc.LOCK_KC, bc.LOCK_SA, bc.LOCK_DEBATABLE_TP, bc.LOCK_OFFINV, bc.LOCK_NEG, bc.LOCK_DZ, cus.CODE as CUS_CODE, cus.NAME as CUS_NAME, cus.NKA_NAME as CUS_NKA_NAME ,bc.PR_APP_TYPE ,bc.PAYLINK_CODE ,PL.NAME_CN AS PAYLINK_NAME ,bc.USER_FD_CODE ,bc.KC_TYPE ,dug.CODE as DUG_CODE ,hact.CODE as HYPER_ACT_CODE ,hact.NAME as HYPER_ACT_NAME – ,(select MIN(fd.FROM_DATE) from T_TP_FD_BC fdbc left join T_TP_FD fd on fdbc.FD_HEAD_ID=fd.ID where fdbc.BCODE_ID=bc.ID) as BC_FROM_DATE – ,(select MAX(fd.TO_DATE) from T_TP_FD_BC fdbc left join T_TP_FD fd on fdbc.FD_HEAD_ID=fd.ID where fdbc.BCODE_ID=bc.ID) as BC_TO_DATE ,bc.FD_FROM_DATE as BC_FROM_DATE ,bc.FD_TO_DATE as BC_TO_DATE ,bc.FD_COST_TYPE ,bc.FD_BRANDS ,tz.code as TZ_CODE ,tz.isvalid as ISVALID ,bc.MATCH_AMOUNT ,bc.AMOUNT_APPROVE_DATE ,tzBrand.CODE AS TZ_BRAND_CODE ,tzExpenseType.CODE as TZ_EXPENSE_TYPE_CODE ,tzMaterial.CODE as TZ_SKU_CODE ,tz.BEGIN_DATE as TZ_BEGIN_DATE ,TZ.END_DATE AS TZ_END_DATE ,sku.code as SKU_CODE from T_PR_BC as bc left join T_BI_COMPANY as company on bc.COMPANY_ID = company.id left join T_BI_USER as u on bc.CREATOR_ID = u.ID left join SYSUSER as sys on u.SYSUSERID = sys.ID left join T_BI_USER as uu on bc.PROPOSER_ID = uu.ID left join SYSUSER as sysu on uu.SYSUSERID = sysu.ID left join T_BI_EXPENSE_TYPE as expLV3 on bc.COST_TYPE_ID = expLV3.ID left join T_BI_EXPENSE_TYPE as expLV2 on expLV3.P_ID = expLV2.ID left join T_BI_EXPENSE_TYPE as expLV1 on expLV2.P_ID = expLV1.ID left join T_BI_ACTIVITY as act on bc.ACT_ID = act.ID left join T_BI_CPSUBTYPE as cps on bc.CP_SUTYPE_ID = cps.ID left join T_SAP_CUSTOMER_FUCTION as pay on bc.PAYER_ID = pay.ID left join T_SAP_CUSTOMER as payer on pay.CUSTOMER = payer.ID left join T_SAP_VENDOR as vendor on bc.VENDOR_ID = vendor.ID left join T_SAP_CUSTOMER_FUCTION as sold on bc.SOLDTO_ID = sold.ID left join T_SAP_CUSTOMER as soldto on sold.CUSTOMER = soldto.ID left join T_BI_CURRENCY as currency on bc.CUR_ID = currency.ID left join T_TP_APP_LINE as line on bc.REF_ID = line.ID left join T_BI_MATERIAL as sku on bc.SKU_ID = sku.ID left join T_BI_BRAND as brandtp on bc.BRAND_ID = brandtp.ID left join T_TP_APP as head on line.TP_APP_ID = head.ID left join V_BI_CUSTOMER as cus on (cus.CUS_ID=bc.CUS_ID and cus.BUDGET_YEAR=bc.BUDGET_YEAR) left join T_BI_CUSTOMER_ACCOUNT as acc on cus.CUS_ACCOUNT_ID = acc.ID left join T_SAP_CUSTOMER as PL on bc.PAYLINK_CODE=PL.CODE and PL.COCD=‘7049’ and PL.BLOCK=0 left join T_BI_DUG dug on cus.DUG_ID=dug.ID left join T_BI_HYPER_ACTIVITY hact on bc.HYPER_ACT_ID=hact.ID left join T_PR_BC_TZ tz on tz.bcode_id=bc.ID and tz.isValid=1 LEFT JOIN T_BI_EXPENSE_TYPE tzExpenseType ON tzExpenseType.ID =bc.NEW_COST_TYPE_ID LEFT JOIN T_BI_BRAND tzBrand on tzBrand.ID = bc.NEW_BRAND_ID LEFT JOIN T_BI_MATERIAL tzMaterial on tzMaterial.id = bc.NEW_SKU_ID where REF_TYPE=‘TP’ AND KC_TYPE IN(99,3) UNION ALL –kc tp bcode, without tp appline select bc.ID, bc.CODE, bc.REF_CODE as REF_CODE, bc.REF_TYPE as REF_TYPE, company.CODE as COMPANY_CODE, company.NAME_FULL as COMPANY_FULL_NAME, bc.BUDGET_YEAR, bc.BUDGET_MONTH, sys.USERNAME as CREATOR_USER_NAME, sys.realName as CREATOR_REAL_NAME, sysu.USERNAME as PROPOSER_USER_NAME, sysu.realName as PROPOSER_REAL_NAME, bc.CREATE_DATE, expLV3.CODE as EXP_LV3, expLV2.CODE as EXP_LV2, expLV1.CODE as EXP_LV1, case when hact.CODE is null then act.CODE else null end as TP_CODE, case when hact.CODE is null then act.NAME else null end as TP_NAME, case when hact.CODE is null then cps.CODE else null end as CP_CODE, case when hact.CODE is null then cps.NAME else null end as CP_NAME, payer.CODE as PAYER_CODE, payer.NAME_CN as PAYER_NAME_CN, vendor.CODE as VENDOR_CODE, vendor.NAME_CN as VENDOR_NAME_CN, soldto.CODE as SOLDTO_CODE, soldto.NAME_CN as SOLDTO_NAME_CN, bc.IS_CLOSE, bc.BLOCK, bc.IS_NEED_FD, bc.REMARK, currency.CODE as CURRENCY_CODE, bc.BC_PLAN, bc.BC_ACT, bc.BC_REV, bc.BC_IN_PP, bc.BC_PP, bc.BC_IN_PC, bc.BC_IN_NPC, bc.BC_IN_REV, cus.SOLDTO_CODE as DIR_CUS_CODE, acc.CODE as CUS_ACCOUNT, cus.BU as BU, CAST(cast (bc.BUDGET_YEAR as varchar) + ‘-’ + cast(bc.BUDGET_MONTH as varchar) +‘-1’ as date) as LINE_FROM_DATE, dateadd(day,-1,dateadd(Month,1,CAST(cast (bc.BUDGET_YEAR as varchar) + ‘-’ + cast(bc.BUDGET_MONTH as varchar) +‘-1’ as date))) as LINE_TO_DATE, null as SHIPMENT_BEGIN_DATE, null as SHIPMENT_END_DATE, brandtp.CODE as BRAND, bc.CREATE_DATE AS APPROVE_DATE, bc.LOCK_ALLOC, bc.LOCK_AUTO, bc.LOCK_FD, bc.LOCK_KC, bc.LOCK_SA, bc.LOCK_DEBATABLE_TP, bc.LOCK_OFFINV, bc.LOCK_NEG, bc.LOCK_DZ, cus.CODE as CUS_CODE, cus.NAME as CUS_NAME, cus.NKA_NAME as CUS_NKA_NAME ,bc.PR_APP_TYPE ,bc.PAYLINK_CODE ,PL.NAME_CN as PAYLINK_NAME ,bc.USER_FD_CODE ,bc.KC_TYPE ,dug.CODE as DUG_CODE ,hact.CODE as HYPER_ACT_CODE ,hact.NAME as HYPER_ACT_NAME – ,(select MIN(fd.FROM_DATE) from T_TP_FD_BC fdbc left join T_TP_FD fd on fdbc.FD_HEAD_ID=fd.ID where fdbc.BCODE_ID=bc.ID) as BC_FROM_DATE – ,(select MAX(fd.TO_DATE) from T_TP_FD_BC fdbc left join T_TP_FD fd on fdbc.FD_HEAD_ID=fd.ID where fdbc.BCODE_ID=bc.ID) as BC_TO_DATE ,bc.FD_FROM_DATE as BC_FROM_DATE ,bc.FD_TO_DATE as BC_TO_DATE ,bc.FD_COST_TYPE ,bc.FD_BRANDS ,tz.code as TZ_CODE ,tz.isvalid as ISVALID ,bc.MATCH_AMOUNT ,bc.AMOUNT_APPROVE_DATE ,tzBrand.CODE AS TZ_BRAND_CODE ,tzExpenseType.CODE as TZ_EXPENSE_TYPE_CODE ,tzMaterial.CODE as TZ_SKU_CODE ,tz.BEGIN_DATE as TZ_BEGIN_DATE ,TZ.END_DATE AS TZ_END_DATE ,sku.code as SKU_CODE from T_PR_BC as bc left join T_BI_COMPANY as company on bc.COMPANY_ID = company.id left join T_BI_USER as u on bc.CREATOR_ID = u.ID left join SYSUSER as sys on u.SYSUSERID = sys.ID left join T_BI_USER as uu on bc.PROPOSER_ID = uu.ID left join SYSUSER as sysu on uu.SYSUSERID = sysu.ID left join T_BI_EXPENSE_TYPE as expLV3 on bc.COST_TYPE_ID = expLV3.ID left join T_BI_EXPENSE_TYPE as expLV2 on expLV3.P_ID = expLV2.ID left join T_BI_EXPENSE_TYPE as expLV1 on expLV2.P_ID = expLV1.ID left join T_BI_ACTIVITY as act on bc.ACT_ID = act.ID left join T_BI_CPSUBTYPE as cps on bc.CP_SUTYPE_ID = cps.ID left join T_SAP_CUSTOMER_FUCTION as pay on bc.PAYER_ID = pay.ID left join T_SAP_CUSTOMER as payer on pay.CUSTOMER = payer.ID left join T_SAP_VENDOR as vendor on bc.VENDOR_ID = vendor.ID left join T_SAP_CUSTOMER_FUCTION as sold on bc.SOLDTO_ID = sold.ID left join T_SAP_CUSTOMER as soldto on sold.CUSTOMER = soldto.ID left join T_BI_CURRENCY as currency on bc.CUR_ID = currency.ID left join T_BI_MATERIAL as sku on bc.SKU_ID = sku.ID left join T_BI_BRAND as brandtp on bc.BRAND_ID = brandtp.ID left join V_BI_CUSTOMER as cus on (cus.CUS_ID=bc.CUS_ID and cus.BUDGET_YEAR=bc.BUDGET_YEAR) left join T_BI_CUSTOMER_ACCOUNT as acc on cus.CUS_ACCOUNT_ID = acc.ID left join T_SAP_CUSTOMER as PL on bc.PAYLINK_CODE=PL.CODE and PL.COCD=‘7049’ and PL.BLOCK=0 left join T_BI_DUG dug on cus.DUG_ID=dug.ID left join T_BI_HYPER_ACTIVITY hact on bc.HYPER_ACT_ID=hact.ID left join T_PR_BC_TZ tz on tz.bcode_id=bc.ID and tz.isValid=1 LEFT JOIN T_BI_EXPENSE_TYPE tzExpenseType ON tzExpenseType.ID =bc.NEW_COST_TYPE_ID LEFT JOIN T_BI_BRAND tzBrand on tzBrand.ID = bc.NEW_BRAND_ID LEFT JOIN T_BI_MATERIAL tzMaterial on tzMaterial.id = bc.NEW_SKU_ID where REF_TYPE=‘TP’ AND KC_TYPE not in(99,3) UNION ALL –none TP bcode select bc.ID, bc.CODE, bc.REF_CODE as REF_CODE, bc.REF_TYPE as REF_TYPE, company.CODE as COMPANY_CODE, company.NAME_FULL as COMPANY_FULL_NAME, bc.BUDGET_YEAR, bc.BUDGET_MONTH, sys.USERNAME as CREATOR_USER_NAME, sys.realName as CREATOR_REAL_NAME, sysu.USERNAME as PROPOSER_USER_NAME, sysu.realName as PROPOSER_REAL_NAME, bc.CREATE_DATE, expLV3.CODE as EXP_LV3, expLV2.CODE as EXP_LV2, expLV1.CODE as EXP_LV1, null as TP_CODE, null as TP_NAME, case when hact.code is null then cps.CODE else null end as CP_CODE, case when hact.code is null then cps.NAME else null end as CP_NAME, payer.CODE as PAYER_CODE, payer.NAME_CN as PAYER_NAME_CN, vendor.CODE as VENDOR_CODE, vendor.NAME_CN as VENDOR_NAME_CN, soldto.CODE as SOLDTO_CODE, soldto.NAME_CN as SOLDTO_NAME_CN, bc.IS_CLOSE, bc.BLOCK, bc.IS_NEED_FD, bc.REMARK, currency.CODE as CURRENCY_CODE, bc.BC_PLAN, bc.BC_ACT, bc.BC_REV, bc.BC_IN_PP, bc.BC_PP, bc.BC_IN_PC, bc.BC_IN_NPC, bc.BC_IN_REV, null as DIR_CUS_CODE, null as CUS_ACCOUNT, null as BU, line.FROM_DATE as LINE_FROM_DATE, line.TO_DATE as LINE_TO_DATE, null as SHIPMENT_BEGIN_DATE, null as SHIPMENT_END_DATE, brandcp.CODE as BRAND, head.APPROVE_DATE, bc.LOCK_ALLOC, bc.LOCK_AUTO, bc.LOCK_FD, bc.LOCK_KC, bc.LOCK_SA, bc.LOCK_DEBATABLE_TP, bc.LOCK_OFFINV, bc.LOCK_NEG, bc.LOCK_DZ, null AS CUS_CODE, null AS CUS_NAME, null AS CUS_NKA_NAME ,bc.PR_APP_TYPE ,null as PAY_LINK_CODE ,NULL AS PAY_LINK_NAME ,bc.USER_FD_CODE ,bc.KC_TYPE ,null as DUG_CODE ,hact.CODE as HYPER_ACT_CODE ,hact.NAME as HYPER_ACT_NAME ,null ,null ,null ,null ,tz.code as TZ_CODE ,tz.isvalid as ISVALID ,bc.MATCH_AMOUNT ,bc.AMOUNT_APPROVE_DATE ,tzBrand.CODE AS TZ_BRAND_CODE ,tzExpenseType.CODE as TZ_EXPENSE_TYPE_CODE ,tzMaterial.CODE as TZ_SKU_CODE ,tz.BEGIN_DATE as TZ_BEGIN_DATE ,TZ.END_DATE AS TZ_END_DATE ,sku.code as SKU_CODE from T_PR_BC as bc left join T_BI_COMPANY as company on bc.COMPANY_ID = company.id left join T_BI_USER as u on bc.CREATOR_ID = u.ID left join SYSUSER as sys on u.SYSUSERID = sys.ID left join T_BI_USER as uu on bc.PROPOSER_ID = uu.ID left join SYSUSER as sysu on uu.SYSUSERID = sysu.ID left join T_BI_EXPENSE_TYPE as expLV3 on bc.COST_TYPE_ID = expLV3.ID left join T_BI_EXPENSE_TYPE as expLV2 on expLV3.P_ID = expLV2.ID left join T_BI_EXPENSE_TYPE as expLV1 on expLV2.P_ID = expLV1.ID left join T_BI_CPSUBTYPE as cps on bc.CP_SUTYPE_ID = cps.ID left join T_SAP_CUSTOMER_FUCTION as pay on bc.PAYER_ID = pay.ID left join T_SAP_CUSTOMER as payer on pay.CUSTOMER = payer.ID left join T_SAP_VENDOR as vendor on bc.VENDOR_ID = vendor.ID left join T_SAP_CUSTOMER_FUCTION as sold on bc.SOLDTO_ID = sold.ID left join T_SAP_CUSTOMER as soldto on sold.CUSTOMER = soldto.ID left join T_BI_CURRENCY as currency on bc.CUR_ID = currency.ID left join T_AP_APP_LINE as line on bc.REF_ID = line.ID left join T_AP_APP as head on line.APP_ID = head.ID left join T_BI_BRAND as brandcp on head.BRAND_ID = brandcp.ID left join T_BI_HYPER_ACTIVITY hact on bc.HYPER_ACT_ID=hact.ID left join T_PR_BC_TZ tz on tz.bcode_id=bc.ID and tz.isValid=1 LEFT JOIN T_BI_EXPENSE_TYPE tzExpenseType ON tzExpenseType.ID =bc.NEW_COST_TYPE_ID LEFT JOIN T_BI_BRAND tzBrand on tzBrand.ID = bc.NEW_BRAND_ID LEFT JOIN T_BI_MATERIAL tzMaterial on tzMaterial.id = bc.NEW_SKU_ID left join T_BI_MATERIAL as sku on bc.SKU_ID = sku.ID where REF_TYPE not in(‘TP’) )TEMP GO 这是我这个sql server 视图的内容，现在我的需求是 tz_code如果有多个需要 使用 英文逗号 拼成一行例如BCTZ01,BCTZ02,BCTZ03 ,你能直接帮我改好一个完整的视图，我只需要复制粘贴就行
09-11
我们注意到在原始视图中，TZ_CODE 字段是从 `T_PR_BC_TZ` 表通过 `left join` 得到的，由于是一对多关系，会导致主表记录重复。我们需要将每个 `bc.ID` 对应的多个 `tz.code` 拼接成一个字符串（逗号分隔）。 由于...
select d.STATUS, xs.materielcode, d.ID, d.oldid, l.invoicenumber, (l.amount + l.totaltaxamount) TOTALAMOUNT, l.YKFSJE from gk_erp_xslotno xs left join gk_erp_order d on xs.xsorder = d.id left join gk_erp_xslotno x on xs.oldxslotnoid = x.id left join gk_erp_invoice_log l on l.importid = x.invoicecode and l.amount > 0 where xs.isout = '1' and xs.invoicecode is null and l.invoicenumber is not null and nvl(l.status, '1') <> '7' and (d.oldid like '%BHBJ20251029000029%' or l.invoicenumber like '%BHBJ20251029000029%' or xs.materielcode like '%BHBJ20251029000029%' or d.ID like '%BHBJ20251029000029%')ORA-00923: 未找到要求的 FROM 关键字\n
最新发布
11-01
重新审视整个语句，我们发现一个可能的问题：在WHERE子句中，我们引用了表`l`（即`gk_erp_invoice_log`）的字段，但是我们在JOIN时使用了LEFT JOIN，而WHERE子句中对`l.invoicenumber`进行了非空判断（`l.invoice...
SELECT t.pid deviceId, pc.id productOid, t.template_name templateName, t.template_type templateType, pc.linkage linkage, pc.access_way accessWay, pc.name productName, pc.brand_name brandName, pc.brand_name_en brandNameEn, pc.series productSeries, pc.model productModule, pc.hot_line hotLine, pc.is_serial isSerial, c.icon_url iconUrl, pc.serial_list serialInfoVoList, pc.nan_type simpleConnect, b.communication_module_info moduleName, b.connection_function connectionFunction, b.interaction interaction, b.operating_system operateSystem FROM product_template_info t left join ceco_classification c on t.classification_id=c.oid left join product_core pc on pc.pid=t.pid left join partner p on t.template_creator=p.oid left join product_brace b on pc.id=b.core_id where c.type_id = #{classificationId} and t.mark_for_delete = 0 and pc.deleted=0 and t.template_creator=#{templateCreator} and pc.owner_id=#{ownerId} UNION SELECT t.pid deviceId, pc.id productOid, t.template_name templateName, t.template_type templateType, pc.linkage linkage, pc.access_way accessWay, pc.name productName, pc.brand_name brandName, pc.brand_name_en brandNameEn, pc.series productSeries, pc.model productModule, pc.hot_line hotLine, pc.is_serial isSerial, c.icon_url iconUrl, pc.serial_list serialInfoVoList, pc.nan_type simpleConnect, b.communication_module_info moduleName, b.connection_function connectionFunction, b.interaction interaction, b.operating_system operatingSystem FROM product_template_info t LEFT JOIN ceco_classification c ON t.classification_id = c.oid LEFT JOIN product_core pc ON pc.pid = t.pid LEFT JOIN product_brace b ON pc.id = b.core_id WHERE c.type_id = #{classificationId} AND t.template_type = 1 AND t.mark_for_delete = 0 AND pc.deleted = 0 order by templateType ASC Select结果为空时
10-22
这会导致将LEFT JOIN转换为内连接的效果，因为如果LEFT JOIN的表中没有匹配的行，那么这些条件（非NULL条件）会使得行被过滤。例如，如果`ceco_classification`表中没有对应的`type_id`，那么`c.type_id`为NULL，...
mysql left join中on后加条件判断和where中加条件的区别
xinshoumeinianda的博客
02-02 1310
mysql条件查询
mysql left join中where和on条件的区别
weixin_30337157的博客
10-18 166
left join中关于where和on条件的几个知识点： 1.多表left join是会生成一张临时表，并返回给用户 2.where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。 3.on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL 4.on条件中如果...
mysql left join 中文_mysql使用left join需要注意的点
weixin_42425947的博客
02-07 395
含义：LEFT JOIN关键字会从左表(table_name1)那里返回所有的行，即使在右表(table_name2)中没有匹配的行。Mysql对于left join采用类似嵌套循环的方式进行处理，例如：SELECT*FROMLTLEFTJOINRTONP1(LT,RT))WHEREP2(LT,RT)其中p1是on的过滤条件，缺失则认为rue，p2是where过滤条件...
mysql在LEFT JOIN中使用多个条件，发现查询结果个数有问题
木头骑士的旋转木马
09-19 2224
今天在导数据的过程中发现，在LEFT JOIN的过程中，多个匹配条件的结果个数不对。 比如有三张表A, B, C，在A left join B leftjoin C的过程中，总的数量应该与表A个数相同，但是当有多个匹配条件时，发现结果的个数不对，经过排查，原来原因出在where上。 开始写的语句类似于： SELECT * FROM A LEFT JOIN B ON A.field1 = B.field1 LEFT JOIN C ON A.field2 = C.field2 AND B.field3
sql 中left join的使用注意事项
ArcherSYM的博客
10-14 455
https://blog.youkuaiyun.com/fdipzone/article/details/45119551
left join on 后增加条件分析
datangxiajun的博客
10-27 1万+
SQL用过一定时间的同学，对left join，right join应该非常熟悉了，可能有些同学包括我在内， 对left/right join on 后面增加左表或右表条件后，或者多个left/right join 表不同的位置查询结果 感觉到很不解，因此我对以下左连接做了分析演示： 1.两个表的左连接的查询。 2.两个表左连接，在on后面增加左表条件，以及增加右表条件的查询。 3.三张...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值