mysql - 如何在左连接中返回空值，但在返回没有空值的特定值时排除它

Question

所以我有这张表:

history_table

+------------+----------+----------+--------+---------+--------------+--------------+------+---------+
| history_id | TDNO     | titleNO  | lotNO  | area    | encumbrances | assess_value | EFF  | memo_id |
+------------+----------+----------+--------+---------+--------------+--------------+------+---------+
|        145 | F-111111 | T-00000  | LOTF1  | 500 SQM | NONE         |     12331.13 | 2016 | 415     |
|        146 | F-000000 | T-000000 | LOT F0 | 1 HA    | NONE         |       123.23 | 2015 | 412     |
|        147 | E-000000 | T-00000  | LOTE0  | 500 SQM | NONE         |     13123.12 | 1994 | 413     |
+------------+----------+----------+--------+---------+--------------+--------------+------+---------+

memoranda

+---------+--------------+
| memo_id | memo_string  |
+---------+--------------+
|       0 |              |
|     412 | TEST.        |
|     413 | TEST2.       |
|     415 | Test3.       |
+---------+--------------+

td_ownersinfo

  TD_NO     owner           location  transaction  
  --------  --------------  --------  -------------
  F-000000  name1           SAN JOSE  TRANSFER     
  F-111111  name2           LAS VEGAS WALK-IN 

所以我有这个查询:

SELECT 
  history_table.`history_id`,
  history_table.`TDNO` AS 'TAX DECLARATION NO',
  history_table.`titleNO` AS 'TITLE NO',
  history_table.`lotNO` AS 'LOT NO',
  history_table.`area` AS 'AREA',
  history_table.`encumbrances` AS 'ENCUMBRANCES',
  FORMAT(history_table.`assess_value`, 2) AS 'ASSESS VALUE',
  history_table.`EFF`,
  history_table.`memo_id`,
  memoranda.`memo_string` AS MEMORANDA, 
  TD_ownersinfo.`owner`,
  TD_ownersinfo.`location`,
  TD_ownersinfo.`transaction`
FROM
  history_table 
  INNER JOIN memoranda 
    ON memoranda.memo_id = history_table.memo_id 
  LEFT JOIN td_ownersinfo 
    ON history_table.TDNO = td_ownersinfo.TD_NO
WHERE history_table.TDNO LIKE '%%' 
  AND history_table.titleNO LIKE '%%' 
  AND td_ownersinfo.owner LIKE '%%' 
  AND td_ownersinfo.transaction LIKE '%%' 
  AND td_ownersinfo.location LIKE '%%' 
  OR TD_ownersinfo.`TD_NO` IS NULL 
ORDER BY history_table.history_id 

结果是这样的:

history_id  TAX DECLARATION NO  TITLE NO  LOT NO  AREA     ENCUMBRANCES  ASSESS VALUE  EFF     memo_id  MEMORANDA            owner           location  transaction  
----------  ------------------  --------  ------  -------  ------------  ------------  ------  -------  -------------------  --------------  --------  -------------
       145  F-111111            T-00000   LOTF1   500 SQM  NONE          12,331.13     2016    415      TEST3.               name2           LAS VEGAS  WALK-IN      
       146  F-000000            T-000000  LOT F0  1 HA     NONE          123.23        2015    412      TEST.                name1           SAN JOSE  TRANSFER     
       147  E-000000            T-00000   LOTE0   500 SQM  NONE          13,123.12     1994    413      TEST2.               (NULL)          (NULL)    (NULL)       

但是当我尝试将值放入 history_table.TDNO LIKE '%%' 时，td_ownersinfo 表中具有空值的 TDNO 仍将显示。例如，我将在同一查询中将值放入history_table.TDNO:history_table.TDNO LIKE '%F-111111%'。结果将是这样的:

history_id  TAX DECLARATION NO  TITLE NO  LOT NO  AREA     ENCUMBRANCES  ASSESS VALUE  EFF     memo_id  MEMORANDA   owner           location  transaction  
----------  ------------------  --------  ------  -------  ------------  ------------  ------  -------  ----------  --------------  --------  -------------
       145  F-111111            T-00000   LOTF1   500 SQM  NONE          12,331.13     2016    415      TEST3.      name2           LAS VEGAS  WALK-IN      
       147  E-000000            T-00000   LOTE0   500 SQM  NONE          13,123.12     1994    413      TEST2.      (NULL)          (NULL)    (NULL)       

我知道问题出在 WHERE 子句中，我在 OR TD_ownersinfo.TD_NO IS NULL 中使用 OR，有什么方法可以解决这个问题吗？

