带有 CASE 或 IF ELSEIF 的 MySQL 选择语句？不知道如何得到结果

MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result

提问by user409858

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).

我有两张桌子。一个有制造商信息，包括他们可以销售的地区。另一个有他们的产品出售。我们必须根据区域限制产品的可见性。这就像 Netflix 系统中的视频只能在任何地方 (1)、仅在加拿大 (2)、仅在美国 (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.

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

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.

例如，在制造商表中，有两个字段，称为expose_new 和expose_used，每个字段的值都为1,2 或3，以限制可以看到他们的新视频或使用过的视频的位置。

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.

添加视频时，它们不会被分配“曝光”值，这意味着在将它们添加到我们的索引时根据当前制造商的 Exposure_new 或 Exposure_used 值即时完成。

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.

我想要得到的是物品的详细信息和计算值，根据它是新的还是旧的，以及分配给制造商的所有新产品或旧产品的规则/值，可以看到它的位置。我需要在每个产品的基础上使用这个单个数字来有条件地将其显示在列表中。

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.

以下内容不起作用，但您将了解我正在尝试做什么。我已经用 CASE 语句和以下 WRONG IF/ELSEIF 语句尝试过这个。

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

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.

这是一个 CASE 版本，它实际上似乎在执行，但无论结果为真（在本例中为 1），它总是产生第一个值。我不确定我是否可以在每个 WHEN 语句中添加另一个带有 AND 的测试，但它不会给出错误，只会给出错误的结果。

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

回答by Devart

Try this query -

试试这个查询 -

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

回答by Limitless isa

Syntax:

句法：

CASE value WHEN [compare_value] THEN result 
[WHEN [compare_value] THEN result ...] 
[ELSE result] 
END

Alternative:CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]

替代方案：CASE WHEN [条件] THEN 结果 [WHEN [条件] THEN 结果 ...]

mysql> SELECT CASE  WHEN 2>3 THEN 'this is true' ELSE 'this is false' END; 
+-------------------------------------------------------------+
| CASE  WHEN 2>3 THEN 'this is true' ELSE 'this is false' END |
+-------------------------------------------------------------+
| this is false                                               | 
+-------------------------------------------------------------+

I am use:

我正在使用：

SELECT  act.*,
    CASE 
        WHEN (lises.session_date IS NOT NULL AND ses.session_date IS NULL) THEN lises.location_id
        WHEN (lises.session_date IS NULL AND ses.session_date IS NOT NULL) THEN ses.location_id
        WHEN (lises.session_date IS NOT NULL AND ses.session_date IS NOT NULL AND lises.session_date>ses.session_date) THEN ses.location_id
        WHEN (lises.session_date IS NOT NULL AND ses.session_date IS NOT NULL AND lises.session_date<ses.session_date) THEN lises.location_id
    END AS location_id
FROM activity AS act
LEFT JOIN li_sessions AS lises ON lises.activity_id = act.id AND  lises.session_date >= now()
LEFT JOIN session AS ses ON  ses.activity_id = act.id AND  ses.session_date >= now()
WHERE act.id

回答by Ali Hamza

Another way of doing this is using nested IF statements. Suppose you have companies table and you want to count number of records in it. A sample query would be something like this

另一种方法是使用嵌套的 IF 语句。假设您有公司表并且您想计算其中的记录数。示例查询将是这样的

SELECT IF(
      count(*) > 15,
      'good',
      IF(
          count(*) > 10,
          'average',
          'poor'
        ) 
      ) as data_count 
      FROM companies

Here second IF condition works when the first IF condition fails. So Sample Syntax of the IF statement would be IF ( CONDITION, THEN, ELSE).Hope it helps someone.

这里第二个 IF 条件在第一个 IF 条件失败时起作用。所以 IF 语句的示例语法将是IF ( CONDITION, THEN, ELSE)。希望它可以帮助某人。