第一个表中的所有数据都未显示在sql中

Question

我正在尝试添加两个表，其中第一个表包含所有视频详细信息，在第二个表中，用户可以使用user_id和video_id看到视频的详细信息。我只想添加两个表，它将显示来自第一个表的所有视频列表，但如果视频是由用户看到，状态将显示1或1。

以下是我的查询，

SELECT
    videos.id, videos.lang_id, videos.medical_type_id, videos.name, 
    videos.description, videos.thumbnail, videos.video, videos.video_type, 
    videos.delete_status, 
    CASE 
       WHEN video_quews.user_id = $user_id 
          THEN $user_id 
          ELSE 'ok' 
    END AS user_id, 
    video_quews.video_id, video_quews.created_at, 
    CASE 
       WHEN video_quews.video_id = videos.id 
          THEN 1 
          ELSE 0 
    END AS status 
FROM
    videos 
LEFT JOIN 
    video_quews ON videos.id = video_quews.video_id 
ORDER BY   
    video_quews.video_id DESC

目前，视频正在重复。

请帮帮我

这是我的回应，

{
    "message": "All related videos",
    "status": "success",
    "code": 200,
    "videos": [
        {
            "id": "30",
            "lang_id": "2",
            "medical_type_id": "15",
            "name": "Fracture",
            "thumbnail": "thumbnail_images/1579869167.png",
            "video": "videos/fracture.m4v",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "6",
            "video_id": "30",
            "created_at": "2021-03-04 23:29:50",
            "status": "1"
        },
        {
            "id": "16",
            "lang_id": "2",
            "medical_type_id": "14",
            "name": "Electrocution",
            "thumbnail": "thumbnail_images/1579698529.png",
            "video": "videos/ELECTROCUTION.m4v",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": "16",
            "created_at": "2021-03-05 08:19:29",
            "status": "1"
        },
        {
            "id": "15",
            "lang_id": "2",
            "medical_type_id": "13",
            "name": "Adult CPR & AED(Cardiac Arrest)",
            "thumbnail": "thumbnail_images/1579698505.png",
            "video": "videos/CPR1.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": "15",
            "created_at": "2021-03-05 08:18:38",
            "status": "1"
        },
        {
            "id": "14",
            "lang_id": "2",
            "medical_type_id": "12",
            "name": "Choking",
            "thumbnail": "thumbnail_images/1579698405.png",
            "video": "videos/Choking.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": "14",
            "created_at": "2021-03-05 08:17:47",
            "status": "1"
        },
        {
            "id": "13",
            "lang_id": "2",
            "medical_type_id": "11",
            "name": "Chest pain",
            "thumbnail": "thumbnail_images/1579698381.png",
            "video": "videos/CHESTPAIN.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": "13",
            "created_at": "2021-03-05 08:17:20",
            "status": "1"
        },
        {
            "id": "12",
            "lang_id": "2",
            "medical_type_id": "10",
            "name": "Burns",
            "thumbnail": "thumbnail_images/1579698360.png",
            "video": "videos/Burns.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": "12",
            "created_at": "2021-03-05 08:16:39",
            "status": "1"
        },
        {
            "id": "11",
            "lang_id": "2",
            "medical_type_id": "9",
            "name": "Breathing Difficulties",
            "thumbnail": "thumbnail_images/1579698344.png",
            "video": "videos/BreathingDifficulties.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": "11",
            "created_at": "2021-03-05 08:16:19",
            "status": "1"
        },
        {
            "id": "8",
            "lang_id": "2",
            "medical_type_id": "6",
            "name": "Asthma",
            "thumbnail": "thumbnail_images/1579698286.png",
            "video": "videos/ASTHMA.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": "8",
            "created_at": "2021-03-05 08:15:59",
            "status": "1"
        },
        {
            "id": "24",
            "lang_id": "2",
            "medical_type_id": "22",
            "name": "Fits/Seizures",
            "thumbnail": "thumbnail_images/1579698775.png",
            "video": "videos/SEIZURES.m4v",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "26",
            "lang_id": "2",
            "medical_type_id": "24",
            "name": "Sprain, strain",
            "thumbnail": "thumbnail_images/1579698853.png",
            "video": "videos/SPRAIN&STRAIN.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "28",
            "lang_id": "2",
            "medical_type_id": "26",
            "name": "Disposing Glove",
            "thumbnail": "thumbnail_images/1579698903.png",
            "video": "videos/disposingglove.mp4",
            "video_type": "1",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "31",
            "lang_id": "2",
            "medical_type_id": "8",
            "name": "Bleeding",
            "thumbnail": "thumbnail_images/1581498917.png",
            "video": "videos/bleeding.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "18",
            "lang_id": "2",
            "medical_type_id": "16",
            "name": "Gunshot Wound",
            "thumbnail": "thumbnail_images/1579698550.png",
            "video": "videos/Gunshot.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "20",
            "lang_id": "2",
            "medical_type_id": "18",
            "name": "Hypoglycemia",
            "thumbnail": "thumbnail_images/1579698684.png",
            "video": "videos/Hypoglycemia.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "22",
            "lang_id": "2",
            "medical_type_id": "20",
            "name": "Nose Bleeding",
            "thumbnail": "thumbnail_images/1579698728.png",
            "video": "videos/nosebleeding.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "25",
            "lang_id": "2",
            "medical_type_id": "23",
            "name": "Snake Bite",
            "thumbnail": "thumbnail_images/1579698801.png",
            "video": "videos/SnakeBite.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "27",
            "lang_id": "2",
            "medical_type_id": "25",
            "name": "Stroke",
            "thumbnail": "thumbnail_images/1579698879.png",
            "video": "videos/STROKE.m4v",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "29",
            "lang_id": "2",
            "medical_type_id": "27",
            "name": "Dog Bite",
            "thumbnail": "thumbnail_images/1579698934.png",
            "video": "videos/Dogbite.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "32",
            "lang_id": "2",
            "medical_type_id": "7",
            "name": "Allergy",
            "thumbnail": "thumbnail_images/1581580973.png",
            "video": "videos/Allergy.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "19",
            "lang_id": "2",
            "medical_type_id": "17",
            "name": "Hyperthermia(Heat Exhaustion)",
            "thumbnail": "thumbnail_images/1579698575.png",
            "video": "videos/Hyperthermia.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "21",
            "lang_id": "2",
            "medical_type_id": "19",
            "name": "Introduction",
            "thumbnail": "thumbnail_images/1579698707.png",
            "video": "videos/intro.mp4",
            "video_type": "1",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        },
        {
            "id": "23",
            "lang_id": "2",
            "medical_type_id": "21",
            "name": "Recovery Position",
            "thumbnail": "thumbnail_images/1579698747.png",
            "video": "videos/RecoveryPosition.mp4",
            "video_type": "2",
            "delete_status": "1",
            "user_id": "ok",
            "video_id": null,
            "created_at": null,
            "status": "0"
        }
    ],
}

