SQL查询需求：筛选所有站点所有者均选Yes的Site URL

To fetch the Site URLs where every associated Site Owner has Delete Site set to "Yes" (and exclude any URL that has even one "No" or NULL entry), here are two straightforward approaches you can use:

Approach 1: Using NOT EXISTS #

This query checks each Site URL to ensure there are no rows where Delete Site is either "No" or NULL:

SELECT DISTINCT `Site URL`
FROM your_table_name
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name AS sub
    WHERE sub.`Site URL` = your_table_name.`Site URL`
      AND (`Delete Site` = 'No' OR `Delete Site` IS NULL)
);

Approach 2: Using GROUP BY and HAVING #

Here we group the data by Site URL and verify that the number of invalid entries (No or NULL) is zero:

SELECT `Site URL`
FROM your_table_name
GROUP BY `Site URL`
HAVING COUNT(CASE WHEN `Delete Site` != 'Yes' OR `Delete Site` IS NULL THEN 1 END) = 0;

How this works #

• For your sample data, both queries will return only google.com—since all its owners have "Yes" for Delete Site.
• The NOT EXISTS method is efficient because it stops searching as soon as it finds an invalid entry for a URL.
• The GROUP BY approach aggregates the data, counts how many entries aren't "Yes" (including NULLs), and ensures that count is zero.

Just replace your_table_name with the actual name of your table. If your SQL dialect doesn’t use backticks for column names with spaces, use double quotes instead (e.g., "Site URL").

内容的提问来源于stack exchange，提问作者Bebo Santana