Question

我正试图找到特定用户的录像记录。

下面是三个相关表格:

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

www.un.org/Depts/DGACM/index_french.htm 表格是当用户上载录像时,他会收到“上载”清单。 www.un.org/Depts/DGACM/index_french.htm 表格是某一影片的所有信用额。它完全独立于上载电影(即,一个用户可以在不自信的情况下上载录像,一个用户可以被录入他未上载的录像)。

在试图找到视频会议时,一个用户被记在册,我想发现:

# 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 OU JOIN。

我已能够看到每个上载/认证的UNTS,但能够说明如何将这两者合并起来并清除重复。我需要做些什么? 谢谢。

顺便提一下,这是我目前对每个(个人)委员会的看法:

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;

Answer 1

X-Zero的建议是,在数据中增加“超载信贷”,这是保持简单查询的最佳途径。如果这种选择不可行,用户名——个人形象和用户名——之间就进行内部整合,以方便消除重复:

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

排序可能有助于形成一种观点。

Answer 2

There are basically 2 ways to do this:
1) Add uploader as something they can be credited for, and add a trigger to autopopulate the entry - only one table to go for, etc.
2) I believe the following query should also work (which will also take care of your full_name problem):

WITH credit_rollup (profile_id) as (SELECT profile_id
                                    FROM userprofile_videocredit 
                                    GROUP BY profile_id, video_id)
SELECT full_name, COALESCE((SELECT count(*) 
                            FROM credit_rollup as v
                            WHERE v.profile_id = u.id), 0) +
                  COALESCE((SELECT count(*)
                            FROM userprofile_videoinfo as v
                            WHERE v.uploaded_by_id = u.id), 0) as credits
FROM userprofile_userprofile
ORDER by credits DESC

虽然你不妨从每张桌子上删除用户名,只是用该名标语提一下。

只按每摄像计1个信用额。

After reviewing the post one more time, it has become apparent that I missed a key use of the word duplicate - in that the user was only credited once for uploading the video AND having some sort of credit in the video (director, editor, etc.), but not twice (which is an OR).
As such, the following query is more in line (and thanks to @simon for making me think about it):

WITH credit_rollup (uploaded_by_id, credited_to_id) 
                   AS (SELECT info.uploaded_by_id, credit.profile_id
                       FROM userprofile_videoinfo as info
                       JOIN userprofile_videocredit as credit
                       ON info.id = credit.video_id
                       GROUP BY info.uploaded_by_id, credit.profile_id)
SELECT usr.full_name, COALSECE((SELECT count(*)
                                 FROM credit_rollup as rollup
                                 WHERE rollup.uploaded_by_id = usr.id
                                 OR rollup.credited_to_id = usr.id), 0) as credits
FROM userprofile as usr
ORDER BY credits DESC

正如 @simon所说,CTE(他使用的是分局)可能有助于形成一种观点(从实质上来说,它列出了每个人的once,以便他们在录像中参与,这是一件手的事情。

Answer 3

If I haven t made any mistake:

SELECT u.id
     , u.full_name
     , ( SELECT COUNT(*) 
         FROM userprofile_videoinfo vi
         WHERE u.id = vi.uploaded_by_id
       ) AS cnt_VideosUploadedBy                         <---- 5
    , cnt_Credits_InMyUploads 
        + cnt_Videos_CreditedIn - cnt_Videos_CreditedIn_and_UploadBy
      AS cnt_Difficult                               <---- 5 + 4 - 2 = 7
    , cnt_Credits_Total                                  <---- 8
    , cnt_Credits_InMyUploads                            <---- 5
    , cnt_Videos_CreditedIn                              <---- 4 
    , cnt_Videos_CreditedIn_and_UploadBy                 <---- 2
FROM userprofile_userprofile u 
  LEFT JOIN
      ( SELECT u.id
             , COUNT(vc.video_id)
               AS cnt_Credits_Total
             , (COUNT(vi.profile)
               AS cnt_Credits_InMyUploads
             , COUNT(DISTINCT vc.video_id)
               AS cnt_Videos_CreditedIn
             , (COUNT(DISTINCT vi.id)
               AS cnt_Videos_CreditedIn_and_UploadBy
        FROM userprofile_userprofile u 
          JOIN userprofile_videocredit vc
            ON u.id = vc.profile_by_id
          LEFT JOIN userprofile_videoinfo vi
            ON vc.video_id = vi.id
            AND vi.profile = u.id
        GROUP BY u.id
      ) AS grp
    ON grp.id = u.id

友情链接