MySQL - 根据不同的条件忽略某些行 [英] MySQL - Ignore certain rows depending on different conditions

问题描述

举个例子，我有一个 Pokemon 列，里面有某些数据，其中许多是重复的

As an example I have a Pokemon column with certain data inside, many of them repeat

宝可梦

Wartotle
Pichu
Pikachu
Pichu
Kadabra
Charmelon
Squirtle
Wartotle
Wartotle
Pidgeotto
Pidgeotto
Diglett

我需要的是忽略某些数据，如果同一 column 上的特定数据不存在.在我通过 SELECT

What I need is to ignore certain data, if a specific data on the same column doesn't exists. Specific data from the pokemon column must exists before I fetch data via SELECT

我想要的是对多个数据执行类似操作的查询

What I want is something is a query that do something like this but on multiple data

SELECT * FROM table 
(
    If `Pichu` doesn't exists then don't `SELECT` `Pikachu`
    If `Abra` doesn't exists then don't `SELECT` `Kadabra`
    If `Squirtle` doesn't exists then don't `SELECT` `Wartotle`
    If `Pidgety` doesn't exists then don't `SELECT` `Pidgeotto`
    If `Squirtle` doesn't exists then don't `SELECT` `Wartotle`
    If `Charmander` doesn't exists then don't `SELECT` `Charmeleon`
)

所以我需要的 SELECT 查询的最终结果将导致某些内容

So the final result of the SELECT query I need would result in something in this

Wartotle
Pichu
Pikachu
Pichu
Squirtle
Wartotle
Wartotle
Diglett

我知道这是一个有点令人困惑的请求，但它是我正在寻找的查询

I know it's a bit confusing request but it's the query I'm looking for

推荐答案

你可以先找到那些你不想包含的(子查询)，然后从结果中排除那些(外查询):

You could first find those that you do not want to include (subquery) and exclude those from the result (outer query):

select t.name
from table1 t
where t.name not in (
  select s.skip
  from (
    select 'Pichu' as search, 'Pikachu' as 'skip'
    union
    select 'Abra', 'Kadabra'
    union
    select 'Squirtle', 'Wartotle'
    union
    select 'Pidgety', 'Pidgeotto'
    union
    select 'Squirtle', 'Wartotle'
    union
    select 'Charmander', 'Charmeleon'
  ) as s 
    left join table1 t1 on t1.name=s.search
  where t1.name is null
);

顺便说一句，根据您的规则，Charmelon 应该包含在结果集中.

Btw, based on your rules, the Charmelon should be included in the result set.

参见 dbfiddle

这篇关于MySQL - 根据不同的条件忽略某些行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！