php - 具有百分比明细的MySQL子查询(困难查询)

好的，我有两个相关的表，一个包含称为“ opportunities”的主字段，另一个包含名为“ opportunities_cstm”的附加字段。就我们而言，机会表包含以下字段：id和sales_stage。 careers_cstm表包含字段id_c和sales_stage_before_closed_c。 id_c是将两个表关联起来的内容。

sales_stage包含从1到10的值，还包含“ Closed Lost”（关闭丢失）或“ Closed Won”（关闭赢得）。在实际应用中，1到10代表从0-9％到90-99％的百分比范围，平仓亏损为0％，平仓赢率为100％。

sales_stage_before_closed_c是关闭前的百分比范围。

因此，在我的实际查询中，我需要为每个sales_stage显示一个百分比，其中有多少机会达到了这一阶段并带来了获胜的机会，有多少机会达到了这一阶段并导致了损失。

更新到更接近我需要的新查询：

SELECT opportunities_c_top.sales_stage_before_closed_c AS 'Sales Stage',
COUNT(*) * 100.0 /
( SELECT COUNT(*)
FROM `opportunities_cstm` opportunities_cstm join
`opportunities` opportunities
on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` =   'Closed Won' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c ) AS 'Closed Won',

COUNT(*) * 100.0 /
( SELECT COUNT(*)
FROM `opportunities_cstm` opportunities_cstm join
`opportunities` opportunities
on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` =   'Closed Lost' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c ) AS 'Closed Lost'

FROM `opportunities_cstm` opportunities_c_top join
`opportunities` opportunities_top
on opportunities_top.id = opportunities_c_top.id_c
WHERE (opportunities_top.`sales_stage` = 'Closed Won' OR opportunities_top.`sales_stage` = 'Closed Lost')
GROUP BY opportunities_c_top.sales_stage_before_closed_c

http://sqlfiddle.com/#!2/ac28d/1

它仍然不是100％正确，但是，如果您运行查询，它将显示60％-69％均为200，而不是每侧50。

最佳答案

SQL并非真正用于演示。我会考虑只提取原始数据，然后在PHP中进行操作。

SELECT
    opportunities.`sales_stage`,
    opportunities_cstm.`percent_before_closed_c`,
    count(*)
FROM `opportunities` opportunities join
    `opportunities_cstm` opportunities_cstm
    on opportunities.id = opportunities_cstm.id_c
WHERE opportunities.`sales_stage` in ('Closed Lost', 'Closed Won')
GROUP BY opportunities.`sales_stage`, opportunities_cstm.`percent_before_closed_c`

除非我完全忽略了这一点。

关于php - 具有百分比明细的MySQL子查询(困难查询)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21380019/