我正在尝试查找特定用户拥有的视频积分数量。
以下是相关的三个表:
CREATE TABLE `userprofile_userprofile` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`full_name` varchar(100) NOT NULL,
...
)
CREATE TABLE `userprofile_videoinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(256) NOT NULL,
`uploaded_by_id` int(11) NOT NULL,
...
KEY `userprofile_videoinfo_e43a31e7` (`uploaded_by_id`),
CONSTRAINT `uploaded_by_id_refs_id_492ba9396be0968c` FOREIGN KEY (`uploaded_by_id`) REFERENCES `userprofile_userprofile` (`id`)
)
CREATE TABLE `userprofile_videocredit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`video_id` int(11) NOT NULL,
`profile_id` int(11) DEFAULT NULL,
KEY `userprofile_videocredit_fa26288c` (`video_id`),
KEY `userprofile_videocredit_141c6eec` (`profile_id`),
CONSTRAINT `profile_id_refs_id_31fc4a6405dffd9f` FOREIGN KEY (`profile_id`) REFERENCES `userprofile_userprofile` (`id`),
CONSTRAINT `video_id_refs_id_4dcff2eeed362a80` FOREIGN KEY (`video_id`) REFERENCES `userprofile_videoinfo` (`id`)
)
The videoinfo
表是当用户上传视频时,他将获得“uploaded_by”列表。这videocredit
表是给定电影的所有学分。它完全独立于上传电影(即,用户可以上传视频而无需注明自己的姓名,并且可以在用户未上传的视频中注明姓名)。
在尝试查找用户已记入的视频 COUNT 时,我想查找:
# videos a user has uploaded + # of non duplicate-video credits uploaded by others
举例来说:如果用户上传 5 个视频,名为:
VideoME1, VideoME2, VideoME3, VideoME4, and VideoME5
(total = 5 videos [`videoinfo.uploaded_by_id`])
并拥有以下视频制作人员:
VideoME1 (4 credits - director, writer, editor, choreographer)
VideoME2 (1 credit)
VideoOTHER1 (2 credits - writer, editor)
VideoOTHER2 (1 credit - writer)
(total = 8 video credits [`videocredit.profile_id`])
COUNT 应为 5(上传的视频)+ 2(其他人上传的非重复视频片尾)= 7。如果用户没有视频片尾,则应 = 0(即,LEFT OUTER JOIN
).
我已经能够计算出每个上传/积分的计数,但无法弄清楚如何将两者结合起来并消除重复项。我需要什么 SQL 来执行此操作?谢谢。
顺便说一句,这是我目前为每个(个人)COUNT 拥有的:
mysql> SELECT full_name, v.video_id, COUNT(DISTINCT v.video_id) as cnt
-> FROM userprofile_userprofile u LEFT OUTER JOIN userprofile_videocredit v
-> ON u.id = V.profile_id
-> GROUP BY full_name
-> ORDER BY cnt DESC;
mysql> SELECT full_name, v.id, COUNT(v.uploaded_by_id) as cnt
-> FROM userprofile_userprofile u LEFT OUTER JOIN userprofile_videoinfo v
-> ON u.id = v.uploaded_by_id
-> GROUP BY full_name
-> ORDER BY cnt DESC;