mysql - 使用许多 IF 条件优化 MySQL SELECT 查询

Question

有没有办法以更短或更简单的方式做到这一点？
我正在尝试在产品名称中搜索品牌并将其显示在新列中。
看起来有点乱:

SELECT 
    p.id, p.code, p.name, p.unit, p.size, 
    IF (INSTR(LCASE(p.name),' apple ')>0, 'Apple', IF (INSTR(LCASE(p.name),' western digital ')>0, 'Western Digital', IF (INSTR(LCASE(p.name),' kingston ')>0, 'Kingston', 
        IF (INSTR(LCASE(p.name),' samsung ')>0, 'Samsung', IF (INSTR(LCASE(p.name),' brother ')>0, 'Brother', IF (INSTR(LCASE(p.name),' noganet ')>0, 'NogaNet', IF (INSTR(LCASE(p.name),' noga ')>0, 'Noganet', 
        IF (INSTR(LCASE(p.name),' verbatim ')>0, 'Verbatim', IF (INSTR(LCASE(p.name),' logitech ')>0, 'Logitech', IF (INSTR(LCASE(p.name),' sandisk ')>0, 'Sandisk', IF (INSTR(LCASE(p.name),' infotec ')>0, 'Infotec', 
        IF (INSTR(LCASE(p.name),' novatech ')>0, 'Novatech', IF (INSTR(LCASE(p.name),' lg ')>0, 'LG', IF (INSTR(LCASE(p.name),' epson ')>0, 'Epson', IF (INSTR(LCASE(p.name),' genius ')>0, 'Genius', 
        IF (INSTR(LCASE(p.name),' hp ')>0, 'HP', IF (INSTR(LCASE(p.name),' tp-link ')>0, 'TP-Link', IF (INSTR(LCASE(p.name),' tplink ')>0, 'TP-Link', IF (INSTR(LCASE(p.name),' nisuta ')>0, 'Nisuta', 
        IF (INSTR(LCASE(p.name),' panacom ')>0, 'Panacom', IF (INSTR(LCASE(p.name),' sony ')>0, 'Sony', IF (INSTR(LCASE(p.name),' philips ')>0, 'Philips', IF (INSTR(LCASE(p.name),' toshiba ')>0, 'Toshiba', 
        IF (INSTR(LCASE(p.name),' sapphire ')>0, 'Sapphire', IF (INSTR(LCASE(p.name),' bangho ')>0, 'Bangho', IF (INSTR(LCASE(p.name),' trendnet ')>0, 'Trendnet', IF (INSTR(LCASE(p.name),' maxell ')>0, 'Maxell', 
        IF (INSTR(LCASE(p.name),' siyoteam ')>0, 'Siyoteam', IF (INSTR(LCASE(p.name),' marware ')>0, 'Marware', IF (INSTR(LCASE(p.name),' videomate ')>0, 'VideoMate', IF (INSTR(LCASE(p.name),' manhattan ')>0, 'Manhattan', 
        IF (INSTR(LCASE(p.name),' wd ')>0, 'Western Digital', IF (INSTR(LCASE(p.name),' asus ')>0, 'Asus', IF (INSTR(LCASE(p.name),' gigabyte ')>0, 'Gigabyte', IF (INSTR(LCASE(p.name),' nvidia ')>0, 'nVidia', 
        IF (INSTR(LCASE(p.name),' intel ')>0, 'Intel', IF (INSTR(LCASE(p.name),' amd ')>0, 'AMD', IF (INSTR(LCASE(p.name),' super electric ')>0, 'Super Electric', IF (INSTR(LCASE(p.name),' encore ')>0, 'Encore', 
        IF (INSTR(LCASE(p.name),' omega ')>0, 'Omega', IF (INSTR(LCASE(p.name),' domo ')>0, 'Domo', IF (INSTR(LCASE(p.name),' benq ')>0, 'Benq', IF (INSTR(LCASE(p.name),' coby ')>0, 'Coby', 
        IF (INSTR(LCASE(p.name),' edifier ')>0, 'Edifier', IF (INSTR(LCASE(p.name),' kodak ')>0, 'Kodak', IF (INSTR(LCASE(p.name),' liteon ')>0, 'Liteon', IF (INSTR(LCASE(p.name),' veryjet ')>0, 'VeryJet', 
        IF (INSTR(LCASE(p.name),' microsoft ')>0, 'Microsoft', IF (INSTR(LCASE(p.name),' asrock ')>0, 'Asrock', IF (INSTR(LCASE(p.name),' acer ')>0, 'Acer', IF (INSTR(LCASE(p.name),' sharknet ')>0, 'SharkNet', 
        IF (INSTR(LCASE(p.name),' airlive ')>0, 'Airlive', IF (INSTR(LCASE(p.name),' bags ')>0, 'Bags', IF (INSTR(LCASE(p.name),' cooler master ')>0, 'Cooler Master', IF (INSTR(LCASE(p.name),' foscam ')>0, 'Foscam', 
        IF (INSTR(LCASE(p.name),' eurocase ')>0, 'Eurocase', IF (INSTR(LCASE(p.name),' garmin ')>0, 'Garmin', IF (INSTR(LCASE(p.name),' kozumi ')>0, 'Kozumi', IF (INSTR(LCASE(p.name),' lenovo ')>0, 'Lenovo', 
        IF (INSTR(LCASE(p.name),' linksys ')>0, 'Linksys', IF (INSTR(LCASE(p.name),' cisco ')>0, 'Cisco', IF (INSTR(LCASE(p.name),' d-link ')>0, 'D-Link', IF (INSTR(LCASE(p.name),' dlink ')>0, 'D-Link', 
        IF (INSTR(LCASE(p.name),' msi ')>0, 'MSI', IF (INSTR(LCASE(p.name),'  ')>0, '', IF (INSTR(LCASE(p.name),' pcbox ')>0, 'PcBox', IF (INSTR(LCASE(p.name),' sanyo ')>0, 'Sanyo', 
        IF (INSTR(LCASE(p.name),' seagate ')>0, 'Seagate', IF (INSTR(LCASE(p.name),' trv ')>0, 'TRV', IF (INSTR(LCASE(p.name),' vitsuba ')>0, 'Vitsuba', 
        '')))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) AS marca,
    p.cost, p.price, p.image,
    p.category_id, c.code AS category_code, c.name AS category_name, 
    p.subcategory_id, s.code AS subcategory_code, s.name AS subcategory_name, 
    p.cf1, p.cf2, p.cf3, p.cf4, p.cf5, p.cf6, 
    w.quantity, s.sales
FROM products AS p
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS quantity
    FROM warehouses_products
    GROUP BY product_id
) AS w ON w.product_id=p.id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS sales
    FROM sale_items
    GROUP BY product_id
) AS s ON s.product_id=p.id
LEFT JOIN categories AS c ON c.id=p.category_id
LEFT JOIN subcategories AS s ON s.id=p.subcategory_id
WHERE p.id!=27
ORDER BY p.id ASC;

Answer 1

您需要有一长串条件。我认为 case 语句更适合您想要做的事情:

此外，我会使用 like 而不是 instr()。默认情况下，like 不匹配大小写，因此您可以执行以下操作:

(case when p.name like '% apple %' then 'Apple'
      when p.name like '% western digital %' then 'Western Digital'
      . . .
      else ''
 end) as marca

您仍然需要执行相同的比较列表，因此代码会很长。

如果性能不是一个特别大的问题，但维护代码是一个特别大的问题，那么您可能需要一个包含搜索字符串和全名的表。典型的行如下所示:

SEARCHSTRING    FULLNAME
'% apple %'     'Apple'

然后您可以通过执行以下操作找到所有匹配项:

select p.name, group_concat(ss.fullname separator ', ') as fullnames
from products p left outer join
     searchstrings ss
     on p.name like ss.searchstring
group by p.name;

然后您只需向该表添加新行即可搜索其他产品。

编辑:

如果您希望第二个建议的每场比赛一行，您可以这样做:

select p.name, ss.fullname
from products p left outer join
     searchstrings ss
     on p.name like ss.searchstring;