代码之家 › 专栏 › 技术社区 › den bardadym hakatashi

SQL problem with 12 subquery

task sql

den bardadym hakatashi · 技术社区 · 15 年前

存在这样的DB模式: alt text http://img156.imageshack.us/img156/9017/2706.png

I need to write query.

For every doctor i need average cost of visit by month for 2009 year. The result is (name_of_doctor, january, febriary, ..., december)

I know how to do this with 12 subquery. Exists another more convinient way?

3 回复 | 直到 15 年前

Tom H zenazn 15 年前

I would just do a normal select with a GROUP BY over the month and have your UI handle displaying it as 12 columns across. If you really need to do it though, then this should work:

SELECT
    D.name,
    AVG(CASE WHEN MONTH(V.visit_date) = 1 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 2 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 3 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 4 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 5 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 6 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 7 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 8 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 9 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 10 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 11 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 12 THEN V.cost ELSE NULL END)
FROM
    Doctors D
INNER JOIN Visits V ON
    V.id_doc = D.id AND
    V.visit_date BETWEEN '2009-01-01' AND '2009-12-31'
GROUP BY
    D.name
ORDER BY
    D.name

You might need to change the date functions based on your RDBMS. Also, you may need to fiddle with the edge cases - if your dates have a time component it won't catch rows on 12/31.

最后,我不知道RDBMS和我之间的这种变化是否现在无法测试,但是如果AVG将空值计算为0成本而不是折扣它们,那么您可能需要做您自己的averag-sum(case…成本…0)/总和(大小写…1…0)。我希望这是有道理的。

Jason McCreary 15 年前

Try the following. You may need to modify the date functions depending on your RDBMS. I have assumed MySQL, but the rest should be universal SQL.

SELECT doctors.name, monthly.average, monthly.month
FROM doctors JOIN (
  SELECT AVG(cost), MONTH(visit_date) AS month FROM visits
  WHERE YEAR(visit_date) GROUP BY MONTH(visit_date)
) AS monthly ON doctors.id = visits.id_doc

Note, this may only include months for doctors that have visits. So you may need to use IFNULL 或 COALESCE 清理你的输出。

josephj1989 15 年前

您可以像下面这样轻松地完成这项工作,但它会在单独的一行中列出每个月的成本。如果您希望将成本放在同一行,则可以使用PIVOT语句。对于SQL 2008,如果需要进行数据透视,则可以这样做。如果存在性能问题,请使用日期范围扫描,而不是使用日期部分扫描。

select d.name,datepart(month,v.visit_date) as month,
avg(v.cost) as avgcost
from visits as v inner join 
doctors as d on v.id_doc=d.id
and datepart(year,v.visit_date)=2010
group by d.name,datepart(month,v.visit_date)