需要帮助找出MySQL查询计数如果某个数字 [英] Need help figuring out MySQL query to count if a certain number

问题描述

我有一个表设置如下：

fruit1 | fruit2 | fruit3 | fruit4 | number1 | number2 | number3 | number4
--------------------------------------------------------------------------
apple  | orange | banana |  berry |    5    |    2    |    1    |    4   
orange | banana | apple  |  berry |    3    |    2    |    5    |    2
berry  | banana | orange |  apple |    1    |    2    |    5    |    2

我需要一个MySQL查询来计算一个给定的水果是多少次5.在这个例子中，苹果是5次两次，橙色是5次。那有意义吗？ fruit1的数字是number1，fruit2的数字是number2，等等...

I need a MySQL query to count how many times a given fruit is number 5. In this example, apple is 5 twice and orange is 5 once. Does that make sense? fruit1's number is number1, fruit2's number is number2, etc...

我可以做一些PHP的代码，但我知道这是一个非常简单的MySQL查询。

I could do this with a bit of php code but I know it's a pretty simple MySQL query. I just have the hardest time putting queries together.

提前感谢！

推荐答案

SELECT fruit, COUNT(*) NumberOfInstance
FROM
(
    SELECT fruit1 fruit, number1 num FROM table1
    UNION ALL
    SELECT fruit2 fruit, number2 num FROM table1
    UNION ALL
    SELECT fruit3 fruit, number3 num FROM table1
    UNION ALL
    SELECT fruit4 fruit, number4 num FROM table1
) s
WHERE num = 5
GROUP BY fruit

• SQLFiddle演示链接


• SQLFiddle Demo Link

这篇关于需要帮助找出MySQL查询计数如果某个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！