mysql如何查找交叉引用表中至少有一行为空或条件 [英] mysql how to find if at least one row from cross reference table is null or criteria

问题描述

我在使用mysql时遇到问题，我找不到解决方法，也许我不知道mysql的好关键字

i have trouble with mysql, i dont find the way to do it maybe i dont know the good mysql keyword

mysql5

+----------+------------+----------+
| ID       | FOREIGNKEY |  TRAINER |
+----------+------------+----------+
|      ... | ...        | ...      |
|      475 | 254        |  NULL    |
|      476 | 254        |  NULL    |
|      477 | 254        |  NULL    |
|      478 | 286        |  NULL    |
|      479 | 286        |  FREE    |
|      480 | 286        |  FREE    |
|      481 | 401        |  FREE    |
|      482 | 401        |  1       |
|      483 | 401        |  FREE    |
|      484 | 405        |  NULL   |
|      485 | 405        |  1       |
|      486 | 405        |  5       |
|      487 | 405        |  FREE    |
|      488 | 406        |  1       |
|      489 | 406        |  5       |
|      490 | 406        |  5       |
|      491 | 406        |  2       |
|      ... | ...        |  ...     |
+----------+------------+----------+

预期结果

约束:

我想获取并非所有培训师都为NULL或FREE(至少1，但可以为2或更多)，但至少一个应为NULL的所有外键ID.

i would like to get all the foreignkey id that have not all trainer NULL or FREE (at least 1 but can be 2 or more) but at least one should be NULL

+------------+-------+
|    ID_TR   | FIELD |
+------------+-------+
|      405   |   ..  |
+------------+-------+

我不知道如何在mysql中执行此操作? 然后分组安排一名教练== FREE或NULL吗?

i dont know how to do it in mysql ? Group then HAVING one trainer == FREE OR NULL ?

感谢您的帮助

推荐答案

这听起来像EXISTS运算符的经典用例:

This sounds like a classic usecase for the EXISTS operator:

SELECT *
FROM   mytable a
WHERE  EXISTS (SELECT 1
               FROM   mytable b
               WHERE  a.foreignkey = b.foreignkey 
               AND    trainer IS NOT NULL 
               AND    trainer <> 'FREE'

如果您只想使用截然不同的foreignkey s:

If you just just want the distinct different foreignkeys:

SELECT DISTINCT foreignkey
FROM   mytable a
WHERE  EXISTS (SELECT 1
               FROM   mytable b
               WHERE  a.foreignkey = b.foreignkey 
               AND    trainer IS NOT NULL 
               AND    trainer <> 'FREE'

这篇关于mysql如何查找交叉引用表中至少有一行为空或条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！