mysql order by null last_order by 语句出现null的情况
时间: 2023-08-09 21:05:35 浏览: 140
当使用 `ORDER BY` 对某一列进行排序时,如果这一列中存在 `NULL` 值,那么默认情况下 `NULL` 值会被排在结果集的最前面。但有时候我们需要把 `NULL` 值排在最后面,这时可以使用以下两种方式:
1. 使用 `DESC` 关键字对该列进行倒序排序,这样 `NULL` 值就会被排在最后面。例如:
```sql
SELECT * FROM my_table ORDER BY my_column DESC;
```
2. 使用 `ORDER BY` 和 `CASE` 语句结合,对包含 `NULL` 的列进行特殊处理。例如:
```sql
SELECT * FROM my_table ORDER BY CASE WHEN my_column IS NULL THEN 1 ELSE 0 END, my_column;
```
上述语句中,首先对 `my_column` 列进行 `CASE` 语句判断,如果该列为 `NULL`,则返回值为 `1`,否则为 `0`。然后按照返回值进行排序,即先排 `0`,再排 `1`,最后再按照 `my_column` 排序。这样就可以将 `NULL` 值排在最后面了。
相关问题
druid oracle sql语句转mysql
很抱歉,Druid不能直接将Oracle SQL语句转换为MySQL语句,因为Oracle和MySQL的语法有很大的区别。但是,你可以手动将Oracle SQL语句转换为MySQL语句,或者使用一些第三方工具进行转换。
以下是一些常见的Oracle SQL语句与MySQL语句的对比:
1. 创建表:
Oracle SQL语句:
```
CREATE TABLE employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4)
);
```
MySQL语句:
```
CREATE TABLE employees (
employee_id INT(6),
first_name VARCHAR(20),
last_name VARCHAR(25),
email VARCHAR(25),
hire_date DATE,
job_id VARCHAR(10),
salary DECIMAL(8,2),
commission_pct DECIMAL(2,2),
manager_id INT(6),
department_id INT(4)
);
```
2. 插入数据:
Oracle SQL语句:
```
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (100, 'Steven', 'King', 'steven.king@example.com', '17-JUN-87', 'AD_PRES', 24000, NULL, NULL, 90);
```
MySQL语句:
```
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (100, 'Steven', 'King', 'steven.king@example.com', '1987-06-17', 'AD_PRES', 24000, NULL, NULL, 90);
```
3. 查询数据:
Oracle SQL语句:
```
SELECT employee_id, first_name, last_name, email, hire_date, job_id, salary, commission_pct, manager_id, department_id
FROM employees
WHERE department_id = 90
ORDER BY hire_date DESC;
```
MySQL语句:
```
SELECT employee_id, first_name, last_name, email, hire_date, job_id, salary, commission_pct, manager_id, department_id
FROM employees
WHERE department_id = 90
ORDER BY hire_date DESC;
```
需要注意的是,Oracle和MySQL的数据类型有很大的区别,因此在进行数据类型转换时需要注意精度和类型的匹配问题。
帐号表结构如下: CREATE TABLE `t_account` ( `account_id` int(10) unsigned NOT NULL auto_increment COMMENT '所属帐号id', `account_name` varchar(50) NOT NULL COMMENT '帐号名', `password` varchar(16) NOT NULL COMMENT '密码', `last_login_time` timestamp NOT NULL default '1979-01-01 00:00:00' COMMENT '最后登录时间', `point` int(10) unsigned NOT NULL default '0' COMMENT '积分', PRIMARY KEY USING BTREE (`account_id`), ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8; CREATE TABLE `t_account_nickname` ( `account_id` int(10) unsigned NOT NULL COMMENT '所属帐号id', `account_nickname `varchar(50) NOT NULL COMMENT'昵称', PRIMARY KEY USING BTREE (`account_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 请写出SQL语句(温馨提示:列点作答,字体端正,代码细节与规范) 1.查询最近1000个登录的帐号(mysql) 2.查询如下结果:第一列帐号ID,第二列帐号名,第三列昵称,第四列积分 3.对帐号名里包含rock的帐号,积分修改为0 4.查询出重复的昵称
1. 查询最近1000个登录的帐号(mysql)
```
SELECT account_id, account_name, last_login_time, point
FROM t_account
ORDER BY last_login_time DESC
LIMIT 1000;
```
2. 查询如下结果:第一列帐号ID,第二列帐号名,第三列昵称,第四列积分
```
SELECT t1.account_id, t1.account_name, t2.account_nickname, t1.point
FROM t_account AS t1
LEFT JOIN t_account_nickname AS t2 ON t1.account_id = t2.account_id;
```
3. 对帐号名里包含rock的帐号,积分修改为0
```
UPDATE t_account
SET point = 0
WHERE account_name LIKE '%rock%';
```
4. 查询出重复的昵称
```
SELECT account_nickname, COUNT(*) AS num
FROM t_account_nickname
GROUP BY account_nickname
HAVING num > 1;
```