MySQL中复杂的COUNT查询 [英] Complicated COUNT query in MySQL
问题描述
我正在尝试查找特定用户拥有的视频点数。
I am trying to find the number of video credits a particular user has.
以下是相关的三个表:
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`)
)
videoinfo
表是用户上传视频时,获取 uploaded_by列表。 videocredit
表是给定电影的所有片数。它完全独立于上载影片(即,用户可以在不信誉自己的情况下上传视频,而用户可以在他尚未上传的视频中获得信誉)。
The videoinfo
table is when a user uploads a video, he will get an "uploaded_by" listing. The videocredit
table are all the credits for a given film. It is entirely independent of uploading the film (i.e., a video can be uploaded by a user without crediting himself, and a user can be credited in a video he has not uploaded).
在尝试查找用户已记入的COUNT个视频时,我想查找:
In trying to find the COUNT of videos a user has been credited in, I want to find:
# videos a user has uploaded + # of non duplicate-video credits uploaded by others
例如:用户上传了5个视频,分别为:
By way of example: if a user uploads 5 videos called:
VideoME1, VideoME2, VideoME3, VideoME4, and VideoME5
(total = 5 videos [`videoinfo.uploaded_by_id`])
并且具有以下视频片数:
And has the following video credits:
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
)。
The COUNT should be 5 (videos uploaded) + 2 (non-duplicate video credits uploaded by others) = 7. If a user has no video credits, it should = 0 (i.e., LEFT OUTER JOIN
).
我已经能够计算出每个上载/贷项的COUNTS,但无法弄清楚如何合并两者并消除重复项。我需要执行什么SQL?谢谢。
I've been able to figure out the COUNTS for each of the uploads/credits, but can't figure out how to combine the two and get rid of duplicates. What SQL do I need to do this? Thank you.
顺便说一句,这是我目前每个(个人)COUNT所拥有的:
By the way, this is what I currently have for each (individual) 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;
推荐答案
X-Zero建议添加上载额度数据是保持查询简单的最佳方法。如果不是这样,请在userprofile_videoinfo和userprofile_videocredit之间进行内部联接,以轻松消除重复项:
X-Zero's suggestion of adding an "uploader credit" to the data is the best way to keep the query simple. If that's not an option, do an inner join between userprofile_videoinfo and userprofile_videocredit to make it easy to eliminate duplicates:
SELECT u.id, u.full_name, COUNT(DISTINCT v.video_id) as credit_count
FROM userprofile_userprofile u
LEFT JOIN (SELECT vi.video_id, vi.uploaded_by_id, vc.profile_id as credited_to_id
FROM userprofile_videoinfo vi
JOIN userprofile_videocredit vc ON vi.id = vc.video_id
) v ON u.id = v.uploaded_by_id OR u.id = v.credited_to_id
GROUP BY u.id, u.full_name
ORDER BY credit_count DESC
子查询对于作为视图创建可能有用。
The subquery may be useful to create as a view.
这篇关于MySQL中复杂的COUNT查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!