Question

我有两个问题:

select count(*) over (partition by col1) from t1

以及

select case when count(*) over (partition by col1) >1 then 1 else 0 end from t1

第一个罚款。然而,第二种错误是:

Invalid column reference  count : (possible column names are: <all columns in t1>)

将任何栏目名称改为*,载于count。

What causes the problem? I haven t found any restrictions in dosc for case when 以及 over partition by together.

Answer 1

Try this:

select COUNT(CASE WHEN >1 THEN 1 ELSE 0 END) over (partition by col1) from t1;