时间戳

首页 > 解决方案 > 使用不同的 where 子句向我的查询添加另一列

sql-server - 使用不同的 where 子句向我的查询添加另一列

问题描述

有没有办法使用不应用于其他列的单独 where 子句将另一列添加到第一个查询？

select
  month(From_iso8601_timestamp(u.created)) as Month,
  count(distinct u.id) as Sign_Ups,
  count (distinct w.owner) as Sign_Ups_with_Want_Created,
  count(distinct g.owner) as Sign_Ups_with_Reel_Created
from
  prodjoinreel.users u
  left join prodjoinreel.goals g on g.owner = u.id
left join prodjoinreel.wants w on w.owner = u.id
where year(From_iso8601_timestamp(u.created)) = 2019
group by 1
order by 1

使用我要添加的附加列进行查询（我只希望将 where g.status = 'done' 应用于 Sign_ups_with_complete_reel 列）：

select
  count(distinct u.id) as New_Users
  , month(From_iso8601_timestamp(u.created)) as Month
from
  prodjoinreel.goals g
  right join prodjoinreel.users u on
    g.owner = u.id
where
  year(From_iso8601_timestamp(u.created)) = 2019
and g.status = 'done'
group by
  month(From_iso8601_timestamp(u.created)) as Month
order by
  month(From_iso8601_timestamp(u.created)) as Month asc

谢谢

标签： sql-server

解决方案

如果我理解正确，那么您应该将 `g.status = 'done' 从覆盖整个查询的 where 语句移到 count 聚合内的 case 语句中。我已经在下面完成了这项工作，并且我还稍微修改了您的查询以避免月份计算中的冗余：

select      New_Users = count(distinct u.id), 
            ap.Month,
            Sign_Ups_with_Want_Created = count(distinct w.owner),

            Sign_Ups_with_Reel_Created = 
                count(distinct 
                    case 
                    when g.status = 'done' then g.owner 
                    end
                )

from        prodjoinreel.goals g
right join  prodjoinreel.users u on g.owner = u.id
cross apply (select Month = From_iso8601_timestamp(u.created)) ap
where       year(From_iso8601_timestamp(u.created)) = 2019
group by    ap.Month
order by    ap.Month

推荐阅读