假设我有一个表:
ID A B
10 0 0
11 0 0
12 0 1
13 0 1
14 1 1
15 1 1
16 1 1
我希望我的表格输出为:
ID A B A_B_COUNT
10 0 0 2
11 0 0 2
12 0 1 2
13 0 1 2
14 1 1 3
15 1 1 3
16 1 1 3
但是用我这里的代码,我的输出看起来像这个
SELECT ID, COUNT(*) AS A_B_COUNT
FROM table
GROUP BY A, B
ID A B A_B_COUNT
10 0 0 2
12 0 1 2
14 1 1 3
我可以创建sql查询的任何方式都类似于我的顶级表与我当前创建的表使用:10.5.5-MariaDB
使用窗口函数:
select t.*, count(*) over (partition by a, b) as a_b_count
from t;
你可以做:
select e.A, e.B, c.cnt as A_B_COUNT
from entries e
join (
select A, B, count(*) as cnt
from entries
group by A, B
) as c
where e.A=c.A and e.B=c.B
参见db fiddle。