如何最好地使用SQL查询中的代码列表作为另一个查询的输入

Question

我有一对SQL我试图一起使用来生成一个报告显示AVG金额为2个字段的给定代码，我想从其他查询中提取。所以这个查询返回代码列表

SELECT DISTINCT
cc.code AS `CC Code`
FROM ds
LEFT OUTER JOIN dd
ON ds.dd_id = dd.id
LEFT OUTER JOIN ii
ON ds.ii_id = ii.id
LEFT OUTER JOIN dcc
ON ds.id = dcc.dos_id
LEFT OUTER JOIN cc
ON dcc.cpt_id = cc.id
LEFT OUTER JOIN bi
ON ds.bi_id = bi.id
LEFT OUTER JOIN cs
ON bi.claim_status = cs.id
WHERE ii.company = "Alpha"
AND cs.status = "On HOLD"
AND dcc.amount_allowed > 0

返回一个包含大约25个唯一代码的列表。然后我手动运行这个，插入其中的每一个，以获得使用该代码的2个字段的平均金额。

SELECT
cc.code AS `CC Code`,
CAST(AVG(dcc.amount_allowed) AS decimal(10, 2)) `AVG Allowed`,
CAST(AVG(dcc.paid_amount) AS decimal(10, 2)) `Paid Avg`,
ii.company AS `I Company`
FROM ds
LEFT OUTER JOIN dd
ON ds.dd_id = dd.id
LEFT OUTER JOIN ii
ON ds.ii_id = ii.id
LEFT OUTER JOIN dcc
ON ds.id = dcc.dos_id
LEFT OUTER JOIN cc
ON dcc.cpt_id = cc.id
LEFT OUTER JOIN bi
ON ds.bi_id = bi.id
WHERE ii.company = "Alpha"
AND code = '35647'
AND dcc.amount_allowed > 0

返回类似

的结果

tbody> <<tr>

CC Code	AVG Allowed	已付AVG	保险公司
35647	9373.44	8652.30	α

让我们先把注意力集中在你想要得到的东西上。忘记查询中不使用的所有其他表;要么在SELECT，要么在WHERE。例如:

SELECT
cc.code AS `CC Code`,
CAST(AVG(dcc.amount_allowed) AS decimal(10, 2)) `AVG Allowed`,
CAST(AVG(dcc.paid_amount) AS decimal(10, 2)) `Paid Avg`,
ii.company AS `I Company`
FROM ds
/* comment out this part
LEFT OUTER JOIN dd
ON ds.dd_id = dd.id */
LEFT OUTER JOIN ii
ON ds.ii_id = ii.id
LEFT OUTER JOIN dcc
ON ds.id = dcc.dos_id
LEFT OUTER JOIN cc
ON dcc.cpt_id = cc.id
/* comment out this part
LEFT OUTER JOIN bi
ON ds.bi_id = bi.id*/
WHERE ii.company = "Alpha"
AND code = '35647'
AND dcc.amount_allowed > 0

然后根据您的条件"我想只是运行它，让它传递每个代码并返回每个代码的结果">，修改查询如下:

SELECT
cc.code AS `CC Code`,
CAST(AVG(dcc.amount_allowed) AS decimal(10, 2)) `AVG Allowed`,
CAST(AVG(dcc.paid_amount) AS decimal(10, 2)) `Paid Avg`,
ii.company AS `I Company`
FROM ds
LEFT OUTER JOIN ii
ON ds.ii_id = ii.id
LEFT OUTER JOIN dcc
ON ds.id = dcc.dos_id
LEFT OUTER JOIN cc
ON dcc.cpt_id = cc.id
WHERE dcc.amount_allowed > 0
GROUP BY cc.code, ii.company;
*commented out part omitted here for better view.
*removed > WHERE ii.company = "Alpha" AND code = '35647'.

由于您有一个聚合(AVG())，SELECT中的任何非聚合列都应该在GROUP BY中。

对于ii.company，有几个选项:

1. 如果你不想要它，从SELECT和GROUP BY中删除它。
2. 如果你真的不关心ii.company返回什么，你可以把它改为ANY_VALUE(ii.company)，并保留在GROUP BY。
3. 如果你想显示ii.company中的所有内容附加到单个cc.code，你可以更改为GROUP_CONCAT(DISTINCT ii.company)并从GROUP BY中删除它。

看看这是否适合你。