MySQL 选择案例时（选择）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14885912/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT CASE WHEN THEN (SELECT)
提问by Stewart Haffenden
I am trying to select a different set of results for a product depending on a product type. So if my product should be a book I want it to look up the UPC and Artist for a normal product these details are however irrelevant and for another product I would want a completely different set of results.
我正在尝试根据产品类型为产品选择一组不同的结果。因此，如果我的产品应该是一本书，我希望它查找普通产品的 UPC 和艺术家，但这些细节无关紧要，而对于另一种产品，我想要一组完全不同的结果。
SELECT CASE Product.type_id
WHEN 10 THEN (
SELECT
Product.product_id,
Product.type_id,
Product.product_name,
Product.UPC,
Product_Type.type,
CONCAT_WS(' ' , first_name, middle_name, last_name ) AS artistC
FROM Product, Product_Type, Product_ArtistAuthor
WHERE Product.type_id = Product_Type.type_id
AND Product.product_id = $pid
AND Product.artist_id = Product_ArtistAuthor.artist_id
)
ELSE (
SELECT
Product.product_id,
Product.type_id,
Product.product_name,
Product_Type.type
FROM Product, Product_Type
WHERE Product.type_id = Product_Type.type_id
AND Product.product_id = $pid
)
END
FROM Product
WHERE Product.product_id = $pid
I am not sure where I am going wrong
我不确定我哪里出错了
回答by Kevin Kunderman
You Could try the other format for the case statement
您可以尝试使用其他格式的 case 语句
CASE WHEN Product.type_id = 10
THEN
(
Select Statement
)
ELSE
(
Other select statement
)
END
FROM Product
WHERE Product.product_id = $pid
See http://msdn.microsoft.com/en-us/library/ms181765.aspxfor more information.
有关详细信息，请参阅http://msdn.microsoft.com/en-us/library/ms181765.aspx。
回答by Ryan-Neal Mes
You should avoid using nested selects and I would go as far to say you should never use them in the actual select part of your statement. You will be running that select for each row that is returned. This is a really expensive operation. Rather use joins. It is much more readable and the performance is much better.
你应该避免使用嵌套的选择，我会说你永远不应该在你的语句的实际选择部分使用它们。您将为返回的每一行运行该选择。这是一个非常昂贵的操作。而是使用连接。它更具可读性，性能也更好。
In your case the query below should help. Note the cases statement is still there, but now it is a simple compare operation.
在您的情况下，下面的查询应该会有所帮助。请注意，cases 语句仍然存在，但现在它是一个简单的比较操作。
select
p.product_id,
p.type_id,
p.product_name,
p.type,
case p.type_id when 10 then (CONCAT_WS(' ' , first_name, middle_name, last_name )) else (null) end artistC
from
Product p
inner join Product_Type pt on
pt.type_id = p.type_id
left join Product_ArtistAuthor paa on
paa.artist_id = p.artist_id
where
p.product_id = $pid
I used a left join since I don't know the business logic.
我使用了左连接，因为我不知道业务逻辑。
回答by Ed Heal
For a start the first select has 6 columns and the second has 4 columns. Perhaps make both have the same number of columns (adding nulls?).
首先，第一个选择有 6 列，第二个选择有 4 列。也许使两者具有相同数量的列（添加空值？）。
回答by Stewart Haffenden
I ended up leaving the common properties from the SELECT queries and making a second SELECT query later on in the page. I used a php IF command to call for different scripts depending on the first SELECT query, the scripts contained the second SELECT query.
我最终从 SELECT 查询中保留了公共属性，并在稍后的页面中进行了第二个 SELECT 查询。我使用 php IF 命令根据第一个 SELECT 查询调用不同的脚本，这些脚本包含第二个 SELECT 查询。
