有人知道更漂亮的方式吗？ [英] Anyone know a prettier way of doing this ?

问题描述

select owner_id,
sum(CASE WHEN MONTH(close_date) = 1 THEN 1 ELSE 0 END) AS JanLeads,
sum(CASE WHEN MONTH(close_date) = 1 THEN amtfinanced ELSE 0 END) AS JanAmt,
sum(CASE WHEN MONTH(close_date) = 2 THEN 1 ELSE 0 END) AS FebLeads,
sum(CASE WHEN MONTH(close_date) = 2 THEN amtfinanced ELSE 0 END) AS FebAmt,
sum(CASE WHEN MONTH(close_date) = 3 THEN 1 ELSE 0 END) AS MarLeads,
sum(CASE WHEN MONTH(close_date) = 3 THEN amtfinanced ELSE 0 END) AS MarAmt,
sum(CASE WHEN MONTH(close_date) = 4 THEN 1 ELSE 0 END) AS AprLeads,
sum(CASE WHEN MONTH(close_date) = 4 THEN amtfinanced ELSE 0 END) AS AprAmt,
sum(CASE WHEN MONTH(close_date) = 5 THEN 1 ELSE 0 END) AS MayLeads,
sum(CASE WHEN MONTH(close_date) = 5 THEN amtfinanced ELSE 0 END) AS MayAmt,
sum(CASE WHEN MONTH(close_date) = 6 THEN 1 ELSE 0 END) AS JunLeads,
sum(CASE WHEN MONTH(close_date) = 6 THEN amtfinanced ELSE 0 END) AS JunAmt,
sum(CASE WHEN MONTH(close_date) = 7 THEN 1 ELSE 0 END) AS JulLeads,
sum(CASE WHEN MONTH(close_date) = 7 THEN amtfinanced ELSE 0 END) AS JulAmt,
sum(CASE WHEN MONTH(close_date) = 8 THEN 1 ELSE 0 END) AS AugLeads,
sum(CASE WHEN MONTH(close_date) = 8 THEN amtfinanced ELSE 0 END) AS AugAmt,
sum(CASE WHEN MONTH(close_date) = 9 THEN 1 ELSE 0 END) AS SepLeads,
sum(CASE WHEN MONTH(close_date) = 9 THEN amtfinanced ELSE 0 END) AS SepAmt,
sum(CASE WHEN MONTH(close_date) = 10 THEN 1 ELSE 0 END) AS OctLeads,
sum(CASE WHEN MONTH(close_date) = 10 THEN amtfinanced ELSE 0 END) AS OctAmt,
sum(CASE WHEN MONTH(close_date) = 11 THEN 1 ELSE 0 END) AS NovLeads,
sum(CASE WHEN MONTH(close_date) = 11 THEN amtfinanced ELSE 0 END) AS NovAmt,
sum(CASE WHEN MONTH(close_date) = 12 THEN 1 ELSE 0 END) AS DecLeads,
sum(CASE WHEN MONTH(close_date) = 12 THEN amtfinanced ELSE 0 END) AS DecAmt,
sum(amtfinanced) as yearlyamt,
count(owner_id) as yearlyleads
from opportunity o
inner join sumfinanced sf
on o.opp_id = sf.opp_id
group by owner_id
order by owner_id

推荐答案

初看，下面的查询应该会给你行结果：

At the first look, below query should give you result in rows:

SELECT OwnerId, MONTH(close_date) AS MonthId, COUNT(*) AS Amt
FROM ...
GROUP BY OwnerId, MONTH(close_date)

为了能够将其转换为列视图，请使用 pivot [ ^ ]。

在SQL查询中使用Pivot的简单方法 [ ^ ]

如果是SQL Server（你没有指定） ...



考虑使用公用表表达式：

If SQL Server (you didn't specify)...

Consider using a Common Table Expression:

WITH cte AS
(
  SELECT OwnerId
  , MONTH(close_date) close_month
  , COUNT(*) leads
  , SUM(amtfinanced) amtfinanced
  FROM ...
  GROUP BY OwnerId
  , MONTH(close_date)
)
SELECT ...
FROM cte 
...

这篇关于有人知道更漂亮的方式吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！