MySQL select语句是CASE还是IF ELSEIF?不确定如何获得结果 [英] MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result

问题描述

我有两个桌子.一个拥有制造商信息，并包括他们可以销售的区域.另一个有他们的产品出售.我们必须根据地区来限制产品的可见性.这就像Netflix的系统中的视频只能在任何地方(1)，仅在加拿大(2)，仅在美国(3)观看一样.

I have a two tables. One has manufacturer information and includes the regions where they can sell. The other has their products for sale. We have to limit visibility of the product based on the regions. This is like Netflix have videos in their system that can only be viewed Everywhere (1), only in Canada (2), only in USA (3).

我正在尝试进行查询，以根据制造商表中的设置告诉我可以在哪里查看产品.

I am trying to make a query that tells me where the product can be viewed based on the settings in the manufacturer table.

例如，在制造商表中，有两个字段称为Exposure_new和Exposure_used，每个字段的值分别为1,2或3，以限制可以观看其新视频或使用过的视频的位置.

For example, in the manufacturer table, there are two fields called expose_new and expose_used each of which will have a value of 1,2 or 3 to limit where their new or used videos can seen.

添加视频时，不会为它们分配曝光"值，这是根据当前制造商的Exposure_new或Exposure_used值在将它们添加到我们的索引中时即时进行的.

When the videos are added, they are not assigned an 'expose' value and this is meant to be done on the fly when adding them to our index depending on the current manufacturer's expose_new or expose_used values.

我要获取的是商品详细信息以及根据其是新的还是二手的以及可以在何处看到的计算值，以及为其所有新的或二手产品分配给制造商的规则/值.我需要基于每个产品的一位数字，以便有条件地将其显示在列表中.

What I am trying to get is the item details and the computed value for where it can be seen based on whether it is new or used and the rule/value assigned to the manufacturer for all their new or used products. I need this single digit on a per-product basis to conditionally display it in a list.

以下操作无效，但是您将了解我正在尝试执行的操作.我已经使用CASE语句和以下WRONG IF/ELSEIF语句进行了尝试.

The following does not work, but you will get the idea of what I am trying to do. I have tried this with CASE statements and the following WRONG IF/ELSEIF statement.

任何帮助调试此错误并将其指向正确方向的帮助，将不胜感激.

Any help to debugger this and point me in the right direction would be appreciated.

SELECT 
t2.company_name,
t2.expose_new,  // 1,2 or 3
t2.expose_used, // 1,2 or 3
t1.title,
t1.seller,
t1.status,  //can be new or used
(SELECT 
IF(status ='New',
  (select expose_new from manufacturers where id = t1.seller),1
)
ELSEIF(t1.status ='Used',
  (select expose_used from manufacturers where id = t1.seller),1
)
END IF
) as 'expose'
FROM `products` t1
join manufacturers t2 on t2.id = t1.seller
where t1.seller = 4238

这是一个CASE版本，实际上似乎在执行，但无论什么情况发生(在本例中为1)，总是会产生第一个值.我不确定我是否可以在每个WHEN语句中使用AND来添加另一个测试，但它不会给出错误，而只会给出错误的结果.

Here is a CASE version that actually seems to execute but always results in the first value no matter what happens to be true (in this case 1). I am not sure that I can have the addition of another test with the AND in each WHEN statement but it does not give an error, only the wrong result.

SELECT 
t2.company_name,
t2.expose_new,
t2.expose_used,
t1.title,
t1.status,
 CASE status
   when 'New' and t2.expose_new = 1 then 1
   when 'New' and t2.expose_new = 2 then 2
   when 'New' and t2.expose_new = 3 then 3
   when 'Used' and t2.expose_used = 1 then 1
   when 'Used' and t2.expose_used = 2 then 2
   when 'Used' and t2.expose_used = 3 then 3
END as expose
FROM `products` t1
join manufacturers t2 on t2.id = t1.seller
where t1.seller = 4238

推荐答案

尝试此查询-

SELECT 
  t2.company_name,
  t2.expose_new,
  t2.expose_used,
  t1.title,
  t1.seller,
  t1.status,
  CASE status
  WHEN 'New' THEN t2.expose_new
  WHEN 'Used' THEN t2.expose_used
  ELSE NULL
  END as 'expose'
FROM
  `products` t1
JOIN manufacturers t2
  ON
    t2.id = t1.seller
WHERE
  t1.seller = 4238

这篇关于MySQL select语句是CASE还是IF ELSEIF?不确定如何获得结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！