Mysql 从表中 Select 具有不同顺序的列

发布于09月14日

我有一位餐桌客人.该表具有列cust_id、member、date.Cust_id是主键(自动递增)，成员可以是1或0，Date包含时间戳.我想在MySQL上运行一个查询，它允许我 Select 成员=1、按日期排序的cust_id，然后 Select 成员=0、按cust_id排序的cust_id.两者应按降序排列(表示从最新或最大到最旧或最小).我已经try 了UNION，但是SQL给出了错误，因为列DATE不存在.

SELECT `cust_id` FROM `customer` WHERE `member` = 1 ORDER BY `date` DESC
SELECT `cust_id` FROM `customer` WHERE `member` = 0 ORDER BY `cust_id` DESC

我想合并这两个查询的结果.

我期待这样的输出:

cust_id	member	date
25	1	2022-05-01 22:22:22
30	1	2021-01-11 05:23:11
50	0	2023-09-01 22:22:22
49	0	2023-08-21 20:20:20
46	0	2023-08-20 19:20:21

但我想我遗漏了什么.

请帮帮忙.

推荐答案

您可以在MySQL中使用参数来使其正常工作

CREATE tABLe customer ( `cust_id` int,`member` int,`date` datetime)

INSERT INTO customer
    (`cust_id`, `member`, `date`)
VALUES
    (25, 1, '2022-05-01 22:22:22'),
    (30, 1, '2021-01-11 05:23:11'),
      (31, 1, '2022-05-01 21:22:22'),
    (32, 1, '2021-01-11 06:23:11'),
    (50, 0, '2023-09-01 22:22:22'),
    (49, 0, '2023-08-21 20:20:20'),
    (46, 0, '2023-08-20 19:20:21'),
      (50, 0, '2023-09-01 22:22:22'),
    (51, 0, '2023-08-21 20:20:20'),
    (52, 0, '2023-08-20 19:20:21')
;

Records: 10  Duplicates: 0  Warnings: 0

(SELECT `cust_id`,`member`,`date` FROM `customer` WHERE `member` = 1 ORDER BY `date` DESC LIMIT 10000000)
UNION 
(SELECT `cust_id`,`member`,`date` FROM `customer` WHERE `member` = 0 ORDER BY `cust_id` DESC  LIMIT 10000000)

cust_id	member	date
25	1	2022-05-01 22:22:22
31	1	2022-05-01 21:22:22
32	1	2021-01-11 06:23:11
30	1	2021-01-11 05:23:11
52	0	2023-08-20 19:20:21
51	0	2023-08-21 20:20:20
50	0	2023-09-01 22:22:22
49	0	2023-08-21 20:20:20
46	0	2023-08-20 19:20:21