没有使用ID的Where子句时没有结果集

Question

我想知道为什么我的代码在取出Account_ID时不返回任何数据。我希望它运行并找到符合连接条件和我想返回的字段的每个Account_ID。这是在MySQL中，我看了看周围和谷歌搜索，但没有发现这样的事情或只是没有把它正确。所以，下面是代码，如果有人可以帮助我，我将不胜感激。代码应该返回1行每Account_ID 我不习惯使用MySQL。版本5.6.34使用Toad Data-Point运行sql脚本。 感谢丹

SELECT DISTINCT 
    pd.ACCOUNT_ID     
    , do.MEMBER_NAME 
    , do.GENDER 
    , MaxDate.EarlyDATE AS ACTIVITY_DATE 
    , MaxDate.LateDATE AS LAST_ACTIVITY_DATE 
    , pd.NUMBER_WKS   
    ... more fields after this 
    Joins below 
    FROM ZZ_Program_Details pd 
     LEFT JOIN ZZ_Demographics do 
     ON do.ACCOUNT_ID = pd.ACCOUNT_ID 
    INNER JOIN 
     (SELECT DISTINCT cl.ACCOUNT_ID 
     , min(cl.activity_date) AS EarlyDATE 
     , cl1.activity_date AS LateDATE 
     , cl1.scheduling_selection 
     , cl.ATND_STATUS_THIS_INTERVAL   
     , cl.WEIGHT_STATUS_FOR_INTERVAL   
     , TRUNCATE(cl.WEIGHT_WHEN_ENROLLED,2) AS WEIGHT_WHEN_ENROLLED 
     , TRUNCATE(cl.WEIGHT_AT_INTERVAL_END,2) AS WEIGHT_AT_INTERVAL_END 
     FROM ZZ_Class_Attendance cl 
     INNER JOIN ZZ_Class_Attendance cl1 
      ON cl1.account_id = cl.ACCOUNT_ID 
     WHERE cl1.ACTIVITY_DATE > cl.ACTIVITY_DATE 
     /*and cl.account_id = '47F74C65BA8BB02DE053BC010B0AF714'*/ 
     ) AS MaxDate 
    INNER JOIN 
    (SELECT ESRA.healthfleet_member_id 
     , ESRA.Sponsor 
     , ESRA.uhc_region 
     , ESRA.uhc_major_market 
     , ESRA.uhc_minor_market AS ESRA_UHC_MINOR_MARKET 
     , ESRA.policy_number AS ESRA_POLICY_NBR 
     , ESRA.relationship_code 
     , sf.Market_Segment 
     , sf.UHC_Minor_Market AS SF_UHC_MINOR_MARKET 
     , sf.CLIENT_NAME 
     , sf.Client_Owner 
     , sf.Payer_Effective_Date 
    FROM reporting_adhoc.ZZ_ESRA_People ESRA 
    LEFT JOIN reporting_adhoc.ZZ_Salesforce sf 
     ON sf.Policy_Number = ESRA.policy_number 
    /*WHERE ESRA.healthfleet_member_id='47F74C65BA8BB02DE053BC010B0AF714'*/ 
    GROUP BY ESRA.healthfleet_member_id 
    , ESRA.Sponsor, ESRA.uhc_region 
    , ESRA.uhc_major_market 
    , ESRA.uhc_minor_market 
    , ESRA.relationship_code 
    , sf.Market_Segment 
    , sf.UHC_Minor_Market 
    , sf.CLIENT_NAME 
    , sf.Client_Owner, ESRA.policy_number 
    , sf.Payer_Effective_Date 
    ) ESRA_DATA 

    ON MaxDate.ACCOUNT_ID = pd.ACCOUNT_ID 
    AND ESRA_DATA.healthfleet_member_id = pd.ACCOUNT_ID 

    /*WHERE pd.account_id = '47F74C65BA8BB02DE053BC010B0AF714'* --with this 
    it works fine */ 
    WHERE pd.account_id > 0 --an attempt with getting all account_ids 

    GROUP BY 
    pd.ACCOUNT_ID     
    , do.MEMBER_NAME 
    , do.GENDER 
    , MaxDate.EarlyDATE 
    , MaxDate.LateDATE 
    ... rest of fields 
    ORDER BY pd.ACCOUNT_ID 

