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