根据id使用PHP和MySQL计数传递，失败，跳过数字

Question

I have four tables look like below:

test_case (1='pass',2='fail',3='skip')

id        testing_status
 1              1
 2              3
 3              1
 4              2
 5              3
 6              2
 7              2
 8              3

test_suite_with_case (referenced with test_case_id in test_case table and test_suite_id with test_suite table)

id        test_suite_id       test_case_id
 1              4             1,3,5,2,6,7,8
 2              3               2,5,4,6,7

test_suite

id        test_suite_name
 3            test_1
 4            test_2

test_suite_run (referenced with test_suite_id in test_suite table)

id        test_suite_id       name
 1            3               BBH
 2            4               CXN

Now i want to run a where query from test_suite_run by id (for example id=2 in test_suite_run) and want a output look like below:

id(test_suite_run)    name       test_suite_name   pass   fail   skip
 1                    CXN          test_suite_2      2     2       3 

I am new in PHP and MySQL.

Answer 1

Try this:

select 
    tr.id,
    tr.name,
    ts.test_suite_name,
    sum(testing_status = 1) pass,
    sum(testing_status = 2) fail,
    sum(testing_status = 3) skip
from test_suite_run tr
inner join test_suite ts on tr.test_suite_id = ts.id
inner join test_suite_with_case tsc on ts.id = tsc.test_suite_id
inner join test_case tc on find_in_set(tc.id, tsc.test_case_id) > 0
group by
    tr.id,
    tr.name,
    ts.test_suite_name;