SELECT * FROM emm_task.tbl_scm_passenger ORDER by CASE WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN 0 ELSE 1 END, CASE when phone id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) then phone end asc NULLS LAST这段SQL错误怎么修改
时间: 2023-08-01 16:09:14 浏览: 35
这段 SQL 语句中的问题在于第二个 CASE 表达式中的语法错误,正确的语法应该是:
```
SELECT *
FROM emm_task.tbl_scm_passenger
ORDER BY
CASE
WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN 0
ELSE 1
END,
CASE
WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN phone
ELSE NULL
END ASC NULLS LAST;
```
通过上述修改,第二个 CASE 表达式中添加了一个 ELSE NULL 来解决语法错误,并且将其放到了正确的位置上,以保证整个语句的正确性。
相关问题
SELECT * FROM emm_task.tbl_scm_passenger ORDER BY CASE WHEN id in (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982) THEN 0 ELSE 1,这一部分数据按照phone正序排序,其余数据按照时间降序
您的问题是一条 SQL 查询语句,它会从 `emm_task.tbl_scm_passenger` 表中查询数据,并按照以下条件排序:
1. 如果 `id` 字段的值是 53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,则排序值为 0。
2. 如果 `id` 字段的值不是上述值,则排序值为 1。
3. 对于排序值为 1 的数据,按照 `phone` 字段正序排序。
4. 对于排序值为 2 的数据,按照时间降序排序。
具体的 SQL 查询语句如下:
```
SELECT *
FROM emm_task.tbl_scm_passenger
ORDER BY
CASE
WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982) THEN 0
ELSE 1
END,
CASE
WHEN id NOT IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982) THEN phone
END ASC,
CASE
WHEN id NOT IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982) THEN created_at
END DESC
```
SELECT * FROM emm_task.tbl_scm_passenger ORDER BY CASE WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN 0 ELSE 1 END,修改上述SQL,满足在指定集合中按照字段phone正序排序phone空值排在后面排序
你已经给出了一个带有排序规则的 SQL 语句,但是这个 SQL 语句中排序规则并不符合你现在的要求。为了满足你的要求,我们需要对这个 SQL 语句进行修改。修改后的 SQL 语句如下:
```
SELECT * FROM emm_task.tbl_scm_passenger
ORDER BY
CASE
WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989)
THEN 0
ELSE 1
END,
CASE
WHEN phone IS NULL
THEN 1
ELSE 0
END,
phone ASC;
```
这个 SQL 语句中,我们增加了一个 `CASE WHEN` 表达式,用于判断 `phone` 字段是否为空。如果为空,则将这些记录排在后面,否则排在前面。同时,我们将原来的排序规则保留,依次按照 id 字段和 phone 字段进行排序。其中,id 在指定集合中的记录会排在前面,其余记录会排在后面。最后,对于排在前面的记录,不做任何排序;对于排在后面的记录,按照 phone 字段的值正序排序。