时间戳

首页 > 解决方案 > 具有多个条件的 MySQL 案例未按预期工作

mysql - 具有多个条件的 MySQL 案例未按预期工作

问题描述

这真的很奇怪，我有这个 SQL 查询：

    select p.NAME, p.MEASURING_UNIT, p.EAN, p.ITEM_NR, IF(f.FORM_TYPE = 'InventoryList', log.QUANTITY_DIFF, fi.QUANTITY) AS VAL_QUANTITY, IF(IF(f.FORM_TYPE = 'InventoryList', log.QUANTITY_DIFF, fi.QUANTITY)>0,fi.GROSS_MONEY,0) as VAL_GROSS_MONEY, f.FORM_DATE, f.FORM_TYPE, f.DIRECTION, f.FORM_NR, pd.NAME as PARTNER_NAME, ad.CITY,
case f.FORM_TYPE 
    when 'Quotation' then 'Quot.'
    when 'Invoice' then 'Inv.'
    when 'Shipment' then 'Ship.'
    when 'Orders' then 'Ord.'
    when 'FacilityForm' then 'FacF.'
    when 'InventoryList' then 'InvL.'
    else 'Else'
    end as FORM_TYPE_CODE,
case f.DIRECTION
    when 1 AND f.FORM_TYPE <> 'InventoryList' then 'Sale'
    when 2 AND f.FORM_TYPE <> 'InventoryList' then 'Supply'
    when 3 AND f.FORM_TYPE <> 'InventoryList' then 'Moving'
    else 'Else'
    end as DIRECTION_CODE
from product p join form_item fi on p.id = fi.product_id 
join form f on fi.FORM_ID = f.ID 
join partner_data pd on pd.ID = f.PARTNER_DATA_ID 
join address_data ad on ad.ID = f.ADDRESS_DATA_ID 
join inventory_log log on log.FORM_ITEM_ID = fi.ID
where p.id =  8
AND fi.ID IN (SELECT l.FORM_ITEM_ID FROM inventory_log l, inventory_item i, product p 
WHERE l.INVENTORY_ITEM_ID = i.ID
AND i.PRODUCT_ID = p.ID
AND p.ID = 8 )
and f.status > 1 
order by f.FORM_DATE

...而且看起来第二个 CASE 函数没有像我预期的那样工作。现在，如果 FORM_TYPE 是 'FacilityForm' 并且 DIRECTION 是 2，我希望我能回到 DIRECTION_CODE 'Supply'，但我给了我：'Else'...

如果我从中删除第二个条件并保持不变：

when 2 then 'Supply'

它的工作，但我不知道为什么......

有人可以在这里帮助我吗，我错过了什么？谢谢你。

顺便提一句。这是查询结果，错误的值在最后一行：

NAME            MEASURING_UNIT  EAN         ITEM_NR     VAL_QUANTITY    VAL_GROSS_MONEY FORM_DATE   FORM_TYPE       DIRECTION   FORM_NR         PARTNER_NAME            CITY            FORM_TYPE_CODE  DIRECTION_CODE
Test product    pc              EAN-PRD1    PROD-001    20000           1371600         2018-05-16  Invoice         1           INV-00003/2018  Partner Ltd.            CityNameTest    Inv.            Sale
Test product    pc              EAN-PRD1    PROD-001    100000          1257300         2018-05-25  Invoice         1           INV-00006/2018  Partner Ltd.            CityNameTest    Inv.            Sale
Test product    pc              EAN-PRD1    PROD-001    50000           380365          2018-06-02  Invoice         1           INV-00008/2018  Good partner Limited    CityNameTest    Inv.            Sale
Test product    pc              EAN-PRD1    PROD-001    -33000          0               2018-06-27  InventoryList   1           INVL-00001/2018 New Partner             CityNameTest    InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    -40000          0               2018-06-29  InventoryList   1           INVL-00002/2018 InventoryList           CityNameTest    InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    -55000          0               2018-06-30  InventoryList   1           INVL-00003/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    -43000          0               2018-07-11  InventoryList   1           INVL-00004/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    30000           149850          2018-07-13  InventoryList   1           INVL-00014/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    21000           119880          2018-07-13  InventoryList   1           INVL-00012/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    0               0               2018-07-13  InventoryList   1           INVL-00011/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    0               0               2018-07-13  InventoryList   1           INVL-00010/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    0               0               2018-07-13  InventoryList   1           INVL-00009/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    -35000          0               2018-07-13  InventoryList   1           INVL-00008/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    -50000          0               2018-07-13  InventoryList   1           INVL-00007/2018 InventoryList                           InvL.           Else
Test product    pc              EAN-PRD1    PROD-001    50000           49950           2018-07-18  Invoice         1           INV-00009/2018  Good partner Limited    CityNameTest    Inv.            Sale
Test product    pc              EAN-PRD1    PROD-001    42000           41958           2018-08-17  Shipment        1           SHP-00002/2018  Good partner Limited    CityNameTest    Ship.           Sale
Test product    pc              EAN-PRD1    PROD-001    42000           41958           2018-08-17  Shipment        1           SHP-00002/2018  Good partner Limited    CityNameTest    Ship.           Sale
Test product    pc              EAN-PRD1    PROD-001    50000           49950           2018-09-04  Invoice         1           INV-00010/2018  Good partner Limited    CityNameTest    Inv.            Sale
Test product    pc              EAN-PRD1    PROD-001    100000          99900           2018-09-05  Invoice         1           INV-00011/2018  Good partner Limited    CityNameTest    Inv.            Sale
Test product    pc              EAN-PRD1    PROD-001    200000          199800          2018-09-27  FacilityForm    2           FAC-00002/2018  Company partner         CityNameTest    FacF.           Else

标签： mysqlsql

解决方案

如果您对列使用大小写，则不能添加额外条件：

case ColumnA
when 1 then 'x'
when 2 then 'y'
else'z'
end

这很有效，因为您正在比较列的内容。

要使用多个条件，请将列移动到 case 语句中：

case 
when columna = 1 and columnb = 2 then 'x'
when columna = 2 and columnb = 3 then 'y'
else 'z'
end

推荐阅读