确认

  • [X] 我的版本是最新版本, 我的版本号与 version 相同, 并且项目里无依赖冲突
  • [X] 我已经在 issue 中搜索过, 确认问题没有被提出过
  • [X] 我已经修改标题, 将标题中的 描述 替换为遇到的问题

当前程序版本

3.5.7

问题描述

    select distinct r.role_id,
                    r.role_name,
                    r.role_key,
                    r.role_sort,
                    r.data_scope,
                    r.menu_check_strictly,
                    r.dept_check_strictly,
                    r.status,
                    r.del_flag,
                    r.create_time,
                    r.remark
    from sys_role r
             left join sys_user_role sur on sur.role_id = r.role_id
             left join sys_user u on u.user_id = sur.user_id
             left join sys_dept d on u.dept_id = d.dept_id
查询页码时，数据正常，查询分页数据时，实际数量多于理论数量。因为 __row_number__ DISTINCT没起作用。日志如下

SELECT COUNT(*) FROM (SELECT DISTINCT r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.menu_check_strictly, r.dept_check_strictly, r.status, r.del_flag, r.create_time, r.remark FROM sys_role r LEFT JOIN sys_user_role sur ON sur.role_id = r.role_id LEFT JOIN sys_user u ON u.user_id = sur.user_id LEFT JOIN sys_dept d ON u.dept_id = d.dept_id WHERE (r.del_flag = '0')) TOTAL

WITH selectTemp AS (SELECT DISTINCT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY r.role_sort ASC, r.create_time ASC) as row_number, r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.menu_check_strictly, r.dept_check_strictly, r.status, r.del_flag, r.create_time, r.remark FROM sys_role r LEFT JOIN sys_user_role sur ON sur.role_id = r.role_id LEFT JOIN sys_user u ON u.user_id = sur.user_id LEFT JOIN sys_dept d ON u.dept_id = d.dept_id WHERE (r.del_flag = '0') ORDER BY r.role_sort ASC, r.create_time ASC) SELECT * FROM selectTemp WHERE row_number BETWEEN 1 AND 10 ORDER BY row_number

详细堆栈日志

SELECT COUNT(*) FROM (SELECT DISTINCT r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.menu_check_strictly, r.dept_check_strictly, r.status, r.del_flag, r.create_time, r.remark FROM sys_role r LEFT JOIN sys_user_role sur ON sur.role_id = r.role_id LEFT JOIN sys_user u ON u.user_id = sur.user_id LEFT JOIN sys_dept d ON u.dept_id = d.dept_id WHERE (r.del_flag = '0')) TOTAL

 WITH selectTemp AS (SELECT DISTINCT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY r.role_sort ASC, r.create_time ASC) as __row_number__, r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.menu_check_strictly, r.dept_check_strictly, r.status, r.del_flag, r.create_time, r.remark FROM sys_role r LEFT JOIN sys_user_role sur ON sur.role_id = r.role_id LEFT JOIN sys_user u ON u.user_id = sur.user_id LEFT JOIN sys_dept d ON u.dept_id = d.dept_id WHERE (r.del_flag = '0') ORDER BY r.role_sort ASC, r.create_time ASC) SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 10 ORDER BY __row_number__