我有以下查询,我想按UserView ID降序排序,这似乎是一个很基本的问题,但我不知道如何做到,我想继续选择User.*,但按UsersView表主键desc排序,有什么想法吗?
$phql = " SELECT *
FROM User
WHERE user_id IN
( SELECT user_to
FROM UsersView
WHERE user_from=:user_to: )
AND gender!=:gender:
AND user_id NOT IN (".implode(",",$this->user->getBlocked()).")
ORDER BY last_visit DESC
/* order by should be usersview.id DESC */
";
运气好吗?
根据您的数据模型,您可以对UsersView进行额外的联接,以便在ORDER BY子句中使用它的id列。
$phql = " SELECT *
FROM User
JOIN UsersView on User.user_id = UsersView.user_to
WHERE user_id IN
( SELECT user_to
FROM UsersView
WHERE user_from=:user_to: )
AND gender!=:gender:
AND user_id NOT IN (".implode(",",$this->user->getBlocked()).")
ORDER BY UsersView.id DESC";
您还可以避免使用嵌套选择来进行筛选,而是使用联接表来进行筛选:
$phql = " SELECT *
FROM User
JOIN UsersView on User.user_id = UsersView.user_to
WHERE user_from=:user_to:
AND gender!=:gender:
AND user_id NOT IN (".implode(",",$this->user->getBlocked()).")
ORDER BY UsersView.id DESC";
您的问题实际上没有足够的信息来回答这个问题,这就是为什么有人问您的表结构。
我相信我知道你在问什么,所以我会"猜测"你的桌子结构。以下是我认为将使您走上正确道路的结构、数据和解决方案。我还删除了PHP,因为它与您的问题无关。
CREATE TABLE `User` (user_id int, gender varchar(1));
CREATE TABLE UsersView (user_to int, user_from int, last_visit datetime);
INSERT INTO `User` VALUES
(1, 'M'),(2, 'F'),(3, 'M'),(4, 'F'),(5, 'F');
INSERT INTO UsersView VALUES
(2, 1, CURRENT_TIMESTAMP),
(3, 1, CURRENT_TIMESTAMP),
(4, 1, CURRENT_TIMESTAMP),
(5, 1, CURRENT_TIMESTAMP),
(1, 2, CURRENT_TIMESTAMP),
(3, 2, CURRENT_TIMESTAMP),
(4, 2, CURRENT_TIMESTAMP),
(5, 2, CURRENT_TIMESTAMP),
(1, 3, CURRENT_TIMESTAMP),
(2, 3, CURRENT_TIMESTAMP),
(4, 3, CURRENT_TIMESTAMP),
(5, 3, CURRENT_TIMESTAMP),
(1, 4, CURRENT_TIMESTAMP),
(2, 4, CURRENT_TIMESTAMP),
(3, 4, CURRENT_TIMESTAMP),
(5, 4, CURRENT_TIMESTAMP);
SELECT u.user_id, max(v.last_visit)
FROM User u
LEFT OUTER JOIN UsersView v ON u.user_id = v.user_to
WHERE u.user_id IN (
SELECT user_to
FROM UsersView
WHERE user_from=1
)
AND u.gender != 'M'
AND u.user_id NOT IN (1)
GROUP BY u.user_id
ORDER BY max(v.last_visit) DESC;