sql - MySQL 查询分组子查询问题

Question

出于某种原因，我无法理解这个查询，我想知道是否有人能够帮助我解决这个问题。我有以下 3 个表:

opp_cstm:

id_c  make_c      time_followup_c  lead_category_c  lead_type_c  9     GMC          224             GM               Internet Sales  e     Buick     809             GM Internet      Service8     GMC          1559            Dealer Web       Sales2     Cadillac     10596           Dealer Web       Service3     Chevrolet    15595           GM Internet      Sales4     Chevrolet    905           GM Internet      Service

机会:

id  date_entered            deleted2   2010-07-16 16:46:21      03   2010-07-16 16:55:53      04   2010-07-16 19:30:12      08   2010-07-16 16:44:13      09   2010-07-16 16:39:17      0e   2010-07-16 16:41:44      0

leads_objectives:

makes_carried   resp_time_objGMC           18000Ford             7200Cobalt         43200Chevrolet       18000Buick           18000Cadillac         7200

我需要获得以下布局(这显然会按日期分组，LCat):

Date    LCat          LType     #ofLds   AvgResp       #LdsRespOT   %LdsRespOT  #Lds!RespOT %Lds!RespOT19-Jul  GM Internet   Sales     10       18 minutes     7          70%          3          30%19-Jul  GM Internet   Service   20       20 minutes     10        50%          10         50%19-Jul  Handraiser    Sales     10       45 minutes     5          50%          5          50%20-Jul  Dealer Web    Sales     20       120 minutes    5          25%          15         75%20-Jul  Dealer Web    Service   10       7 minutes    3           30%          7          70%

我需要的每一列的解释:

日期: opportunities.date_entered = 今天(这当然需要在所有事情上)

LCat: opp_cstm.lead_category

LType: opp_cstm.lead_type

#ofLds:这需要是 deleted = "0"且 Lead Category 不为空的机会计数

AvgResp: 平均。机会中的 timefollowup-C 字段，其中 Deleted =“0”且潜在客户类别不为空且 time_followup_c > 0 且不为空

#LdsRespOT: Deleted =“0”且潜在客户类别不为空且 time_followup_c 小于或等于 resp_time_obj 且 make_c = makes_carried 且 time_followup_c > 0 且不为空的机会计数

%LdsRespOT:(#LdsRespOT/#ofLds)

#Lds!RespOT:(#ofLds - #LdsRespOT)

%Lds!RespOT: (#Lds!RespOT/#ofLds)

我很难理解这个问题。我想知道这里是否有人可以为此提供某种帮助？我将如何正确编写此查询？

我试了好几次，但每次都失败了，我很沮丧!我知道我只是缺少某种我缺少的某种分组或某种 sql 子查询。

任何帮助将不胜感激!

谢谢!

Answer 1

对于遇到此问题并可能需要此类帮助的任何人，这就是我最终所做的事情:

SELECT 
opportunities.date_entered as Date,
opportunities_cstm.lead_category_c as LCat,
opportunities_cstm.lead_type_c as LType,
count(opportunities.id) as '# of Lds',
SUM(opportunities_cstm.time_followup_c)/count(opportunities.id) as AvgResp,
SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    ) as '#LdsRespOT',
(SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    ) /count(opportunities.id))*100 as '%LdsRespOT',
count(opportunities.id) - SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    )as '#Lds!RespOT',
((count(opportunities.id) - SUM(
    CASE
     WHEN ( 
        opportunities_cstm.time_followup_c IS NOT NULL
        AND
        opportunities_cstm.time_followup_c < leads_handling_objectives.resp_time_obj
        )
    THEN
        1 
    ELSE
        0
    END
    ))/count(opportunities.id))*100 as '%Lds!RespOT'
FROM
opportunities
INNER JOIN 
opportunities_cstm 
ON 
    opportunities_cstm.id_c = opportunities.id
AND
    opportunities_cstm.lead_category_c IS NOT NULL
AND
    opportunities_cstm.lead_category_c NOT LIKE ''
INNER JOIN
 leads_handling_objectives
ON
    leads_handling_objectives.makes_carried = opportunities_cstm.make_c
WHERE
opportunities.date_entered = DATE(NOW())
AND 
opportunities.deleted='0'
GROUP BY 
opportunities_cstm.lead_category_c