下面那个效率高 一 SELECT a.user_id as userId, b.user_name as userName, a.nick_name as nickName, b.access_time as accessTime FROM sys_user a INNER JOIN (SELECT user_name , min(access_time) as access_ti
时间: 2024-04-28 21:27:20 浏览: 5
我无法判断哪个效率更高,因为你截断了 SQL 语句。但是可以看出第一个 SQL 语句使用了 LEFT JOIN,而第二个 SQL 语句使用了 INNER JOIN。一般来说,INNER JOIN 的效率会高于 LEFT JOIN,因为 INNER JOIN 只会返回两个表中有匹配关系的行,而 LEFT JOIN 则会返回左表中的所有行和右表中匹配的行。但是具体哪个更高效,还需要看具体情况。
相关问题
SELECT DISTINCT rror.recyclebin_id AS recyclebinId, rdt.name AS recyclebinName, sur.phonenumber AS phone, sur.nick_name AS nickName FROM re_order ror INNER JOIN re_recyclebin_order rror ON rror.id = ror.recyclebin_order_id INNER JOIN re_digit_recyclebin rdt ON rdt.id = rror.recyclebin_id left join sys_user sur ON rdt.admin_id = sur.user_id WHERE ror.order_status = '50' AND order_user = #{userId} Order By ror.complete_time DESC 影响了顺序,帮我改一下
可以将 Order By 子句放在整个 SQL 语句的最后,如下所示:
SELECT DISTINCT
rror.recyclebin_id AS recyclebinId,
rdt.name AS recyclebinName,
sur.phonenumber AS phone,
sur.nick_name AS nickName
FROM
re_order ror
INNER JOIN re_recyclebin_order rror ON rror.id = ror.recyclebin_order_id
INNER JOIN re_digit_recyclebin rdt ON rdt.id = rror.recyclebin_id
LEFT JOIN sys_user sur ON rdt.admin_id = sur.user_id
WHERE
ror.order_status = '50'
AND order_user = #{userId}
ORDER BY
ror.complete_time DESC
SELECT DISTINCT p.*, u1.id AS directorId, CASE WHEN up1.member_type = 348 THEN u1.nickname ELSE up1.member_name END AS directorName FROM project p LEFT JOIN userproject up1 ON p.p_id=up1.p_id AND up1.director=1 LEFT JOIN sys_user u1 ON up1.member_id = u1.id LEFT JOIN userproject up ON p.p_id=up.p_id LEFT JOIN sys_user u ON up.member_id = u.id LEFT JOIN sys_user_department d ON d.user_id = u.id WHERE (p.enable = ? AND p.audit_status = ? AND p.organization_id = ? AND p.project_name IS NOT NULL) GROUP BY p.project_name,p.project_code,p.project_nature ORDER BY p.p_id DESC报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'spms.p.p_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by怎么处理
这是因为 MySQL 的 sql_mode 设置了 only_full_group_by,导致在 GROUP BY 子句中未包含的列不能在 SELECT 子句中使用。可以尝试以下两种处理方式:
1. 修改 sql_mode
将 MySQL 的 sql_mode 修改为不包含 only_full_group_by 这个选项,可以在 MySQL 配置文件 my.cnf 或者 my.ini 中找到 sql_mode 选项,将其修改为:
```
sql_mode=NO_ENGINE_SUBSTITUTION
```
然后重启 MySQL 服务即可生效。
2. 修改查询语句
在 SELECT 子句中将非聚合列 p.p_id 改为聚合列,例如使用 MAX(p.p_id)。修改后的查询语句如下:
```
SELECT DISTINCT MAX(p.p_id) AS p_id, p.project_name, p.project_code, p.project_nature, u1.id AS directorId, CASE WHEN up1.member_type = 348 THEN u1.nickname ELSE up1.member_name END AS directorName
FROM project p
LEFT JOIN userproject up1 ON p.p_id=up1.p_id AND up1.director=1
LEFT JOIN sys_user u1 ON up1.member_id = u1.id
LEFT JOIN userproject up ON p.p_id=up.p_id
LEFT JOIN sys_user u ON up.member_id = u.id
LEFT JOIN sys_user_department d ON d.user_id = u.id
WHERE (p.enable = ? AND p.audit_status = ? AND p.organization_id = ? AND p.project_name IS NOT NULL)
GROUP BY p.project_name,p.project_code,p.project_nature
ORDER BY p.p_id DESC
```