php - Mysql子查询带百分比分割(难查询)

Question

好的，我有两个相关的表，一个包含名为“opportunities”的主要字段，另一个包含名为“opportunities_cstm”的附加字段。出于我们的目的，机会表包含以下字段:id 和 sales_stage。 opportunity_cstm 表包含字段 id_c 和 sales_stage_before_angled_c。 id_c 是关联两个表的内容。

sales_stage 包含从 1 到 10 的值以及“Closed Lost”或“Closed Won”。在实际应用中，1到10代表从0-9%到90-99%的百分比范围，平仓亏损为0%，平仓盈利为100%。

sales_stage_before_angled_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。

Answer 1

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`

除非我完全没有捕获要点。