Answer 1

如果无法访问您的表格和数据，我们可能无法提出一个明确的答案，并为您的查询缺少的部分，它使得它更是一个猜谜游戏的。

让我们从这里开始：

SELECT DISTINCT ---------- bad bad 
     pd.ACCOUNT_ID 
    , MaxDate.EarlyDATE AS ACTIVITY_DATE 
    , MaxDate.LateDATE AS LAST_ACTIVITY_DATE 

FROM ZZ_Program_Details pd 
LEFT JOIN ZZ_Demographics do ON do.ACCOUNT_ID = pd.ACCOUNT_ID 
INNER JOIN (
     SELECT DISTINCT ---------- bad bad 
      cl.ACCOUNT_ID 
      , MIN(cl.activity_date) AS EarlyDATE 
      , cl1.activity_date  AS LateDATE 
     FROM ZZ_Class_Attendance cl 
     INNER JOIN ZZ_Class_Attendance cl1 ON cl1.account_id = cl.ACCOUNT_ID 
     WHERE cl1.ACTIVITY_DATE > cl.ACTIVITY_DATE 

     -- no GROUP BY, so how do your get MIN() or MAX() ? 

    ) AS MaxDate ON 
        (WHAT????) 

开始打破你的大查询成小块，就像你看到上面。 这个部分查询是否工作？它可能不是因为您尝试使用DISTINCT而不是GROUP BY，并且连接条件也未知。

另请注意，您已使用INNER JOIN。如果子查询MaxDate未能返回任何匹配的行，那么整个查询将不会返回行。重新考虑每个INNER JOIN（也许尝试将它们作为左连接）。

一旦你得到了部分查询的工作，添加下一个联接并将相关列添加到顶部选择子句。在查询的这部分工作，直到它成功，移动到下一个并重复，直到你有一个工作查询。

请尽量避免在涉及多个连接的复杂查询中使用“select distinct”。这不是很好的做法。

---

您可能会发现这些查询可以帮助您确定哪些表（或子查询）需要左加入或不：

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c 
FROM ZZ_Program_Details pd 
; 

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c 
FROM ZZ_Program_Details pd 
INNER JOIN ZZ_Demographics do ON pd.ACCOUNT_ID = do.ACCOUNT_ID 
; 

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c 
FROM ZZ_Program_Details pd 
LEFT JOIN ZZ_Demographics do ON pd.ACCOUNT_ID = do.ACCOUNT_ID 
; 

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c 
FROM ZZ_Program_Details pd 
INNER JOIN ZZ_Class_Attendance cl ON pd.ACCOUNT_ID = cl.ACCOUNT_ID 
INNER JOIN ZZ_Class_Attendance cl1 ON cl1.account_id = cl.ACCOUNT_ID 
; 

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c 
FROM ZZ_Program_Details pd 
LEFT JOIN ZZ_Class_Attendance cl ON pd.ACCOUNT_ID = cl.ACCOUNT_ID 
LEFT JOIN ZZ_Class_Attendance cl1 ON cl1.account_id = cl.ACCOUNT_ID 
; 

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c 
FROM ZZ_Program_Details pd 
INNER JOIN reporting_adhoc.ZZ_ESRA_People ESRA ON pd.ACCOUNT_ID = ESRA.healthfleet_member_id 
INNER JOIN reporting_adhoc.ZZ_Salesforce sf ON sf.Policy_Number = ESRA.policy_number 
; 

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c 
FROM ZZ_Program_Details pd 
LEFT JOIN reporting_adhoc.ZZ_ESRA_People ESRA ON pd.ACCOUNT_ID = ESRA.healthfleet_member_id 
LEFT JOIN reporting_adhoc.ZZ_Salesforce sf ON sf.Policy_Number = ESRA.policy_number 
;