postgesql:count包含项的标记

zmeyuzjn  于 2021-08-09  发布在  Java
关注(0)|答案(4)|浏览(260)

我有一个包含项和标记的表。如何提出一个要求,将显示标签和相同的标签数量我的项目。

CREATE TABLE items_tags_sets (
uit_set_id varchar PRIMARY KEY,
item_id varchar,
tag varchar
);

insert into items_tags_sets values('uid1', 'it1', 'tag_test1');
insert into items_tags_sets values('uid2', 'it2', 'tag_test1');
insert into items_tags_sets values('uid3', 'it3', 'tag_test2');
insert into items_tags_sets values('uid4', 'it4', 'tag_test2');
insert into items_tags_sets values('uid5', 'it1', 'tag_test3');
insert into items_tags_sets values('uid6', 'it2', 'tag_test3');
insert into items_tags_sets values('uid7', 'it3', 'tag_test4');
insert into items_tags_sets values('uid8', 'it4', 'tag_test4');
insert into items_tags_sets values('uid9', 'it1', 'tag_test5');
insert into items_tags_sets values('uid10', 'it2', 'tag_test5');
insert into items_tags_sets values('uid11', 'it1', 'tag_test1');
insert into items_tags_sets values('uid12', 'it1', 'tag_test1');
insert into items_tags_sets values('uid13', 'it1', 'tag_test3');
----------------------------------
-- 28062020
select
    items_tags_sets.uit_set_id,
    items_tags_sets.item_id,
    items_tags_sets.tag,
  count(distinct items_tags_sets.tag) as tags_count

from items_tags_sets
where 
items_tags_sets.item_id = 'it1'
group by items_tags_sets.uit_set_id, items_tags_sets.tag;

我需要这样的结果:

-----------------------
| it1 | tag_test1 | 3 |
| it1 | tag_test3 | 2 |
| it1 | tag_test5 | 1 |
-----------------------

链接到代码:https://rextester.com/ctmoq42435

9vw9lbht

9vw9lbht1#

你必须 group by item_id, tag :

select
  item_id,
  tag,
  count(*) as tags_count
from items_tags_sets
where item_id = 'it1'
group by item_id, tag;

请看演示。
结果:

| item_id | tag       | tags_count |
| ------- | --------- | ---------- |
| it1     | tag_test1 | 3          |
| it1     | tag_test3 | 2          |
| it1     | tag_test5 | 1          |
m2xkgtsf

m2xkgtsf2#

根据您的描述,我认为您需要自联接和聚合:

select its_1.item_id, its_1.tag, count(distinct its.item_id) as cnt
from items_tags_sets its_1 left join
     items_tags_sets its
     on its_1.tag = its.tag 
where its_1.item_id = 'it1'
group by its_1.item_id, its_1.tag
order by cnt desc;

但是,它返回2/2/2而不是3/2/1。
这是一把小提琴。

cdmah0mi

cdmah0mi3#

如果不在group by子句中使用任何聚合函数,则应排除该列。

select
item_id,
tag,
count(tag) as tags_count

from items_tags_sets
where 
item_id = 'it1'
group by item_id, tag;

输出

qlzsbp2j

qlzsbp2j4#

item_id tag tags_count
it1 tag_test1   3
it1 tag_test3   2
it1 tag_test5   1

相关问题