oracle 2 列中 countif() 的 SQL 等价物
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19158409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL equivalent of countif() across 2 columns
提问by bawpie
I am aware that there is a question with the same title as this one, but I'm not sure it addresses my question.
我知道有一个与此标题相同的问题，但我不确定它是否解决了我的问题。
I have data that looks like this:
我有看起来像这样的数据：
ID Current Nxt
1 1A 1B
1 1B 1C
2 2A 2B
3 3A 3B
3 3B
Using countif in Excel '=COUNTIF($B$2:$B$6,C2)' I can get the data to look like this (formula is in occurred column):
在 Excel 中使用 countif '=COUNTIF($B$2:$B$6,C2)' 我可以让数据看起来像这样（公式在发生的列中）：
ID Current Nxt Occurred
1 1A 1B 1
1 1B 1C 0
2 2A 2B 0
3 3A 3B 1
3 3B 0
Basically I'm just interested in indicating if the Nxt scheduled event has actually occurred (which is indicated if it exists in the data).
基本上，我只是对指示 Nxt 调度事件是否实际发生感兴趣（如果它存在于数据中，则表明它）。
However, I would like to replicate this in my SQL query which looks like this:
但是，我想在我的 SQL 查询中复制它，如下所示：
SELECT
ID,
Current,
Nxt
FROM
Table
I think the answer could lie in doing something like:
我认为答案可能在于执行以下操作：
sum(case when Current='1B' THEN 1 ELSE 0 END) over (partition by Current)
as suggested here: Sql Server equivalent of a COUNTIF aggregate function. The above works for one record, but the issue is that I need to make the criteria reference the contents of the Nxt column rather than have it hardcoded as the following doesn't work:
如此处所建议：Sql Server 等效于 COUNTIF 聚合函数。以上适用于一条记录，但问题是我需要使标准引用 Nxt 列的内容而不是硬编码，因为以下内容不起作用：
sum(case when Current=Nxt THEN 1 ELSE 0 END) over (partition by Current)
I guess this is because Current and Nxt never actually match on a row?
我想这是因为 Current 和 Nxt 从未真正连续匹配过？
For reference my database uses Oracle 11.
作为参考，我的数据库使用 Oracle 11。
回答by Kobi
try this :
尝试这个 ：
SELECT id
,current
,nxt
,nvl2(nxt,decode(next,lead(current) over (partition by id order by id),1,0),0) occured
FROM yourtable
回答by Rachcha
I would suggest you do the followng:
我建议您执行以下操作：
SELECT id, current, nxt
FROM (SELECT id, current, nxt,
CASE lead(current)
over (partition by id order by id)
WHEN nxt THEN 1
ELSE 0
END AS occured
FROM myTable)
WHERE occured = 1;
EDIT :
编辑 ：
