My model is the following:
我的模型如下:
table video:
int id (PK)
int views
table tag:
int id (PK)
table video_tag:
int id (PK)
int video_id (FK to video.id)
int tag_id (FK to tag.id)
The model describes a many-to-many relationship between tags and videos. A video can have multiple tags describing its content. It also has a field with the number of views. The same tag can also refer to multiple videos.
该模型描述了标签和视频之间的多对多关系。视频可以有多个标签来描述其内容。它还有一个包含视图数量的字段。相同的标签也可以引用多个视频。
I would like to build a query (preferably in JPA/JPQL/HQL, but SQL will do as well) that returns all the tags ordered by the sum of the views the videos they refer to have.
我想构建一个查询(最好是在JPA / JPQL / HQL中,但SQL也会这样做),它返回按照它们引用的视频的视图总和排序的所有标记。
For example: two videos videoA and videoB both have the tag Foo. VideoA has 2 views, videoB has 3 views. The number to sort on for tag Foo is 5.
例如:两个视频videoA和videoB都有标签Foo。 VideoA有2个视图,videoB有3个视图。标记Foo的排序数为5。
Here are two attempts using JPA:
以下是使用JPA的两次尝试:
@Query("SELECT t FROM Tag t, SUM(v.views) as viewCount FROM Video v WHERE t MEMBER OF v.tags ORDER BY viewCount DESC")
@Query("SELECT t FROM (SELECT t, SUM(v.views) as viewCount FROM Tag t, Video v WHERE t MEMBER OF v.tags) ORDER BY viewCount DESC")
Attempt using SQL:
尝试使用SQL:
select t.id, sum(v.views) from tag t, video v where t.id in (select vt.tag_id from video_tag vt where vt.tag_id=t.id and vt.video_id=v.id)
1 个解决方案
#1
0
A simple join
between the three tables and a group by
should work:
三个表和一个组之间的简单连接应该起作用:
select t.id, t.label, sum(views)
from video_tag as vt
inner join video as v on v.id = vt.video_id
inner join tag as t on t.id = vt.tag_id
group by t.id, t.label
;
See it running here: http://sqlfiddle.com/#!9/e366a0/1
看到它在这里运行:http://sqlfiddle.com/#!9 / e366a0 / 1
#1
0
A simple join
between the three tables and a group by
should work:
三个表和一个组之间的简单连接应该起作用:
select t.id, t.label, sum(views)
from video_tag as vt
inner join video as v on v.id = vt.video_id
inner join tag as t on t.id = vt.tag_id
group by t.id, t.label
;
See it running here: http://sqlfiddle.com/#!9/e366a0/1
看到它在这里运行:http://sqlfiddle.com/#!9 / e366a0 / 1