MySQL使用条件逻辑进行选择 [英] Mysql select with conditional logic

问题描述

我想在以下方面提供帮助:
(我的描述可能看起来很奇怪，下面的示例图片:)

I would like some help with the following:
(my description will possibly seem weird, example picture below :)

为了生成图形，我想根据表单生成的查询选择天数" (在此阶段，查询将仅包含来自"和至"国家/地区).

in order to generate a graph, i want to select the 'days' based on a query generated by a form (in this stage the query will only contain the 'from' and 'to' countries).

我的问题是我不知道如何使select使用条件逻辑，因此它会从具有匹配的"from"和"to"的每个"item_id"中选择"days".

My problem is that i have no idea on how to make select use conditional logic so it picks 'days' from every 'item_id' that has a matching 'from' and 'to'.

(另一个问题是我不知道任何好的关键字，或者实际上它是怎么称呼的，所以请原谅我，我只做了一些盲目搜索，并试图在这里找到类似的问题)

(well the other problem is that i don't know any good keywords or actually how this is called, so please forgive me, i only did some blind googling and tried to find similar problems here)

推荐答案

SELECT days.meta_value
FROM tableName AS from_country,
     tableName AS to_country,
     tableName AS days
WHERE from_country.item_id = to_country.item_id
  AND from_country.item_id = days.item_id
  AND from_country.field_id = 90
  AND to_country.field_id = 93
  AND days.field_id = 251

如果要按from_country.meta_value或类似方法进行过滤，则可以添加更多限制.并且应该用表的实际名称替换tableName.

You can add more restrictions if you want to filter by from_country.meta_value or similar. And you should replace tableName with the actual name of the table.

将from_country，to_country和days视为指向表中行的三个不同的指针，或者将不同的变量用作从关系中获取值的变量.您希望它们三个都描述相同的项目，并且还希望它们每个都引用与其名称关联的字段.这将导致上述情况.

Think of from_country, to_country and days as three different pointers to rows in your table, or alternatively different variables taking values from your relation. You want all three of them to describe the same item, and you also want each of them to refer to the field associated with its name. This results in the conditions stated above.

您甚至可以创建一个视图，以便像设计正确的表一样访问此设计不良的表:

You could even create a view, in order to access this badly designed table like a properly designed one:

CREATE VIEW viewName AS
SELECT item.item_id AS item_id,
       from_country.meta_value AS from_country,
       to_country.meta_value AS to_country,
       days.meta_value AS days
FROM (SELECT DISTINCT item_id FROM tableName) AS item
     LEFT JOIN tableName AS from_country
            ON (from_country.item_id = item.item_id AND
                from_country.field_id = 90)
     LEFT JOIN tableName AS to_country
            ON (to_country.item_id = item.item_id AND
                to_country.field_id = 93)
     LEFT JOIN tableName AS days
            ON (days.item_id = item.item_id AND
                days.field_id = 251)

这将创建一个具有四列的视图，您可以从中轻松选择:

This would create a view with four columns, from which you could simply select:

SELECT days FROM viewName WHERE from_country LIKE 'A%'

或您要选择的任何内容.请注意，由于左联接，某些项目的缺失值将导致NULL值.这与上面的查询相反，后者将省略所有未指定所有三个值的项目.使用适合您情况的任何东西.

or whatever you want to select. Note that due to the left joins, missing values for some items will result in NULL values. This is in contrast to the query above, which will omit any item which doesn't have all three values specified. Use whatever is more appropriate in your situation.

这篇关于MySQL使用条件逻辑进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！