sqlite 合并三个SQL查询，并保持查询顺序不重复

t5fffqht 于 2022-12-13 发布在 SQLite

关注(0)|答案(2)|浏览(253)

我有三个疑问如下：
select * from orders where full_name like 'Mohammed Ali%' order by g_date desc
这将给出所有以Mohammed Ali开头的名字（10个结果）
select * from orders where full_name like '%Mohammed Ali%' order by g_date desc
所有名字都包含“Mohammed Ali”（20个结果）
select * from orders where full_name like '%Mohammed%Ali%' order by g_date desc
所有名字都有Mohammed和Ali（100个结果）
我希望得到所有三个查询的结果，但优先级为第一个查询，然后第二个，最后，最后一个。我不希望重复的结果。
我首先执行了以下操作：

select * from (
select * from orders where full_name like 'Mohammed Ali%'order by g_date desc)
union
select * from (
select * from orders where full_name like '%Mohammed Ali%'order by g_date desc)
union
select * from (select * from orders where full_name like '%Mohammed%Ali%'order by g_date desc)

但是，我从三个查询中得到了混合的结果：（。没有重复-（100个结果）太好了！
然后，我尝试了以下方法：

select * from (
select *,0 as ord from orders where full_name like 'Mohammed Ali%')
union
select * from (
select *,1 as ord from orders where full_name like '%Mohammed Ali%')
union
select *,2 as ord from (select * from orders where full_name like '%Mohammed%Ali%')
order by ord,g_date desc

第一个问题被修复了（很好）。但是，现在我有重复的结果（10 + 20 + 100）
如何获得没有重复项的排序结果？

sqlite

来源：https://stackoverflow.com/questions/74717981/combine-three-sql-queries-with-keeping-orders-of-the-queries-without-duplicates

2条答案

按热度按时间

oipij1gg1#

WHERE子句中唯一需要的条件是full_name LIKE '%Mohammed%Ali%'。
在ORDER BY子句中，可以按布尔表达式/条件对返回的行进行排序：

SELECT * 
FROM orders 
WHERE full_name LIKE '%Mohammed%Ali%'
ORDER BY full_name LIKE 'Mohammed Ali%' DESC, -- first all names starting with 'Mohammed Ali'
         full_name LIKE '%Mohammed Ali%' DESC; -- then all names containing 'Mohammed Ali'
-- all the other names will be at the bottom of the resultset

赞(0）回复(0）举报 2022-12-13

s4n0splo2#

在......时尝试简单的情况

with orders (full_name)
as
(
select 'Mohammed Ali'
Union all select 'hMohammed Ali'
Union all select 'Mohammed Ali'
Union all select 'fMohammed Ali'
Union all select 'hMohammed fAlia'
)
select 
    *,
    case when full_name like 'Mohammed Ali%' then 1
    when full_name like '%Mohammed Ali%' then 2
    when full_name like '%Mohammed%Ali%' then 3
    end as ord
from 
    (
        select * from orders where full_name like '%Mohammed%Ali%'
    ) x
order by ord

赞(0）回复(0）举报 2022-12-13

我来回答

sqlite 合并三个SQL查询，并保持查询顺序不重复

2条答案

相关问题

热门标签

最新问答