跨表统一筛选条件下无JOIN的多表单列SELECT语句实现咨询

首先得指出你原来的SQL存在的问题：你引用了u、h、l这三个表的列，但这些表并没有出现在FROM或者JOIN子句里，数据库会直接抛出“未知列/表”的错误，这是首先要修正的点。

回到你的核心需求：从四张表各取一列，每张表有独立的筛选条件，且都要匹配customer_code = 'test_customer'，同时因为各表数据行数不同，不想用JOIN产生重复数据。这里分两种场景给你方案：

场景1：需要四个独立的结果集（每个表的数据单独展示） #

如果只是想分别获取每张表符合条件的列数据，不需要把它们放在同一行，用UNION ALL是最直接的方式，它会把四个表的结果拼接起来，不会产生重复数据（因为每个表的结果都是独立的）。你可以给每个结果加一个标识列，方便区分数据来源：

SELECT 'services' AS source_table, s.service_id AS value
FROM services s
LEFT JOIN db.av_product ap ON s.product_id = ap.id
WHERE s.customer_code LIKE 'test_customer'
  AND s.parent_id IS NULL
  AND s.active = 0
  AND ap.sku NOT REGEXP 'fakeregex'

UNION ALL

SELECT 'users' AS source_table, u.id AS value
FROM users u
WHERE u.customer_code LIKE 'test_customer'
  AND (u.support_user != 1 OR u.support_user IS NULL)

UNION ALL

SELECT 'hardware' AS source_table, h.mac_address AS value
FROM hardware h
WHERE h.customer_code LIKE 'test_customer'
  AND h.hardware_id NOT IN ('44','45')

UNION ALL

SELECT 'locations' AS source_table, l.id AS value
FROM locations l
WHERE l.customer_code LIKE 'test_customer'
  AND l.active = '1';

场景2：需要将四列并排显示（每行对应各表的一条数据） #

如果你希望把四个表的列放在同一行展示，又不想因为JOIN产生笛卡尔积式的重复，那可以用行号关联的方式：给每个表的符合条件的数据生成一个行号，然后通过行号做FULL JOIN，这样每个表的每条数据只会出现一次，行数不足的表对应的列会补NULL：

WITH services_data AS (
    SELECT s.service_id, ROW_NUMBER() OVER () AS row_num
    FROM services s
    LEFT JOIN db.av_product ap ON s.product_id = ap.id
    WHERE s.customer_code LIKE 'test_customer'
      AND s.parent_id IS NULL
      AND s.active = 0
      AND ap.sku NOT REGEXP 'fakeregex'
),
users_data AS (
    SELECT u.id AS user_id, ROW_NUMBER() OVER () AS row_num
    FROM users u
    WHERE u.customer_code LIKE 'test_customer'
      AND (u.support_user != 1 OR u.support_user IS NULL)
),
hardware_data AS (
    SELECT h.mac_address, ROW_NUMBER() OVER () AS row_num
    FROM hardware h
    WHERE h.customer_code LIKE 'test_customer'
      AND h.hardware_id NOT IN ('44','45')
),
locations_data AS (
    SELECT l.id AS location_id, ROW_NUMBER() OVER () AS row_num
    FROM locations l
    WHERE l.customer_code LIKE 'test_customer'
      AND l.active = '1'
)
SELECT sd.service_id, ud.user_id, hd.mac_address, ld.location_id
FROM services_data sd
FULL JOIN users_data ud ON sd.row_num = ud.row_num
FULL JOIN hardware_data hd ON COALESCE(sd.row_num, ud.row_num) = hd.row_num
FULL JOIN locations_data ld ON COALESCE(sd.row_num, ud.row_num, hd.row_num) = ld.row_num;

补充说明 #

• 第一种方案适合你需要分别查看每个表的目标数据，结果清晰且无重复；
• 第二种方案适合你需要把四个维度的数据按行对齐展示，避免了传统JOIN带来的重复数据问题，但要注意行号是按数据返回顺序生成的，如果你需要特定的排序逻辑，可以在ROW_NUMBER()的OVER()子句里加上ORDER BY（比如OVER(ORDER BY s.service_id)）。

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