下面是我的数据库enter image description here

Answer 1

在video_ques表中，一个视频有多行，这就是为什么你会得到重复的项目。请澄清，如果有多个用户在video_ques表中的一个视频，你想如何表示这些。我已经更改了查询，如果有多个行，则从video_ques获取第一个匹配。请检查以下查询：

低于8版本的MYSQL的：

  SELECT
        videos.id, videos.lang_id, videos.medical_type_id, videos.name, 
        videos.description, videos.thumbnail, videos.video, videos.video_type, 
        videos.delete_status, 
        CASE 
           WHEN VQ.user_id = $user_id 
              THEN $user_id 
              ELSE 'ok' 
        END AS user_id, 
        VQ.video_id, VQ.created_at, 
        CASE 
           WHEN VQ.video_id = videos.id 
              THEN 1 
              ELSE 0 
        END AS status 
    FROM
        videos 
    LEFT JOIN 
        (SELECT * ,(SELECT COUNT(*)  FROM video_quews b WHERE a.video_id=b.video_id and a.user_id >= b.user_id) rn from video_quews a ) VQ ON videos.id = VQ.video_id and rn=1
    ORDER BY   
        VQ.video_id DESC

适用于MySQL版本8及更高版本的：

     with cte as (select * ,ROW_NUMBER()over(partition by video_id order by user_id)rn from video_quews )
SELECT
    videos.id, videos.lang_id, videos.medical_type_id, videos.name, 
    videos.description, videos.thumbnail, videos.video, videos.video_type, 
    videos.delete_status, 
    CASE 
       WHEN video_quews.user_id = $user_id 
          THEN $user_id 
          ELSE 'ok' 
    END AS user_id, 
    video_quews.video_id, video_quews.created_at, 
    CASE 
       WHEN video_quews.video_id = videos.id 
          THEN 1 
          ELSE 0 
    END AS status 
FROM
    videos 
LEFT JOIN 
    cte ON videos.id = video_quews.video_id and rn=1
ORDER BY   
    video_quews.video_id DESC