时间戳

首页 > 解决方案 > 在全局 ORDER 子句中使用多个 SELECT 的问题

mysql - 在全局 ORDER 子句中使用多个 SELECT 的问题

问题描述

我需要组合五个 SQL 结果集，使用 UNION 集合运算符一次获取七个表

ORDER我曾经在从子句执行查询时从表中获取错误代码：'team'、'user_notifications'、'match_notifications'

Error Code: 1250
Table 'team' from one of the SELECTs cannot be used in global ORDER clause
Table 'user_notifications' from one of the SELECTs cannot be used in global ORDER clause
Table 'match_notifications' from one of the SELECTs cannot be used in global ORDER clause

这是 SQL 查询

(SELECT
  `team`.`Team_ID`,
  `team_member`.`IsNotified`,
  `team_member`.`Team_Member_ID`,
  `team`.`Team_Name`,
  `team`.`Team_Logo`
FROM
  `team_member`
  LEFT JOIN `team`
    ON (
      `team_member`.`Team_ID` = `team`.`Team_ID`
    )
  LEFT JOIN `users`
    ON (
      `users`.`ID` = `team_member`.`Team_User_ID`
    )
WHERE `team_member`.`Team_User_ID` = '1'
  AND `team`.`Founder_ID` NOT IN
  (SELECT
    `team`.`Founder_ID`
  FROM
    `team`
  WHERE `team`.`Founder_ID` = '1'))
UNION
(SELECT
  ID,
  online_timestamp,
  username,
  online_timestamp,
  username,
  avatar
FROM
  users
WHERE users.ID IN
  (SELECT
    userid
  FROM
    user_friends
  WHERE STATUS = '1'
    AND `friendid` = '1') -- LIMIT 3 OFFSET 0 
)
UNION
(SELECT
  `user_notifications`.`notifi_id`,
  `users`.`ID`,
  `user_notifications`.`message`
FROM
  user_notifications
  JOIN users
    ON (
      user_notifications.fromid = users.ID
    )
WHERE user_notifications.status = '0'
  AND user_notifications.userid = '1')
UNION
(SELECT
  `team`.`Team_ID`,
  `team`.`Founder_ID`,
  `team`.`Team_Logo`,
  `team`.`Team_Name`,
  `team_member`.`IsFounder`,
  `team_member`.`IsTeamLead`,
  `team_member`.`Team_ID`,
  `team_member`.`Team_UserName`,
  `team_member`.`Team_User_ID`,
  `match_notifications`.`accept_status`,
  `match_notifications`.`game_match_id`,
  `match_notifications`.`message`,
  `match_notifications`.`notification_status`,
  `match_notifications`.`team_1_id`,
  `match_notifications`.`team_2_id`,
  `match_notifications`.`timestamp`,
  `teams_game_match`.`accept`,
  `teams_game_match`.`game_date`,
  `teams_game_match`.`game_id`,
  `teams_game_match`.`team_1_name`,
  `teams_game_match`.`team_2_name`,
  `teams_game_match`.`game_name`
FROM
  `team`
  INNER JOIN `team_member`
    ON (
      `team`.`Team_ID` = `team_member`.`Team_ID`
    )
  INNER JOIN `match_notifications`
    ON (
      `team`.`Team_ID` = `match_notifications`.`team_2_id`
    )
  INNER JOIN `teams_game_match`
    ON (
      `teams_game_match`.`game_match_id` = `match_notifications`.`game_match_id`
    )
WHERE `team_member`.`Team_User_ID` = '1'
  OR `team`.`Founder_ID` = '1')
UNION
(SELECT
  `match_notifications`.`notification_id`,
  `match_notifications`.`notification_status`
FROM
  `match_notifications`
  INNER JOIN `team_member`
    ON (
      `team_member`.`Team_ID` = `match_notifications`.`team_1_id`
    )
  INNER JOIN `teams_game_match`
    ON (
      `teams_game_match`.`game_match_id` = `match_notifications`.`game_match_id`
    )
  INNER JOIN `team`
    ON (
      `match_notifications`.`team_1_id` = `team`.`Team_ID`
    )
WHERE `match_notifications`.`accept_status` != '0'
  AND `team_member`.`IsTeamLead` = 1
  AND (
    `team_member`.`Team_User_ID` = '1'
    OR `team`.`Founder_ID` = '1'
  ))
ORDER BY 
`team`.`Team_ID`,
`user_notifications`.`notifi_id`,
`match_notifications`.`game_match_id`,
`match_notifications`.`notification_id` 
DESC LIMIT 15 OFFSET 0

有关修复错误代码的任何建议：1250 或

UNION除了使用集合运算符之外，我应该如何结合以上四个SQL来实现？

标签： mysqlsqlsql-order-byunion

解决方案

