我担心我错误地考虑了SQL。我可以将查询混合在一起做我想做的事，但是它们最终由于嵌套了多个选择而变得非常荒谬。这是一个我无法理解如何简化的示例（更不用说如何提高性能了）。我的主要问题之一是当我将用户分组在一起时如何从工会中挑选最新消息。将其包装在另一个选择中只是为了使其正常工作，但看起来太难看了，我一直在担心这样做的性能。我对查询功能的简单英语解释是：
查看与给定用户相关的已发送和已接收消息（在本例中为硬编码为用户ID 1），并返回向我发送消息的所有用户的唯一列表，以及我们之间最近发送或接收消息的时间。
不幸的是，我存储在另一个元表中的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');
最佳答案
另一种可能和更简单的方法是
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;
关于mysql - 有没有更好的方法来简化许多嵌套的select语句？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54922847/