mysql查询某个数到某个数,需要帮助找出MySQL查询计数如果某个数字

I have a table setup like this:

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

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

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.

Thanks in advance!

解决方案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