vc mysql count,MySQL中复杂的COUNT查询

I am trying to find the number of video credits a particular user has.

The following are the three tables that are relevant:

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 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).

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

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`])

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).

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.

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'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.