SQLite多表相似主机名字段合并查询可行性问询

Absolutely! You can pull this off in SQLite3 easily—here's a step-by-step approach that handles both merging hostnames from your different tables into a single column and deduplicating those similar values (like machine001 vs machine001.domainname).

Step 1: Extract Clean Hostnames (Remove Domain Suffixes) #

SQLite doesn’t have a built-in SUBSTRING_INDEX function like MySQL, but we can use INSTR (to find the position of the first dot) and SUBSTR (to slice the string) to strip off any domain suffixes. For values without a dot, we just use the original string directly.

Here’s the reusable pattern for each field:

CASE
  WHEN INSTR([field_name], '.') > 0 THEN SUBSTR([field_name], 1, INSTR([field_name], '.') - 1)
  ELSE [field_name]
END AS clean_hostname

Step 2: Merge All Tables & Deduplicate #

Use UNION (which automatically removes duplicate rows) to combine the cleaned hostnames from all four tables into a single column. If you ever need to keep duplicates (unlikely for a subquery), swap UNION for UNION ALL and add DISTINCT in the outer query.

Full Query Example #

SELECT clean_hostname
FROM (
  -- Pull cleaned hostnames from table a
  SELECT
    CASE
      WHEN INSTR(a.name, '.') > 0 THEN SUBSTR(a.name, 1, INSTR(a.name, '.') - 1)
      ELSE a.name
    END AS clean_hostname
  FROM a
  UNION
  -- Pull cleaned hostnames from table b
  SELECT
    CASE
      WHEN INSTR(b.name, '.') > 0 THEN SUBSTR(b.name, 1, INSTR(b.name, '.') - 1)
      ELSE b.name
    END AS clean_hostname
  FROM b
  UNION
  -- Pull cleaned hostnames from table c
  SELECT
    CASE
      WHEN INSTR(c.hostname, '.') > 0 THEN SUBSTR(c.hostname, 1, INSTR(c.hostname, '.') - 1)
      ELSE c.hostname
    END AS clean_hostname
  FROM c
  UNION
  -- Pull cleaned hostnames from table d
  SELECT
    CASE
      WHEN INSTR(d.addr_host, '.') > 0 THEN SUBSTR(d.addr_host, 1, INSTR(d.addr_host, '.') - 1)
      ELSE d.addr_host
    END AS clean_hostname
  FROM d
) AS all_hostnames
-- Optional: Filter out empty or null values
WHERE clean_hostname IS NOT NULL AND clean_hostname != '';

Usage as a Subquery #

You can plug this directly into a subquery for your main logic, like:

SELECT *
FROM your_target_table
WHERE target_hostname IN (
  -- Paste the full query above here
);

Quick Notes #

• If your values have multiple dots (e.g., machine001.sub.domain.com), the above logic grabs everything before the first dot—this is standard for hostnames. If you need to grab the segment before the last dot (a rare edge case), you’d need a recursive CTE, but that’s overkill for most use cases.
• UNION is efficient here because it deduplicates as it combines datasets. For extremely large tables, UNION ALL + DISTINCT might perform similarly, but UNION is cleaner for this use case.

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