MySQL/Mariadb 문제: "Group by" 앞에 "Order by DESC"가 표시됨

MySQL/Mariadb 문제: "Group by" 앞에 "Order by DESC"가 표시됨

요약:.하고싶어Order by전에Group

나는 그 문제에 대한 좋은 기사를 찾았지만, 결국 그것을 고치는 데 성공하지 못했다.https://eddies-shop.medium.com/mysql-when-to-order-before-group-13d54d6c4ebb

내 서버 구성:

• 서버 유형:마리아DB
• 서버 버전: 10.6.4-MariaDB - Arch Linux

질의에 대해서: 방 목록과 메시지는 받았는데, 각 방의 최신 메시지만 있으면 됩니다.그래서 그룹화 시켜야 돼요conversation_id, 및 정렬message_id or message_time.

위의 쿼리는 정상적으로 동작하지만 완전하지는 않습니다.각 방마다 중복된 열이 있습니다.

쿼리의 마지막 줄에 대한 코멘트를 해제하려고 할 때, 그리고 적용하려고 할 때GROUP BY main.conversation_id. 더 이상 순서가 맞지 않아 주문이 다시 깨졌습니다.

마이쿼리:

SELECT
  main.*
FROM
(
    SELECT
        sub.*
    FROM
    (
        SELECT
        conversation.id AS conversation_id,
        CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
        conversation.is_group AS conversation_isgroup,
        (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
    
        message.id AS message_id,
        message.type AS message_type,
        message.body AS message_body,
        message.filename AS message_filename,
        message.created_at AS message_time,

        message.user_id AS message_user_id,
        CONCAT(user.first_name, " ", user.last_name) AS message_user_name
    FROM
        conversation
    INNER JOIN
        conversation_member
        ON
            conversation_member.conversation_id = conversation.id
    LEFT JOIN
        message
        ON
            message.conversation_id = conversation.id
    LEFT JOIN
        user
        ON
            user.id = message.user_id
    LEFT JOIN
        user as user2
        ON
            user2.id = conversation.owner2_id
            OR
            user2.id = conversation.owner_id
    WHERE
        user2.id != 1
        AND
        name IS NULL
        AND
        conversation_member.user_id = 1
        AND
        conversation.is_group = 0
    ) AS sub
    ORDER BY
      sub.message_id DESC
) as main
# GROUP BY
# main.conversation_id

데이터베이스 구조에 대해 자세히 알아야 할 경우:

--
-- Table structure for table `conversation`
--

CREATE TABLE `conversation` (
  `id` int(50) NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `owner_id` int(50) NOT NULL,
  `owner2_id` int(50) DEFAULT NULL,
  `is_group` int(2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `conversation`
--

INSERT INTO `conversation` (`id`, `name`, `owner_id`, `owner2_id`, `is_group`, `created_at`, `updated_at`) VALUES
(7, 'تالار گفتگوی ریاضی', 1, NULL, 1, '2021-09-13 20:33:38', NULL),
(8, NULL, 2, 1, 0, '2021-09-13 20:33:46', '2021-09-14 07:55:44'),
(9, 'گروه ازمایشی', 3, NULL, 1, '2021-09-14 07:45:04', NULL),
(10, 'پروژه ها و ایده ها', 3, NULL, 1, '2021-09-14 07:47:19', NULL),
(11, NULL, 4, 1, 0, '2021-09-14 08:05:11', NULL);

--
-- Table structure for table `conversation_member`
--

CREATE TABLE `conversation_member` (
  `id` int(50) NOT NULL,
  `conversation_id` int(50) NOT NULL,
  `user_id` int(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `conversation_member`
--

INSERT INTO `conversation_member` (`id`, `conversation_id`, `user_id`, `created_at`) VALUES
(1, 8, 1, '2021-09-14 07:02:18'),
(2, 8, 2, '2021-09-14 07:02:18'),
(3, 7, 1, '2021-09-14 07:02:28'),
(4, 7, 3, '2021-09-14 07:02:28'),
(5, 9, 3, '2021-09-14 07:45:13'),
(7, 10, 4, '2021-09-14 08:02:57'),
(8, 10, 2, '2021-09-14 08:02:57'),
(9, 10, 1, '2021-09-14 08:03:05'),
(10, 11, 4, '2021-09-14 08:05:23'),
(11, 11, 1, '2021-09-14 08:05:23'),
(12, 7, 4, '2021-09-14 09:30:04');

--
-- Table structure for table `message`
--

CREATE TABLE `message` (
  `id` int(50) NOT NULL,
  `conversation_id` int(50) NOT NULL,
  `user_id` int(50) DEFAULT NULL,
  `type` int(2) NOT NULL COMMENT '0=system,1=message,1=file,2=voice',
  `body` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `filename` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_group` int(2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `edited_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `message`
--

INSERT INTO `message` (`id`, `conversation_id`, `user_id`, `type`, `body`, `filename`, `is_group`, `created_at`, `edited_at`) VALUES
(1, 7, 1, 1, '1', NULL, 1, '2021-09-14 07:16:12', '2021-09-14 14:14:44'),
(2, 8, 1, 1, '2', NULL, 0, '2021-09-14 07:16:25', '2021-09-14 14:14:45'),
(3, 11, 1, 1, '3', NULL, 0, '2021-09-14 13:21:30', '2021-09-14 14:14:47'),
(4, 10, 1, 1, '4', NULL, 1, '2021-09-14 13:23:34', '2021-09-14 14:14:49'),
(5, 7, 1, 1, '5', NULL, 1, '2021-09-14 13:25:16', '2021-09-14 14:14:51'),
(6, 7, 1, 1, '6', NULL, 1, '2021-09-14 13:30:40', '2021-09-14 14:14:52'),
(7, 7, 1, 1, '7', NULL, 1, '2021-09-14 13:49:29', '2021-09-14 14:14:54'),
(8, 7, 1, 1, '8', NULL, 1, '2021-09-14 13:49:34', '2021-09-14 14:14:56'),
(9, 10, 1, 1, '9', NULL, 1, '2021-09-14 13:54:04', '2021-09-14 14:14:57'),
(10, 7, 1, 1, '10', NULL, 1, '2021-09-14 14:01:18', '2021-09-14 14:14:59'),
(11, 8, 1, 1, '11', NULL, 0, '2021-09-14 14:07:48', '2021-09-14 14:15:03'),
(12, 11, 4, 1, 'test-new', NULL, 0, '2021-09-14 15:11:51', NULL),
(13, 11, 1, 1, 'fdgdfg', NULL, 0, '2021-09-14 13:21:30', '2021-09-14 14:14:47'),
(14, 11, 1, 1, 'sd1f23sd1f', NULL, 0, '2021-09-14 15:33:02', NULL),
(15, 11, 1, 1, 'dfgdfgdfgdfg', NULL, 0, '2021-09-14 15:33:02', NULL),
(16, 11, 1, 1, 'dfgdfgdfg', NULL, 0, '2021-09-14 15:33:06', NULL),
(17, 11, 1, 1, 'dfg345345345', NULL, 0, '2021-09-14 15:33:06', NULL),
(18, 11, 1, 1, 'gdfg234234234', NULL, 0, '2021-09-14 15:33:17', NULL),
(19, 11, 1, 1, 'dfgda1323123f', NULL, 0, '2021-09-14 15:33:17', NULL),
(20, 11, 1, 1, '234234234234', NULL, 0, '2021-09-14 15:33:17', NULL),
(21, 11, 1, 1, '345345345345', NULL, 0, '2021-09-14 15:33:17', NULL),
(22, 11, 1, 1, '5565656', NULL, 0, '2021-09-14 15:33:17', NULL),
(23, 11, 1, 1, '7787878', NULL, 0, '2021-09-14 15:33:17', NULL),
(24, 11, 1, 1, 'یبلیبلیبلیبل', NULL, 0, '2021-09-14 15:33:28', NULL),
(25, 11, 1, 1, 'ض۳۲ث۱۲۳۴۲۳۴۲۳۴', NULL, 0, '2021-09-14 15:33:28', NULL),
(26, 11, 1, 1, '۳۴۵سیبیلبیبلب', NULL, 0, '2021-09-14 15:33:28', NULL),
(27, 11, 1, 1, 'فقفثفثقفثقف', NULL, 0, '2021-09-14 15:33:28', NULL),
(28, 11, 1, 1, '۳۳۴۲۴۲۳۴', NULL, 0, '2021-09-14 15:33:28', NULL),
(29, 11, 1, 1, '$$$$', NULL, 0, '2021-09-14 15:33:28', NULL),
(30, 11, 1, 1, '$$$%%dfgdfg', NULL, 0, '2021-09-14 15:33:47', NULL),
(31, 11, 1, 1, 'dfgdfg23423423423سیبلیبل', NULL, 0, '2021-09-14 15:33:47', NULL),
(32, 11, 1, 1, 'یبلص۴۳۵۲۳۴۵۳۴۵۳۴۵', NULL, 0, '2021-09-14 15:33:47', NULL),
(33, 11, 1, 1, 'یبل۳۵۳۴۵فثقیبلیبلیبل', NULL, 0, '2021-09-14 15:33:47', NULL),
(34, 11, 1, 1, 'یبلیلبل۳۴۵۳۴۵۳۴۵۳۴۵۳۴۵', NULL, 0, '2021-09-14 15:33:47', NULL),
(35, 11, 1, 1, '$$$$$####', NULL, 0, '2021-09-14 15:33:47', NULL);

--
-- Table structure for table `message_view`
--

CREATE TABLE `message_view` (
  `id` int(50) NOT NULL,
  `message_id` int(50) NOT NULL,
  `conversation_id` int(50) NOT NULL,
  `is_group` int(2) NOT NULL,
  `user_id` int(50) NOT NULL,
  `viewed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `session`
--

CREATE TABLE `session` (
  `id` int(50) NOT NULL,
  `user_id` int(50) NOT NULL,
  `device` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` int(10) DEFAULT NULL,
  `secret` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `session`
--

INSERT INTO `session` (`id`, `user_id`, `device`, `code`, `secret`) VALUES
(1, 1, '08ad5559-15f7-4c32-ab2d-2d8a563670a3', 0, '1'),
(2, 1, 'c8216675-08ff-4deb-9341-2afbe88bc716', NULL, '41234'),
(3, 1, 'f20279f4-615d-4b6f-83e4-0e6c201395e3', 53741, NULL),
(4, 1, 'bef6f3e2-04b7-4ff5-b948-c035c376f4d2', 51003, NULL),
(5, 1, '3492d860-6ae4-4a00-a65d-346d880c4e71', 30646, NULL),
(6, 1, 'fd0b2d35-d7aa-4e40-b362-6b592dc17aad', 28576, NULL),
(7, 1, 'c69516b4-53a8-4e69-a874-a11d859b451d', 32440, NULL),
(8, 1, '075149a4-d94c-4246-99ce-d0b71c72f26c', 99800, NULL),
(9, 1, '4804b854-89b6-4c85-8df3-815ffdd34fba', 19774, NULL),
(10, 1, 'a60195ac-2e50-42ed-9d71-1d3d04729339', 10262, NULL),
(11, 1, '80a9a2e7-ee8b-47ef-8ca4-75216721a6ac', 88424, 'bc82131e-0e59-4841-98b7-798cf65d9fcb'),
(12, 1, '6c120179-312f-4d73-9488-7f692cb54234', 42832, NULL),
(13, 1, 'a8b7ca5b-a47d-48b0-afeb-197b5ec7dc44', 39034, 'fdd51fe1-5bae-424f-9515-1a1b435faed6'),
(14, 1, '0652aa2a-01df-497d-b7b2-77fc87a29c24', 36133, '8fc206e1-1a49-444b-bcb6-ea289c17a918'),
(15, 1, '6b0bf6b9-4d32-4c6b-9e84-fd0b393acb31', 87972, '05de4b7a-cc6e-4ff0-b321-72473ac903bd'),
(16, 1, '0a523464-89ab-4f4a-803e-ca252e637e4f', 40843, 'a35a2169-949d-4043-a061-1a56ec30440e'),
(17, 1, '34b2e1bf-a088-48e8-85b8-1943db001fd5', 65916, '971a3cea-8ccc-45bf-887e-7797e4c6ab22'),
(18, 1, 'fcdb6c98-d044-43e8-a373-351f7ca1536d', 82257, 'bacb442d-e066-4117-a380-c468316d47f2'),
(19, 1, '565a036d-c4f8-46b0-8493-e5371e3dccca', 71626, '610b4e81-cf72-4091-a711-d64c601e0f0c'),
(20, 1, '8775a2d9-4544-48c4-ab5c-6d7216c955f7', 46912, 'b5eabb83-e372-4e7b-a48a-3c99eaba5d6d'),
(21, 1, 'a94f734b-0aa0-4ee9-aea3-86e2a405cf56', 44508, '2db51630-e32a-4172-90aa-99d5f7b00063'),
(22, 1, 'b0ed316b-483b-47cc-b27f-2fe6b83f410a', 87850, 'f68377a4-3ed8-4c36-8a78-1d807ed50449'),
(23, 1, '08f38458-cd12-4a9c-9c63-9c6aa291956b', 79266, 'e6314070-8401-4d7b-b9d7-a44c147c75ec'),
(24, 4, '192dfd3f-b2d7-401f-bf95-2d663b6badab', 65088, '6fa66aa9-f47d-46fd-9ce5-9645802383da'),
(25, 4, 'fc8fe94b-f220-42db-ab77-74de994f8275', 37715, '0e188502-67c2-44a9-b68c-32b07fa150ab'),
(26, 1, '27451de6-c730-450a-b76c-3ea53ff74580', 16934, '662ae741-fa42-4fc3-b0bb-5ae6c9e67b52'),
(27, 4, '6ba92f55-cffe-4d9e-b646-be9cf07e99e3', 91683, '27b85d09-831d-41cd-8032-17743a76616d');

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` int(50) NOT NULL,
  `first_name` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_code` int(2) NOT NULL,
  `phone_number` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `first_name`, `last_name`, `country_code`, `phone_number`, `created_at`, `updated_at`) VALUES
(1, 'Max', 'Base', 98, '9134458080', '2021-09-13 18:28:47', '2021-09-14 07:13:21'),
(2, 'Ali', 'Tahmasebi', 98, '91032545254', '2021-09-14 07:02:46', '2021-09-14 08:02:24'),
(3, 'B.', 'KheirKhah', 98, '9124554020', '2021-09-14 07:02:46', '2021-09-14 08:03:20'),
(4, 'H.', 'Malekian', 98, '9134550773', '2021-09-14 07:25:21', '2021-09-14 08:02:35');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `conversation`
--
ALTER TABLE `conversation`
  ADD PRIMARY KEY (`id`),
  ADD KEY `owner_id` (`owner_id`),
  ADD KEY `is_group` (`is_group`);

--
-- Indexes for table `conversation_member`
--
ALTER TABLE `conversation_member`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `message`
--
ALTER TABLE `message`
  ADD PRIMARY KEY (`id`),
  ADD KEY `is_group` (`is_group`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `conversation_id` (`conversation_id`);

--
-- Indexes for table `message_view`
--
ALTER TABLE `message_view`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `session`
--
ALTER TABLE `session`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `device` (`device`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD KEY `phone_number` (`phone_number`),
  ADD KEY `country_code` (`country_code`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `conversation`
--
ALTER TABLE `conversation`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `conversation_member`
--
ALTER TABLE `conversation_member`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `message`
--
ALTER TABLE `message`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `message_view`
--
ALTER TABLE `message_view`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `session`
--
ALTER TABLE `session`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

데이터베이스 및 테이블에 대한 자세한 내용은 다음과 같습니다.

목적conversation,conversation_member테이블: 메신저 데이터를 저장할 데이터베이스가 있습니다.두 가지 모델 대화가 있습니다.

• 1: 그룹: 여러 명
• 2: 퍼스널채팅: 사용자가 다른 사용자에게 (그 때문에)owner2_id를 누릅니다).

그리고 주요 질문은 개인 채팅과 그룹 채팅을 병합하는 것입니다.ID = 1:

(
    SELECT
        sub1.*
    FROM
    (
        SELECT
            conversation.id AS conversation_id,
            conversation.name AS conversation_name,
            conversation.is_group AS conversation_isgroup,
            conversation.owner_id AS conversation_owner_id,

            message.id AS message_id,
            message.type AS message_type,
            message.body AS message_body,
            message.filename AS message_filename,
            message.created_at AS message_time,

            message.user_id AS message_user_id,
            CONCAT(user.first_name, " ", user.last_name) AS message_user_name
        FROM
            conversation
        INNER JOIN
            conversation_member
            ON
                conversation_member.conversation_id = conversation.id
        LEFT JOIN
            message
            ON
                message.conversation_id = conversation.id
        LEFT JOIN
            user
            ON
                user.id = message.user_id
        WHERE
            conversation_member.user_id = 1
            AND
            conversation.is_group = 1
    ) AS sub1
    GROUP BY sub1.message_id desc
)
UNION
(
    SELECT
        sub2.*
    FROM
    (
        SELECT
            conversation.id AS conversation_id,
            CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
            conversation.is_group AS conversation_isgroup,
            (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,

            message.id AS message_id,
            message.type AS message_type,
            message.body AS message_body,
            message.filename AS message_filename,
            message.created_at AS message_time,

            message.user_id AS message_user_id,
            CONCAT(user.first_name, " ", user.last_name) AS message_user_name
        FROM
            conversation
        INNER JOIN
            conversation_member
            ON
                conversation_member.conversation_id = conversation.id
        LEFT JOIN
            message
            ON
                message.conversation_id = conversation.id
        LEFT JOIN
            user
            ON
                user.id = message.user_id
        LEFT JOIN
            user as user2
            ON
                user2.id = conversation.owner2_id
                OR
                user2.id = conversation.owner_id
        WHERE
            user2.id != 1
            AND
            name IS NULL
            AND
            conversation_member.user_id = 1
            AND
            conversation.is_group = 0
    ) AS sub2
    GROUP BY sub2.message_id desc
);

하고 있는 것

나는 모든 그룹 대화와 개인적인 대화와 그 방의 마지막 메시지 목록을 보고 마지막 MESSAGE_TIME까지 모든 방을 분류하고 싶다.

이것은 거의 모든 메신저에게 일어나는 일입니다.

당신의 질문에 대한 질의는 어떻게 하는지 보여주기 위해서만 사용하겠습니다.MariaDB그 주문은 서브쿼리로 처리하다.

나는 너의 것을 바꿨다.user와 식사하다.user01테이블이 있었기 때문에user내 안에서MariaDB데이터베이스입니다.

SELECT
  main.*
FROM
(
    SELECT
        sub.*
    FROM
    (
        SELECT
        conversation.id AS conversation_id,
        CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
        conversation.is_group AS conversation_isgroup,
        (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
    
        message.id AS message_id,
        message.type AS message_type,
        message.body AS message_body,
        message.filename AS message_filename,
        message.created_at AS message_time,

        message.user_id AS message_user_id,
        CONCAT(user01.first_name, " ", user01.last_name) AS message_user_name
    FROM
        conversation
    INNER JOIN
        conversation_member
        ON
            conversation_member.conversation_id = conversation.id
    LEFT JOIN
        message
        ON
            message.conversation_id = conversation.id
    LEFT JOIN
        user01
        ON
            user01.id = message.user_id
    LEFT JOIN
        user01 as user2
        ON
            user2.id = conversation.owner2_id
            OR
            user2.id = conversation.owner_id
    WHERE
        user2.id != 1
        AND
        name IS NULL
        AND
        conversation_member.user_id = 1
        AND
        conversation.is_group = 0
    ) AS sub
    ORDER BY
      sub.message_id DESC limit 10 
) as main
 GROUP BY
 main.conversation_id;

추가만 했어요.limit 10에sub.message_id DESC limit 10 .

SQL 표준에 따르면 "table"(및 FROM 절의 하위 쿼리)은 정렬되지 않은 행 집합입니다.테이블의 행(또는 FROM 절의 하위 쿼리)은 특정 순서로 정렬되지 않습니다.따라서 옵티마이저는 사용자가 지정한 ORDER BY 절을 무시할 수 있습니다.실제로 SQL 표준에서는 ORDER BY 절을 이 서브쿼리에 표시할 수 없습니다(ORDER BY가 ...이기 때문에 허용됩니다).LIMIT ... 결과, 행 집합, 순서뿐만 아니라 행 집합도 변경합니다.)

FROM 절의 서브쿼리를 미지정 및 정의되지 않은 순서로 행 세트로 취급하고 ORDER BY를 최상위 SELECT에 배치해야 합니다.

https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

참고로 SQL의 마스터는 아니지만, 업무 시간 후에 쿼리 하나를 해결하는 데 성공했을 뿐입니다.

이 목적을 위해 더 나은 질문이 있으면 게시하고 답변해 주십시오.

이것은 하나의 쿼리에 대해서만 2개의 쿼리가 있으며 UNION과 Marge가 필요합니다.

단일 쿼리:

SELECT
    main.*
FROM
(
    SELECT
        sub.*
    FROM
    (
        SELECT
        conversation.id AS conversation_id,
        CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
        conversation.is_group AS conversation_isgroup,
        (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
        
        message.id AS message_id,
        message.type AS message_type,
        message.body AS message_body,
        message.filename AS message_filename,
        message.created_at AS message_time,

        message.user_id AS message_user_id,
        CONCAT(user.first_name, " ", user.last_name) AS message_user_name
    FROM
        conversation
    INNER JOIN
        conversation_member
        ON
            conversation_member.conversation_id = conversation.id
    LEFT JOIN
        message
        ON
            message.conversation_id = conversation.id
    LEFT JOIN
        user
        ON
            user.id = message.user_id
    LEFT JOIN
        user as user2
        ON
            user2.id = conversation.owner2_id
            OR
            user2.id = conversation.owner_id
    WHERE
        user2.id != 1
        AND
        name IS NULL
        AND
        conversation_member.user_id = 1
        AND
        conversation.is_group = 0
    ) AS sub
    GROUP BY sub.message_id desc
) as main
GROUP BY main.conversation_id

및 " " "UNION과를병 병합: :

(
    SELECT
        mm.*
    FROM
    (
        (
            SELECT
                sub1.*
            FROM
            (
                SELECT
                    conversation.id AS conversation_id,
                    conversation.name AS conversation_name,
                    conversation.is_group AS conversation_isgroup,
                    conversation.owner_id AS conversation_owner_id,

                    message.id AS message_id,
                    message.type AS message_type,
                    message.body AS message_body,
                    message.filename AS message_filename,
                    message.created_at AS message_time,

                    message.user_id AS message_user_id,
                    CONCAT(user.first_name, " ", user.last_name) AS message_user_name
                FROM
                    conversation
                INNER JOIN
                    conversation_member
                    ON
                        conversation_member.conversation_id = conversation.id
                LEFT JOIN
                    message
                    ON
                        message.conversation_id = conversation.id
                LEFT JOIN
                    user
                    ON
                        user.id = message.user_id
                WHERE
                    conversation_member.user_id = 1
                    AND
                    conversation.is_group = 1
            ) AS sub1
            GROUP BY sub1.message_id desc
        )
        UNION
        (
            SELECT
                sub2.*
            FROM
            (
                SELECT
                    conversation.id AS conversation_id,
                    CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
                    conversation.is_group AS conversation_isgroup,
                    (SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,

                    message.id AS message_id,
                    message.type AS message_type,
                    message.body AS message_body,
                    message.filename AS message_filename,
                    message.created_at AS message_time,

                    message.user_id AS message_user_id,
                    CONCAT(user.first_name, " ", user.last_name) AS message_user_name
                FROM
                    conversation
                INNER JOIN
                    conversation_member
                    ON
                        conversation_member.conversation_id = conversation.id
                LEFT JOIN
                    message
                    ON
                        message.conversation_id = conversation.id
                LEFT JOIN
                    user
                    ON
                        user.id = message.user_id
                LEFT JOIN
                    user as user2
                    ON
                        user2.id = conversation.owner2_id
                        OR
                        user2.id = conversation.owner_id
                WHERE
                    user2.id != 1
                    AND
                    name IS NULL
                    AND
                    conversation_member.user_id = 1
                    AND
                    conversation.is_group = 0
            ) AS sub2
            GROUP BY sub2.message_id desc
        )
    ) AS mm
    GROUP BY mm.conversation_id desc
)
;

쿼리가 최적화되어 있는지, 좋은지 잘 모르겠습니다만, 이것은 유효합니다.이것이 불충분하면 검토 후 코멘트를 주세요.

언급URL : https://stackoverflow.com/questions/69180293/mysql-mariadb-problem-order-by-desc-before-group-by