最后，我弄清楚了 SQL 的语义结构。我使用了ORDER BYandLIMIT子句，设置为 3，将偏移量设置为 0。因此，选择最多 3 个不同的结果集，每 5 个查询最多使用 15 个。但不完全是 15，因为数据可能存在也可能不存在，每个结果集限制为最大 3

SELECT
  *
FROM
  (
    (SELECT
      t.Team_ID AS j_Team_ID,
      t_m.IsNotified AS j_IsNotified,
      t_m.Team_Member_ID AS j_Team_Member_ID,
      t.Team_Name AS j_Team_Name,
      t.Team_Logo AS j_Team_Logo,
      NULL AS un_notifi_id,
      NULL AS un_id,
      NULL AS un_message,
      NULL AS un_avatar,
      NULL AS un_online_timestamp,
      NULL AS un_username,
      NULL AS uf_id,
      NULL AS uf_online_timestamp,
      NULL AS uf_username,
      NULL AS uf_avatar,
      NULL AS mn_Team_ID,
      NULL AS mn_Founder_ID,
      NULL AS mn_Team_Logo,
      NULL AS mn_Team_Name,
      NULL AS mn_IsTeamLead,
      NULL AS mn_Team_UserName,
      NULL AS mn_Team_User_ID,
      NULL AS mn_accept_status,
      NULL AS mn_game_match_id,
      NULL AS mn_message,
      NULL AS mn_notification_status,
      NULL AS mn_team_1_id,
      NULL AS mn_team_2_id,
      NULL AS mn_timestamp,
      NULL AS mn_accept,
      NULL AS mn_game_date,
      NULL AS mn_game_id,
      NULL AS mn_team_1_name,
      NULL AS mn_team_2_name,
      NULL AS mn_teams_game_match,
      NULL AS mn_notification_id,
      NULL AS pm_Team_ID,
      NULL AS pm_Founder_ID,
      NULL AS pm_Team_Logo,
      NULL AS pm_Team_Name,
      NULL AS pm_IsTeamLead,
      NULL AS pm_game_match_id,
      NULL AS pm_team_1_id,
      NULL AS pm_team_2_id,
      NULL AS pm_timestamp,
      NULL AS pm_accept,
      NULL AS pm_game_date,
      NULL AS pm_team_1_name,
      NULL AS pm_team_2_name,
      NULL AS pm_game_name,
      NULL AS pm_notification_status,
      NULL AS pm_notification_id,
      NULL AS pm_message
    FROM
      team_member t_m
      LEFT JOIN team t
        ON t_m.Team_ID = t.Team_ID
      LEFT JOIN users usr
        ON usr.ID = t_m.Team_User_ID
    WHERE t_m.Team_User_ID = '1'
      AND t.Founder_ID NOT IN
      (SELECT
        Founder_ID
      FROM
        team
      WHERE Founder_ID = '1')
    ORDER BY `t`.`Team_ID` DESC-- limit 0,3
    )
    UNION
    (SELECT
      NULL AS j_Team_ID,
      NULL AS j_IsNotified,
      NULL AS j_Team_Member_ID,
      NULL AS j_Team_Name,
      NULL AS j_Team_Logo,
      `un`.`notifi_id` AS un_notifi_id,
      `users`.`ID` AS un_id,
      `un`.`message` AS un_message,
      `users`.`avatar` AS un_avatar,
      `users`.`online_timestamp` AS un_online_timestamp,
      `users`.`username` AS un_username,
      NULL AS uf_id,
      NULL AS uf_online_timestamp,
      NULL AS uf_username,
      NULL AS uf_avatar,
      NULL AS mn_Team_ID,
      NULL AS mn_Founder_ID,
      NULL AS mn_Team_Logo,
      NULL AS mn_Team_Name,
      NULL AS mn_IsTeamLead,
      NULL AS mn_Team_UserName,
      NULL AS mn_Team_User_ID,
      NULL AS mn_accept_status,
      NULL AS mn_game_match_id,
      NULL AS mn_message,
      NULL AS mn_notification_status,
      NULL AS mn_team_1_id,
      NULL AS mn_team_2_id,
      NULL AS mn_timestamp,
      NULL AS mn_accept,
      NULL AS mn_game_date,
      NULL AS mn_game_id,
      NULL AS mn_team_1_name,
      NULL AS mn_team_2_name,
      NULL AS mn_teams_game_match,
      NULL AS mn_notification_id,
      NULL AS pm_Team_ID,
      NULL AS pm_Founder_ID,
      NULL AS pm_Team_Logo,
      NULL AS pm_Team_Name,
      NULL AS pm_IsTeamLead,
      NULL AS pm_game_match_id,
      NULL AS pm_team_1_id,
      NULL AS pm_team_2_id,
      NULL AS pm_timestamp,
      NULL AS pm_accept,
      NULL AS pm_game_date,
      NULL AS pm_team_1_name,
      NULL AS pm_team_2_name,
      NULL AS pm_game_name,
      NULL AS pm_notification_status,
      NULL AS pm_notification_id,
      NULL AS pm_message
    FROM
      user_notifications un
      JOIN users
        ON (un.fromid = users.ID)
    WHERE un.status = '0'
      AND un.userid = '1'
    ORDER BY `un`.`notifi_id` DESC-- limit 3
    )
    UNION
    (SELECT
      NULL AS j_Team_ID,
      NULL AS j_IsNotified,
      NULL AS j_Team_Member_ID,
      NULL AS j_Team_Name,
      NULL AS j_Team_Logo,
      NULL AS un_notifi_id,
      NULL AS un_id,
      NULL AS un_message,
      NULL AS un_avatar,
      NULL AS un_online_timestamp,
      NULL AS un_username,
      ID AS uf_id,
      online_timestamp AS uf_online_timestamp,
      username AS uf_username,
      avatar AS uf_avatar,
      NULL AS mn_Team_ID,
      NULL AS mn_Founder_ID,
      NULL AS mn_Team_Logo,
      NULL AS mn_Team_Name,
      NULL AS mn_IsTeamLead,
      NULL AS mn_Team_UserName,
      NULL AS mn_Team_User_ID,
      NULL AS mn_accept_status,
      NULL AS mn_game_match_id,
      NULL AS mn_message,
      NULL AS mn_notification_status,
      NULL AS mn_team_1_id,
      NULL AS mn_team_2_id,
      NULL AS mn_timestamp,
      NULL AS mn_accept,
      NULL AS mn_game_date,
      NULL AS mn_game_id,
      NULL AS mn_team_1_name,
      NULL AS mn_team_2_name,
      NULL AS mn_teams_game_match,
      NULL AS mn_notification_id,
      NULL AS pm_Team_ID,
      NULL AS pm_Founder_ID,
      NULL AS pm_Team_Logo,
      NULL AS pm_Team_Name,
      NULL AS pm_IsTeamLead,
      NULL AS pm_game_match_id,
      NULL AS pm_team_1_id,
      NULL AS pm_team_2_id,
      NULL AS pm_timestamp,
      NULL AS pm_accept,
      NULL AS pm_game_date,
      NULL AS pm_team_1_name,
      NULL AS pm_team_2_name,
      NULL AS pm_game_name,
      NULL AS pm_notification_status,
      NULL AS pm_notification_id,
      NULL AS pm_message
    FROM
      users
    WHERE users.ID IN
      (SELECT
        userid
      FROM
        user_friends
      WHERE STATUS = '1'
        AND `friendid` = '1')
    ORDER BY uf_online_timestamp DESC-- limit 0,3
    )
    UNION
    (SELECT
      NULL AS j_Team_ID,
      NULL AS j_IsNotified,
      NULL AS j_Team_Member_ID,
      NULL AS j_Team_Name,
      NULL AS j_Team_Logo,
      NULL AS un_notifi_id,
      NULL AS un_id,
      NULL AS un_message,
      NULL AS un_avatar,
      NULL AS un_online_timestamp,
      NULL AS un_username,
      NULL AS uf_id,
      NULL AS uf_online_timestamp,
      NULL AS uf_username,
      NULL AS uf_avatar,
      `team`.`Team_ID` AS mn_Team_ID,
      `team`.`Founder_ID` AS mn_Founder_ID,
      `team`.`Team_Logo` AS mn_Team_Logo,
      `team`.`Team_Name` AS mn_Team_Name,
      `team_member`.`IsTeamLead` AS mn_IsTeamLead,
      `team_member`.`Team_UserName` AS mn_Team_UserName,
      `team_member`.`Team_User_ID` AS mn_Team_User_ID,
      `match_notifications`.`accept_status` AS mn_accept_status,
      `match_notifications`.`game_match_id` AS mn_game_match_id,
      `match_notifications`.`message` AS mn_message,
      `match_notifications`.`notification_status` AS mn_notification_status,
      `match_notifications`.`team_1_id` AS mn_team_1_id,
      `match_notifications`.`team_2_id` AS mn_team_2_id,
      `match_notifications`.`timestamp` AS mn_timestamp,
      `teams_game_match`.`accept` AS mn_accept,
      `teams_game_match`.`game_date` AS mn_game_date,
      `teams_game_match`.`game_id` AS mn_game_id,
      `teams_game_match`.`team_1_name` AS mn_team_1_name,
      `teams_game_match`.`team_2_name` AS mn_team_2_name,
      `teams_game_match`.`game_name` AS mn_teams_game_match,
      `match_notifications`.`notification_id` AS mn_notification_id,
      NULL AS pm_Team_ID,
      NULL AS pm_Founder_ID,
      NULL AS pm_Team_Logo,
      NULL AS pm_Team_Name,
      NULL AS pm_IsTeamLead,
      NULL AS pm_game_match_id,
      NULL AS pm_team_1_id,
      NULL AS pm_team_2_id,
      NULL AS pm_timestamp,
      NULL AS pm_accept,
      NULL AS pm_game_date,
      NULL AS pm_team_1_name,
      NULL AS pm_team_2_name,
      NULL AS pm_game_name,
      NULL AS pm_notification_status,
      NULL AS pm_notification_id,
      NULL AS pm_message
    FROM
      `match_notifications`
      INNER JOIN `team_member`
        ON (
          `team_member`.`Team_ID` = `match_notifications`.`team_1_id`
        )
      INNER JOIN `teams_game_match`
        ON (
          `teams_game_match`.`game_match_id` = `match_notifications`.`game_match_id`
        )
      INNER JOIN `team`
        ON (
          `match_notifications`.`team_1_id` = `team`.`Team_ID`
        )
    WHERE `match_notifications`.`accept_status` != '0'
      AND `team_member`.`IsTeamLead` = 1
      AND (
        `team_member`.`Team_User_ID` = '1'
        OR `team`.`Founder_ID` = '1'
      )
    ORDER BY `match_notifications`.`notification_id` DESC-- LIMIT 3 OFFSET 0
    )
    UNION
    (SELECT
      NULL AS j_Team_ID,
      NULL AS j_IsNotified,
      NULL AS j_Team_Member_ID,
      NULL AS j_Team_Name,
      NULL AS j_Team_Logo,
      NULL AS un_notifi_id,
      NULL AS un_id,
      NULL AS un_message,
      NULL AS un_avatar,
      NULL AS un_online_timestamp,
      NULL AS un_username,
      NULL AS uf_id,
      NULL AS uf_online_timestamp,
      NULL AS uf_username,
      NULL AS uf_avatar,
      NULL AS mn_Team_ID,
      NULL AS mn_Founder_ID,
      NULL AS mn_Team_Logo,
      NULL AS mn_Team_Name,
      NULL AS mn_IsTeamLead,
      NULL AS mn_Team_UserName,
      NULL AS mn_Team_User_ID,
      NULL AS mn_accept_status,
      NULL AS mn_game_match_id,
      NULL AS mn_message,
      NULL AS mn_notification_status,
      NULL AS mn_team_1_id,
      NULL AS mn_team_2_id,
      NULL AS mn_timestamp,
      NULL AS mn_accept,
      NULL AS mn_game_date,
      NULL AS mn_game_id,
      NULL AS mn_team_1_name,
      NULL AS mn_team_2_name,
      NULL AS mn_teams_game_match,
      NULL AS mn_notification_id,
      `team`.`Team_ID` AS pm_Team_ID,
      `team`.`Founder_ID` AS pm_Founder_ID,
      `team`.`Team_Logo` AS pm_Team_Logo,
      `team`.`Team_Name` AS pm_Team_Name,
      `team_member`.`IsTeamLead` AS pm_IsTeamLead,
      `match_notifications`.`game_match_id` AS pm_game_match_id,
      `match_notifications`.`team_1_id` AS pm_team_1_id,
      `match_notifications`.`team_2_id` AS pm_team_2_id,
      `match_notifications`.`timestamp` AS pm_timestamp,
      `teams_game_match`.`accept` AS pm_accept,
      `teams_game_match`.`game_date` AS pm_game_date,
      `teams_game_match`.`team_1_name` AS pm_team_1_name,
      `teams_game_match`.`team_2_name` AS pm_team_2_name,
      `teams_game_match`.`game_name` AS pm_game_name,
      `match_notifications`.`notification_status` AS pm_notification_status,
      `match_notifications`.`notification_id` AS pm_notification_id,
      `match_notifications`.`message` AS pm_message
    FROM
      `team`
      INNER JOIN `team_member`
        ON (
          `team`.`Team_ID` = `team_member`.`Team_ID`
        )
      INNER JOIN `match_notifications`
        ON (
          `team`.`Team_ID` = `match_notifications`.`team_2_id`
        )
      INNER JOIN `teams_game_match`
        ON (
          `teams_game_match`.`game_match_id` = `match_notifications`.`game_match_id` AND `team_member`.`IsTeamLead` = 1 AND `teams_game_match`.`accept` = 0
        )
    WHERE `team_member`.`Team_User_ID` = '1'
      OR `team`.`Founder_ID` = '1'
    ORDER BY `match_notifications`.`game_match_id` DESC-- LIMIT 3 OFFSET 0 
    )
  ) A

如果有人知道比我发布的更好的解决方案，您可以发布您的答案或留下建设性的批评评论，该评论总是被接受

推荐阅读