Answer 1

这是一个复杂的问题。基本上，当您不与其他字段之一的测试值进行比较时，您只想返回 td_ownership 中具有 NULL 值的行。您可以通过以下方法使用查询字符串变量 ( SQLFiddle ) 来实现此目的:

SET @tdno = '%%';
SET @titleno = '%%';
SET @owner = '%%';
SET @transaction = '%%';
SET @location = '%%';
SELECT 
  history_table.`history_id`,
  history_table.`TDNO` AS 'TAX DECLARATION NO',
  history_table.`titleNO` AS 'TITLE NO',
  history_table.`lotNO` AS 'LOT NO',
  history_table.`area` AS 'AREA',
  history_table.`encumbrances` AS 'ENCUMBRANCES',
  FORMAT(history_table.`assess_value`, 2) AS 'ASSESS VALUE',
  history_table.`EFF`,
  history_table.`memo_id`,
  memoranda.`memo_string` AS MEMORANDA, 
  TD_ownersinfo.`owner`,
  TD_ownersinfo.`location`,
  TD_ownersinfo.`transaction`
FROM
  history_table 
  INNER JOIN memoranda 
    ON memoranda.memo_id = history_table.memo_id 
  LEFT JOIN td_ownersinfo 
    ON history_table.TDNO = td_ownersinfo.TD_NO
WHERE history_table.TDNO LIKE @tdno
  AND history_table.titleNO LIKE @titleno
  AND (td_ownersinfo.owner LIKE @owner OR @owner='%%' AND td_ownersinfo.TD_NO IS NULL)
  AND (td_ownersinfo.transaction LIKE @transaction OR @transaction='%%' AND td_ownersinfo.TD_NO IS NULL) 
  AND (td_ownersinfo.location LIKE @location OR @location='%%' AND td_ownersinfo.TD_NO IS NULL)
  OR 
  td_ownersinfo.TD_NO IS NULL 
  AND CONCAT(@tdno,@titleno,@owner,@transaction,@location) = '%%%%%%%%%%'
ORDER BY history_table.history_id 

它通过检查所有变量字符串是否为 '%%' (因此所有 5 个变量的 CONCAT)来检查什么是有效的“获取所有数据”查询其中是 '%%%%%%%%%%')，或者如果与 td_ownersinfo 表关联的变量字符串是 '%%' 并且仅在这种情况下允许 td_ownersinfo.TD_NO 为 NULL。要检查特定所有者，您可以将 @owner 更改为(例如)

SET @owner='%name2%';

如果您要使用 PHP 等语言进行此查询，则不会使用 MySQL 变量，而是会执行以下操作:

$tdno = '%%';
$titleno = '%%';
$owner = '%%';
$transaction = '%%';
$location = '%%';
$sql = "SELECT 
  history_table.`history_id`,
  history_table.`TDNO` AS 'TAX DECLARATION NO',
  history_table.`titleNO` AS 'TITLE NO',
  history_table.`lotNO` AS 'LOT NO',
  history_table.`area` AS 'AREA',
  history_table.`encumbrances` AS 'ENCUMBRANCES',
  FORMAT(history_table.`assess_value`, 2) AS 'ASSESS VALUE',
  history_table.`EFF`,
  history_table.`memo_id`,
  memoranda.`memo_string` AS MEMORANDA, 
  TD_ownersinfo.`owner`,
  TD_ownersinfo.`location`,
  TD_ownersinfo.`transaction`
FROM
  history_table 
  INNER JOIN memoranda 
    ON memoranda.memo_id = history_table.memo_id 
  LEFT JOIN td_ownersinfo 
    ON history_table.TDNO = td_ownersinfo.TD_NO
WHERE history_table.TDNO LIKE '$tdno'
  AND history_table.titleNO LIKE '$titleno'
  AND (td_ownersinfo.owner LIKE '$owner' OR '$owner'='%%' AND td_ownersinfo.TD_NO IS NULL)
  AND (td_ownersinfo.transaction LIKE '$transaction' OR '$transaction'='%%' AND td_ownersinfo.TD_NO IS NULL) 
  AND (td_ownersinfo.location LIKE '$location' OR '$location'='%%' AND td_ownersinfo.TD_NO IS NULL)
  OR 
  td_ownersinfo.TD_NO IS NULL 
  AND '$tdno$titleno$owner$transaction$location' = '%%%%%%%%%%'
ORDER BY history_table.history_id";
// use the appropriate db command here...
$result = $conn->query($sql);