代码之家 › 专栏 › 技术社区 › Ryan

有没有更好的方法来简化许多嵌套的select语句?

sql mysql

Ryan · 技术社区 · 7 年前

我担心我把SQL想错了。我可以一起破解查询来做我想做的事情,但是它们最终会以多个嵌套选择而结束,这是非常可笑的。下面是一个我无法理解如何简化的示例(更不用说如何提高性能了)。我的一个主要问题是,当我将用户分组到一起时,如何从联盟中挑选最新的消息。包装在另一个选择只是为了让它工作,但似乎太难看,我担心的性能与这样做的所有时间。我用简单的英语解释这个查询的作用是:

这个 uid 我存储在另一个元表中的字段也必须返回,这使得整个过程更加复杂:

SELECT ID, user_nicename, MAX(last_message) AS last_message, uid FROM
    (SELECT * FROM 
        (SELECT wp_users.ID, wp_users.user_nicename, MAX(time) as last_message, (SELECT meta_value FROM wp_usermeta WHERE user_id = recipient_user_id AND meta_key = 'uid') AS uid FROM `wp_usermessages`
            LEFT JOIN wp_users ON wp_users.ID = recipient_user_id
            WHERE sender_user_id = 1
            GROUP BY recipient_user_id, wp_users.user_nicename
        UNION
        SELECT wp_users.ID, wp_users.user_nicename, MAX(time) as last_message, (SELECT meta_value FROM wp_usermeta WHERE user_id = sender_user_id AND meta_key = 'uid') AS uid FROM `wp_usermessages`
            LEFT JOIN wp_users ON wp_users.ID = sender_user_id
            WHERE recipient_user_id = 1
            GROUP BY sender_user_id, wp_users.user_nicename
        ) SentReceived
     ORDER BY last_message) SentReceivedOrdered
GROUP BY ID, user_nicename, uid
ORDER BY `last_message`

db小提琴: https://www.db-fiddle.com/f/6z7jVN6DtUYcuZwSXxeMX7/0

CREATE TABLE `wp_usermessages` (
  `id` bigint(20) NOT NULL,
  `time` datetime NOT NULL,
  `sender_user_id` bigint(20) UNSIGNED NOT NULL,
  `recipient_user_id` bigint(20) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `message_read` bit(1) NOT NULL DEFAULT b'0');

INSERT INTO `wp_usermessages` (`id`, `time`, `sender_user_id`, `recipient_user_id`, `message`, `message_read`) VALUES
(1, '2019-02-26 08:29:57', 2, 1, 'Hi, just wanted to check how you are?', b'0'),
(2, '2019-02-28 08:29:24', 1, 2, 'good thank you', b'0'),
(2, '2019-03-28 08:29:24', 1, 2, 'already been a month', b'0'),
(2, '2019-02-28 08:29:24', 1, 2, 'good thank you', b'0'),
(3, '2019-02-28 08:30:05', 5, 1, 'hi', b'1'),
(3, '2019-02-28 08:00:00', 5, 1, 'anyone', b'1'),
(3, '2019-02-28 08:05:00', 5, 1, 'hello', b'1');


CREATE TABLE `wp_usermeta` (
  `umeta_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext
);

INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES
(73, 1, 'uid', '9f39aa0ecd89d45e'),
(74, 5, 'uid', '0000000000000000'),
(75, 2, 'uid', '1212121212121212');

CREATE TABLE `wp_users` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(255) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL,
  `user_activation_key` varchar(255) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT ''
);

INSERT INTO `wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES
(1, 'ryan', '-', 'ryan', '-', '', '2018-12-05 08:13:11', '', 0, 'ryan'),
(2, 'jim', '-', 'jim', '-', '', '2019-02-01 12:43:29', '', 0, 'test'),
(5, 'bob', '-', 'bob', '-', '', '2019-02-02 09:17:54', '', 0, 'test2');

0 回复 | 直到 7 年前

DEarTh 7 年前

另一种可能的更简单的方法是

SELECT
    tbl.conv_with,
    MAX(tbl.last_message),
    u.user_nicename,
    meta.meta_value
FROM (
        -- select the latest message for each chat
        SELECT ( CASE
                    WHEN um.recipient_user_id = 1
                        THEN um.sender_user_id
                        ELSE um.recipient_user_id
                 END
               ) AS conv_with,
               um.time AS last_message
        FROM wp_usermessages um
        WHERE um.sender_user_id = 1 OR um.recipient_user_id = 1
    ) AS tbl
-- join wp_usermeta to get meta_data for message
LEFT JOIN wp_usermeta meta ON meta.user_id = tbl.conv_with
        AND meta.meta_key = 'uid'
-- join wp_users to get user_nicename for message
LEFT JOIN wp_users u ON u.id = tbl.conv_with
GROUP BY tbl.conv_with,u.user_nicename,meta.meta_value;

Zohar Peled 7 年前

假设您从发布的查询中得到了正确的结果,下面是一种更简单的方法。请注意代码中的注释:

SELECT `ID`, `user_nicename`, `meta_value` as `uid`, `last_message`
FROM 
(
    -- Get the last time a message was sent between any user to user 1 
    SELECT `user_id`, MAX(`time`) AS last_message
    FROM (
        -- Get all the messages where user 1 was either the sender or the recipient
        -- with the message time.
        -- Using case to get the other user id for each record.
        SELECT  CASE WHEN `sender_user_id` 1 THEN `recipient_user_id` 
                ELSE `sender_user_id` 
                END As `user_id`, 
                `time`
        FROM `wp_usermessages`
        WHERE 1 IN(`recipient_user_id`, `sender_user_id`)
    ) AS A
    GROUP BY `user_id`
) AS B 
JOIN
(
    -- Get all the other details of the users
    SELECT `ID`, `user_nicename`, `meta_value` 
    FROM `wp_users` 
    JOIN `wp_usermeta` 
    ON `ID` = `user_id`
) AS C 
    ON B.user_id = C.ID