mysql - 如果 where 子句中的任何列具有 active=1，如何创建一个 where 子句来返回

Question

我有一个如下所示的 SQL 查询，其中有一个 where 子句 WHERE pg.active=1 AND tcg.active=1 AND tpg.active=1，现在的问题是其中一个或全部pg.active 的 tcg.active 、tpg.active 随时设置为 1，在其中一个未设置的情况下to 1 查询不会返回任何内容。

如果 pg.active tcg.active 、tpg.active 设置为 1，如何更改要返回的 where 子句？

    SELECT  
    ..........
    FROM software_products_software_images spsi 
    INNER JOIN software_images si ON si.software_image_id = spsi.software_image_id 
    INNER JOIN software_products sp ON sp.id = spsi.software_product_id 
    LEFT JOIN software_products_software_images_testplan_gate tpg ON tpg.software_products_software_images_id = spsi.Id 
    LEFT JOIN test_suites tp ON tp.id = tpg.testplan_id 
    LEFT JOIN software_products_software_images_testcase_gate tcg ON tcg.software_products_software_images_id = spsi.Id 
    LEFT JOIN test_cases tc ON tc.id = tcg.testcase_id 
    LEFT JOIN test_suites ts ON ts.id = tc.test_suite_id 
    LEFT JOIN software_products_software_images_percentage_gate pg ON pg.software_products_software_images_id = spsi.Id 
    ...........................
    WHERE  pg.active=1 AND tcg.active=1 AND tpg.active=1
    AND si.software_image='NHSS.QSDK.7.0.1' ORDER BY si.software_image, ts.suite_name

Answer 1

我相信您需要将 WHERE 语句的 OR 部分封装在圆括号中，以便它在任何 OR 匹配以及您的其他条件上返回 TRUE。

SELECT<br/>
..........
FROM software_products_software_images spsi
INNER JOIN software_images si ON si.software_image_id = spsi.software_image_id
INNER JOIN software_products sp ON sp.id = spsi.software_product_id
LEFT JOIN software_products_software_images_testplan_gate tpg ON tpg.software_products_software_images_id = spsi.Id
LEFT JOIN test_suites tp ON tp.id = tpg.testplan_id
LEFT JOIN software_products_software_images_testcase_gate tcg ON tcg.software_products_software_images_id = spsi.Id
LEFT JOIN test_cases tc ON tc.id = tcg.testcase_id
LEFT JOIN test_suites ts ON ts.id = tc.test_suite_id
LEFT JOIN software_products_software_images_percentage_gate pg ON pg.software_products_software_images_id = spsi.Id
...........................
WHERE (pg.active=1 OR tcg.active=1 OR tpg.active=1)
AND si.software_image='NHSS.QSDK.7.0.1' ORDER BY si.software_image, ts.suite_name