Mysql 过滤查询结果

发布于10月14日

此查询从数据库MariaDB(MySQL)返回用户. 我需要从Result中删除行，其中在Surname中添加了"(Old)"或"-R". 如何编辑查询？

SELECT au.id,
       au.name,
       au.surname
FROM statements s 
JOIN aduser au ON au.id=s.id_usp
WHERE s.id_utv = 10
GROUP BY au.surname
ORDER BY au.surname ASC

根据该查询结果:

id	name	surname
124	Mike	Argle
221	Mike	Argle (old)
138	Lisa	Doe
126	Lisa	Doe (old)
123	John	Harris
135	John	Harris - R
324	Ann	Perez
329	Tiffani	Perez

对这一结果.查询应返回以下结果:

id	name	surname
124	Mike	Argle
138	Lisa	Doe
123	John	Harris
324	Ann	Perez
329	Tiffani	Perez

推荐答案

您可以使用ROW_NUMBER窗口函数从name + ' - R'/' (old)'个加倍的值中 Select 第一个值.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(
                  PARTITION BY name, surname REGEXP name+' (- R|(old))'  
                  ORDER BY surname) AS rn
    FROM tab
)
SELECT id, name, surname
FROM cte
WHERE rn = 1

查看演示here.

您的查询将按如下方式更新:

WITH cte AS (
    SELECT au.id,
           au.name,
           au.surname,
           ROW_NUMBER() OVER(
               PARTITION BY au.name, au.surname REGEXP au.name+' (- R|(old))'  
               ORDER BY au.surname) AS rn
    FROM statements s 
    JOIN aduser au ON au.id=s.id_usp
    WHERE s.id_utv = 10
)
SELECT id, name, surname
FROM cte
WHERE rn = 1
ORDER BY surname ASC