子查询问题之间的MySQL日期

Question

好的，我有一个正在尝试使用的子查询，需要回答一个复杂的问题。有一个用于生成报告的 SQL 工具。使用此工具，您可以选择日期，只要日期在外部选择语句中即可。因此，您可以使用此工具从一个简单的选择语句中选择一个日期范围，例如:

select date_entered, name from opportunities

这将与该工具一起正常工作。

但是，我有一个 SQL 要求，我需要让每一列成为它自己的 SQL 语句(基本上是一系列子查询)。我的问题是 - 是否有子查询使用初始选择的日期？这是我必须执行的选择语句的示例:

SELECT
(
    SELECT
        count(
            opportunities.id) AS "count_for_opp_id"

    FROM
        opportunities 
LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
WHERE
    opportunities.deleted = '0'
AND ((curdate() - interval 0 day) <= opportunities.date_entered)
AND opportunities_cstm.lead_category_c = 'GM Internet'
AND opportunities_cstm.lead_type_c = 'Sales'
AND opportunities_cstm.partner_id_c LIKE '41%'
OR opportunities_cstm.partner_id_c LIKE '42%'
OR opportunities_cstm.partner_id_c LIKE '43%'
OR opportunities_cstm.partner_id_c LIKE '44%'
OR opportunities_cstm.partner_id_c LIKE '45%'
OR opportunities_cstm.partner_id_c LIKE '47%'
OR opportunities_cstm.partner_id_c LIKE '48%'
OR opportunities_cstm.partner_id_c LIKE '49%'
OR opportunities_cstm.lead_sub_type_c = 'GM 3rd Party'
)AS TOT_DIV_THIRD_PARTY_SALES_LEADS,
(
SELECT
count(opportunities.id) AS "count_for_opp_id"
FROM
opportunities left
    JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
WHERE
    opportunities.deleted = '0'
AND ((curdate() - interval 0 day) <= opportunities.date_entered)
AND opportunities_cstm.lead_category_c = 'Dealer Web'
AND opportunities_cstm.lead_type_c = 'Sales'
AND opportunities_cstm.lead_sub_type_c = 'GM Dealer Website'
OR opportunities_cstm.lead_sub_type_c = 'DMA Landing Page'
)AS TOT_DEALER_WEBSITE_DMA_SALES_LEADS,
(
    SELECT
        count(opportunities.id) AS "count_for_opp_id"
    FROM
        opportunities left
    JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
JOIN leads_handling_objectives
WHERE
opportunities.deleted = '0'
AND ((curdate() - interval 0 day) <= opportunities.date_entered)
AND opportunities_cstm.lead_category_c = 'GM Internet'
AND opportunities_cstm.partner_id_c LIKE '41%'
OR opportunities_cstm.partner_id_c LIKE '42%'
OR opportunities_cstm.partner_id_c LIKE '43%'
OR opportunities_cstm.partner_id_c LIKE '44%'
OR opportunities_cstm.partner_id_c LIKE '45%'
OR opportunities_cstm.partner_id_c LIKE '47%'
OR opportunities_cstm.partner_id_c LIKE '48%'
OR opportunities_cstm.partner_id_c LIKE '49%'
OR opportunities_cstm.lead_sub_type_c = 'GM 3rd Party'
AND opportunities_cstm.time_followup_c <= leads_handling_objectives.resp_time_obj
AND opportunities_cstm.make_c = leads_handling_objectives.makes_carried
AND opportunities_cstm.time_followup_c IS NOT NULL
)AS NUM_OF_LEADS_RESP_ONTIME_DIVISIONAL_THIRD_PARTY,
(
    SELECT
        count(opportunities.id) AS "count_for_opp_id"
    FROM
        opportunities left
    JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
JOIN leads_handling_objectives
WHERE
opportunities.deleted = '0'
AND ((curdate() - interval 0 day) <= opportunities.date_entered)
AND opportunities_cstm.lead_category_c = 'Dealer Web'
AND opportunities_cstm.lead_sub_type_c = 'GM Dealer Website'
OR opportunities_cstm.lead_sub_type_c = 'DMA Landing Page'
AND opportunities_cstm.time_followup_c <= leads_handling_objectives.resp_time_obj
AND opportunities_cstm.make_c = leads_handling_objectives.makes_carried
AND opportunities_cstm.time_followup_c IS NOT NULL
)AS NUM_OF_LEADS_RESP_ONTIME_DEALERWEBSITE_DMA

如果您查看此查询，您会注意到子查询的 curdate...<= date_entered 部分。我真的需要以某种方式在 select 语句的外层上有这个。但是，如果我把它放在外层，我不确定如何让子查询那样使用日期范围。

我知道这是一个复杂的问题，但是 - 是否有任何人可以提供任何建议？谢谢!

(注意:另外，这不能是过程或函数。它必须是选择语句或 View 才能与报告工具一起使用。)

再进行一次编辑 - 上面的 SQL 的一个更简单(更小)的片段可以集中在此处:

SELECT
(SELECT
        count(
            opportunities.id) AS "count_for_opp_id"

    FROM
        opportunities 
LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
WHERE
    opportunities.deleted = '0'
AND ((curdate() - interval 0 day) <= opportunities.date_entered)
AND opportunities_cstm.lead_category_c = 'GM Internet'
AND opportunities_cstm.lead_type_c = 'Sales'
) AS TOT_DIV_THIRD_PARTY_SALES_LEADS

从长远来看，上面的这个可能更容易阅读，也更容易诊断。

谢谢!

Answer 1

您的(较小的)查询目前缺少 FROM。放入一个会给你一个钩子(Hook)来挂起 curdate() 函数......

SELECT (SELECT count(opportunities.id) AS "count_for_opp_id"
            FROM opportunities LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
            WHERE opportunities.deleted              = '0' AND
                  ((curdate() - interval 0 day)     <= opportunities.date_entered) AND 
                  opportunities_cstm.lead_category_c = 'GM Internet' AND
                  opportunities_cstm.lead_type_c     = 'Sales') AS TOT_DIV_THIRD_PARTY_SALES_LEADS
    FROM any_table
    LIMIT 1;

第二个建议...

SELECT <given date>,
       (SELECT count(opportunities.id) AS "count_for_opp_id"
            FROM opportunities LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c
            WHERE opportunities.deleted              = '0' AND
                  (<given date>                     <= opportunities.date_entered AND 
                  opportunities_cstm.lead_category_c = 'GM Internet' AND
                  opportunities_cstm.lead_type_c     = 'Sales') AS TOT_DIV_THIRD_PARTY_SALES_LEADS
    FROM any_table
    LIMIT 1;

给定的日期可以是文字或变量，也可以存在于 any_table 中；它会很好地进入子查询。