文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >理解3个或多个表的联接顺序

问理解3个或多个表的联接顺序
EN

Stack Overflow用户
提问于 2014-09-26 06:13:00
回答 3查看 71关注 0票数 0

我有一个使用*=操作符的旧查询。现在，查询中的where子句如下所示

代码语言：javascript
复制
Table1.Column1 *= Table2.Column1
AND Table1.Column2 *= Table3.Column1
if (some conditions in C# script)  //this whole clause is generated by C# function based on different conditions
AND Table1.Column3 *= Table4.Column1

我不得不重写它来使用左联接，因为我们不再是恐龙了，我们将转向SQL server 2014 (从sql server 2000)。总之，我重写了查询如下

代码语言：javascript
复制
From Table1
Left Join Table2 On Table1.Column1 = Table2.Column1
Left Join Table3 On Table1.Column2 = Table3.Column1
Left Join Table4 On Table1.Column3 = Table4.Column1

我认为这应该为我提供同样的结果，但事实并非如此。显然，在这两种情况下，Server都没有遵循相同的连接顺序。因此，我必须确定旧的查询所遵循的确切顺序，以及如何重新创建相同的顺序。

我对密码不太了解。但是，我可以在这里发布完整的功能，如果它能帮助人们更好地理解情况的话。

编辑：

确切的查询生成器函数，我正在使用。

代码语言：javascript
复制
public virtual FANUC.Common.BaseClasses.Row[] GetCustomersForPopup( FANUC.Common.BaseClasses.Row objListCustomerFilter, FANUC.Common.BaseClasses.PagingEventArgs e )    {

        string strConnector = " WHERE ";
        string strANDClause = "";

        string strSQLQuery  = " SELECT "
                            + " TBL_Company_Master.CMPM_Company_ID,"
                            + " TBL_Company_Master.CMPM_Company_Name,"
                            + " " + ( ( FANUCUser )Thread.CurrentPrincipal.Identity ).DBUser + ".fnGetRefCodeValue( CMPM_Company_Type_ID ) AS CMPM_CompanyTypeID,"
                            + " TBL_Company_Master.CMPM_Company_NickName,"
                            + " TBL_Company_Master.CMPM_Service_Center_ID,"
                            + " TBL_Company_Master.CMPM_Company_BranchName,"
                            + " TBL_Company_Master.CMPM_Black_Listed_Flag,"
                            + " TBL_Company_Master.CMPM_Prohibited_Company_Flag,"   
                            + " " + ( ( FANUCUser )Thread.CurrentPrincipal.Identity ).DBUser + ".fnGetRefCodeValue( TBL_Company_Master.CMPM_Status ) AS CMPM_Status,"
                            + " TBL_Company_Master.CMPM_City_Location_ID AS CMPM_City_Location_ID,"
                            + " TBL_City_Location_Master.CLIM_City_Name AS CLIM_City_Name, "
                            + " TBL_Company_Master.CMPM_Country_ID AS CMPM_Country_ID,"
                            + " TBL_Country_Master.CRIM_CountryName, "
                            + " TBL_Company_Master.CMPM_Night_Call_Applicable_flag,"
                            + " TBL_Company_Master.CMPM_Default_currency_for_transaction,"
                            + " TBL_Company_Master.CMPM_Telephone_No, "
                            + " TBL_Customer_Contact_Master.CNTM_ContactPersonName, "
                            + " TBL_Customer_Contact_Master.CNTM_Section_Name, "
                            + " TBL_Company_Master.Use_Count, "
                            + " TBL_Company_Master.CMPM_Self_Company_Indicator, "
                            + " TBL_Company_Master.CMPM_Transport_Time ";


        string strFromClause    = " FROM TBL_Company_Master, "
                                + " TBL_Service_Center_Master, "
                                + " TBL_City_Location_Master, "
                                + " TBL_Country_Master, "
                                + " TBL_Customer_Contact_Master";

        strANDClause    += " AND TBL_Company_Master.CMPM_Service_Center_ID *= TBL_Service_Center_Master.SCRM_Service_Center_ID  "
                        +  " AND TBL_Company_Master.CMPM_City_Location_ID *= TBL_City_Location_Master.CLIM_City_ID "
                        +  " AND TBL_Company_Master.CMPM_Country_ID *= TBL_Country_Master.CRIM_CountryID ";

            if ( objListCustomerFilter[ Constants.IS_CALLING_CUSTOMER ] != null || objListCustomerFilter[ Constants.IS_PAYEE_CUSTOMER ] != null || Convert.ToInt32( objListCustomerFilter[ "CUTM_Customer_Type_ID" ] ) == 120 ) 
                strANDClause += " AND TBL_Company_Master.CMPM_Company_ID *= TBL_Customer_Contact_Master.CNTM_Customer_ID ";
            else
                strANDClause += " AND TBL_Company_Master.CMPM_Company_ID = TBL_Customer_Contact_Master.CNTM_Customer_ID " ;

            strANDClause    += " AND TBL_Customer_Contact_Master.CNTM_Default_Flag = 'Y' "; 
            strANDClause    += " AND CMPM_Active_Flag != 'N'";


        if ( objListCustomerFilter["CUTM_Customer_Type_ID"] != null && Convert.ToString(objListCustomerFilter["CUTM_Customer_Type_ID"]) != "" ) {
            strFromClause   += " ,TBL_Customer_Type_Mapping ";
            strANDClause    += " AND CUTM_Customer_ID = CMPM_Company_ID " + " AND CUTM_Customer_Type_ID = "+Convert.ToString(objListCustomerFilter["CUTM_Customer_Type_ID"]);
        }

        if ( objListCustomerFilter["CMPM_Company_Type_ID"] != null && Convert.ToString(objListCustomerFilter["CMPM_Company_Type_ID"]) != "" && Convert.ToString(objListCustomerFilter["CMPM_Company_Type_ID"]) != Constants.ALL )   {
            strANDClause += " AND CMPM_Company_Type_ID IN ("+Convert.ToString(objListCustomerFilter["CMPM_Company_Type_ID"])+","+Constants.COMPANY_TYPE_BOTH+") ";
        }

        if ( !Convert.ToString( objListCustomerFilter[ Constants.PAYMENT_REQD ] ).Equals(Constants.CONST_NO ) ) {

            strSQLQuery += ", TBL_Company_Payment_Terms.CMPT_Payment_Term_Description "
                        + ", TBL_Company_Payment_Terms.CMPT_Payment_Term_ID ";

            strFromClause += " ,TBL_Company_Payment_Terms ";

            if((objListCustomerFilter[Constants.IS_CALLING_CUSTOMER] != null) ||(objListCustomerFilter[Constants.IS_END_USER] != null) ) 
                strANDClause    += " AND TBL_Company_Master.CMPM_Company_ID *= TBL_Company_Payment_Terms.CMPT_Company_ID "
                                + " AND TBL_Company_Payment_Terms.CMPT_Default = 'Y' ";
            else
                strANDClause    += " AND TBL_Company_Master.CMPM_Company_ID = TBL_Company_Payment_Terms.CMPT_Company_ID "
                                + " AND TBL_Company_Payment_Terms.CMPT_Default = 'Y' ";


            if ( objListCustomerFilter[ "CMPM_Company_Type_ID" ] != null && Convert.ToString( objListCustomerFilter[ "CMPM_Company_Type_ID" ] ) != Constants.COMPANY_TYPE_BOTH && Convert.ToString( objListCustomerFilter[ "CMPM_Company_Type_ID" ] ) != Constants.ALL )
                strANDClause += " AND CMPT_Company_Type_ID = " + Convert.ToString( objListCustomerFilter[ "CMPM_Company_Type_ID" ] );   

        }

        strANDClause += " AND CMPM_Subsidiary_Code = '"+((FANUCUser)Thread.CurrentPrincipal.Identity).SubsidiaryCode+"'";

        Row objFilter = new Row();

        objFilter["CMPM_Company_ID"]                            = objListCustomerFilter["CMPM_Company_ID"];
        objFilter["CMPM_Black_Listed_Flag"]                     = objListCustomerFilter["CMPM_Black_Listed_Flag"];
        objFilter["CMPM_Prohibited_Company_Flag"]               = objListCustomerFilter["CMPM_Prohibited_Company_Flag"];
        objFilter["CMPM_Status"]                                = objListCustomerFilter["CMPM_Status"];
        objFilter["CMPM_Company_Name~like"]                     = objListCustomerFilter["CMPM_Company_Name"];
        objFilter["CMPM_Company_NickName~like"]                 = objListCustomerFilter["CMPM_Company_NickName"];
        objFilter["CMPM_Telephone_No~like"]                     = objListCustomerFilter["CMPM_Telephone_No"];
        objFilter["CMPM_FAX_No"]                                = objListCustomerFilter["CMPM_FAX_No"];
        objFilter["CMPM_Service_Center_ID"]                     = objListCustomerFilter["CMPM_Service_Center_ID"];
        objFilter["CMPM_Billing_Company_ID"]                    = objListCustomerFilter["CMPM_Billing_Company_ID"];
        objFilter["CMPM_Shipping_Company_ID"]                   = objListCustomerFilter["CMPM_Shipping_Company_ID"];
        objFilter["CMPM_City_Location_ID"]                      = objListCustomerFilter["CMPM_City_Location_ID"];
        objFilter["CMPM_State_ID"]                              = objListCustomerFilter["CMPM_State_ID"];
        objFilter["CMPM_Country_ID"]                            = objListCustomerFilter["CMPM_Country_ID"];
        objFilter["CMPM_Grp_Parent_Company_ID"]                 = objListCustomerFilter["CMPM_Grp_Parent_Company_ID"];
        objFilter["CMPM_Night_Call_Applicable_Flag"]            = objListCustomerFilter["CMPM_Night_Call_Applicable_Flag"];
        objFilter["CMPM_Default_currency_for_transaction"]      = objListCustomerFilter["CMPM_Default_currency_for_transaction"];
        objFilter["CMPM_Company_local_registration_No~like"]    = objListCustomerFilter["CMPM_Company_local_registration_No"];
        objFilter["CMPM_Company_central_registration_No~like"]  = objListCustomerFilter["CMPM_Company_central_registration_No"];
        objFilter["CMPM_Insurance_Policy_No~like"]              = objListCustomerFilter["CMPM_Insurance_Policy_No"];
        objFilter["CMPM_Active_Flag"]                           = objListCustomerFilter["CMPM_Active_Flag"];
        objFilter["CMPM_Company_BranchName~like"]               = objListCustomerFilter["CMPM_Company_BranchName"];
        objFilter["CMPM_Company_BranchName_LocalLanguage~like"] = objListCustomerFilter["CMPM_Company_BranchName_LocalLanguage"];
        objFilter["CMPM_Postal_Code"]                           = objListCustomerFilter["CMPM_Postal_Code"];
        objFilter["CMPM_Web_Site~like"]                         = objListCustomerFilter["CMPM_Web_Site"];
        objFilter["CMPM_Distance"]                              = objListCustomerFilter["CMPM_Distance"];

        if ( objListCustomerFilter["CMPM_Self_Company_Indicator"] != null && Convert.ToString(objListCustomerFilter["CMPM_Self_Company_Indicator"]) != Constants.ALL )
            objFilter[ "CMPM_Self_Company_Indicator" ]  = objListCustomerFilter["CMPM_Self_Company_Indicator"];



        CommonBQ    objCommonBQ     = new CommonBQ();
        string      strSearchClause = objCommonBQ.CreateFilter( objFilter );
        string      strFinalString  = "";

        if ( !strSearchClause.Equals( "" ) )    strFinalString  = strSQLQuery + strFromClause + strConnector + strSearchClause + strANDClause;
        else    {

            strSQLQuery += strFromClause + strConnector + strANDClause;

            int     iFirstPos       = strSQLQuery.IndexOf( "AND", 0 );
            string  strFirstPart    = strSQLQuery.Substring( 0, iFirstPos );
            string  strSecondPart   = strSQLQuery.Substring( iFirstPos + 3, strSQLQuery.Length - iFirstPos - 3 );

            strFinalString  = strFirstPart + strSecondPart;
        }

        return  GetRows( strFinalString, CreateParameterArray( objListCustomerFilter ), CommandType.Text, null, e );
    }
sql
sql-server
sql-server-2014
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2014-09-26 07:14:35

在这个查询中，您应该更新一些内容：

  1. 在select子句中使用表别名而不是完整的表名。
  2. TBL_Customer_Contact_Master是根据以下条件加入的： objListCustomerFilter Constants.IS_CALLING_CUSTOMER != null \ objListCustomerFilter Constants.IS_PAYEE_CUSTOMER !=空\ Convert.ToInt32( objListCustomerFilter "CUTM_Customer_Type_ID“) == 120 )，如果这是正确的，那么就有一个Left Join else Inner join。
  3. 因此，请将声明更新为： 字符串strFromClause =“FROM TBL_Company_Master TCM”+ "Left Join TBL_Service_Center_Master TSC on TCM.CMPM_Service_Center_ID = TSC.SCRM_Service_Center_ID“+ "Left TBL_City_Location_Master TCL on TCM.CMPM_City_Location_ID = TCL.CLIM_City_ID”+“左侧Join TBL_Country_Master TC on TCM.CMPM_Country_ID = TC.CRIM_CountryID "；
  4. 更新条件1如下： if ( objListCustomerFilter Constants.IS_CALLING_CUSTOMER != null欧元/ objListCustomerFilter Constants.IS_PAYEE_CUSTOMER != null欧元/ Convert.ToInt32( objListCustomerFilter "CUTM_Customer_Type_ID“) == 120 ) strFromClause +=“左加入TCM.CMPM_Company_ID = TCCM.CNTM_Customer_ID的TBL_Customer_Contact_Master TCCM”，strFromClause +=“TCM.CMPM_Company_ID = TCCM.CNTM_Customer_ID上的内连接TBL_Customer_Contact_Master TCCM "；
  5. 然后将条件2更新为: if ( objListCustomerFilter"CUTM_Customer_Type_ID“!= null & Convert.ToString CUTM_Customer_Type_ID != "”){ "+Convert.ToString(objListCustomerFilter"CUTM_Customer_Type_ID“strFromClause +=”左加入CUTM_Customer_ID = CMPM_Company_ID和CUTM_Customer_Type_ID =CMPM_Company_ID；}
  6. 条件3为： if((objListCustomerFilterConstants.IS_CALLING_CUSTOMER != null(objListCustomerFilter Constants.IS_END_USER != null) strFromClause +=“TCM.CMPM_Company_ID = TCPT.CMPT_Company_ID和TCPT.CMPT_Default = 'Y‘上的左联接TBL_Company_Payment_Terms TCPT；strFromClause +=”TCM.CMPM_Company_ID = TCPT.CMPT_Company_ID和TCPT.CMPT_Default = 'Y’上的内连接TBL_Company_Payment_Terms TCPT；

我可能有些地方漏掉了几个逗号和分号，但它应该让你知道事情可能会在哪里丢失。希望这会有帮助！

票数 1
EN

Stack Overflow用户

发布于 2014-09-26 06:38:31

这个顺序在左边的外部连接中不重要，所以请放心，它不是造成的。我的猜测是如果状态可能导致结果的差异。如果您可以共享表数据或输出，则可以计算出它。

票数 0
EN

Stack Overflow用户

发布于 2014-09-26 07:11:00

不同之处在于如何对外部联接表中的列应用附加where子句筛选器。

在这方面：

代码语言：javascript
复制
select *
from a
    left outer join b on a.id = b.id
where
    b.other_col = 'test'

结果将只包含在b中找到行而b中的other_col列具有值test的行。

与此相比：

代码语言：javascript
复制
select *
from a, b
where
    a.id *= b.id
    and b.other_col = 'test'

这将在a中找到所有行。它将包含来自b的列，用于b.other_col = 'test'所在的行。

因此，使用第二个查询并将其转换为具有左联接的查询，下面的一个查询将提供相同的输出：

代码语言：javascript
复制
-- 1.

select *
from a
    left outer join b on a.id = b.id and b.other_col = 'test'

-- 2.

select *
from a
    left outer join 
    (
        select *
        from b
        where other_col = 'test'
    ) as b on a.id = b.id
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/26053157

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档