作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个在分组时使用 ROLLUP
子句的 Sql Server 查询。我想要 Postgres 中的等效查询。 SQl Server 中的查询是:
SELECT (CASE WHEN acnt_dba_name Is Null THEN 'Total' ELSE acnt_dba_name END) as account,
(CASE WHEN evt_name Is Null THEN '' ELSE evt_name END) as event,
COUNT(CASE reg_is_complete WHEN true THEN 1 ELSE Null END) as regsComplete,
COUNT(CASE WHEN reg_frn_pro_id > 0 AND reg_is_complete = false THEN 1 ELSE Null END) as regsInComplete,
COUNT(CASE WHEN reg_frn_pro_id > 0 THEN Null ELSE 1 END) as regsClicks
FROM registrations_view
LEFT JOIN events ON (evt_id = reg_frn_evt_id)
LEFT JOIN accounts ON (acnt_id = evt_frn_acnt_id)
WHERE reg_date_created < #CreateODBCDate(url.endDate)#
AND reg_date_created > #CreateODBCDate(url.startDate)#
AND reg_is_active = true -- only active regs
AND reg_is_test = false -- only live registrations
-- AND reg_is_denied = false -- exclude denied reg statuses (include these for now RWB 8/7/2)
GROUP BY rollup(acnt_dba_name, evt_name)
-- Sort with Nulls at the bottom
ORDER BY acnt_dba_name, evt_name
最佳答案
with detail as (
select
acnt_dba_name as account,
evt_name as event,
count(case reg_is_complete when true then 1 else null end) as regscomplete,
count(case when reg_frn_pro_id > 0 and reg_is_complete = false then 1 else null end) as regsincomplete,
count(case when reg_frn_pro_id > 0 then null else 1 end) as regsclicks
from
registrations_view
left join
events on evt_id = reg_frn_evt_id
left join
accounts on acnt_id = evt_frn_acnt_id
where
reg_date_created < #CreateODBCDate(url.endDate)#
AND reg_date_created > #CreateODBCDate(url.startDate)#
and reg_is_active = true -- only active regs
and reg_is_test = false -- only live registrations
group by acnt_dba_name, evt_name
), account as (
select
account,
'' as event,
sum(regscomplete) as regscomplete,
sum(regsimcomplete) as regsincomplete,
sum(regsclicks) as regsclicks
from detail
group by account
), total as (
select
'Total' as account,
'' as event,
sum(regsComplete) as regsComplete,
sum(regsImComplete) as regsInComplete,
sum(regsClicks) as regsClicks
from account
)
select * from detail
union
select * from account
union
select * from total
order by account, event
关于sql-server - PostgreSQL 等同于 SQL Server GROUP BY WITH ROLLUP,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13990821/
判断这2个相似的Uris实际上相同的标准方法是什么? var a = new Uri("http://sample.com/sample/"); var b = new Uri("http://sam
这个问题在这里已经有了答案: Why does "true" == true show false in JavaScript? (5 个答案) 关闭 5 年前。 可能我很困惑,但我无法理解这个愚蠢
我是一名优秀的程序员,十分优秀!