sql - 使用 PostgreSQL 批量验证电话号码

Question

这是我的 SQL:

SELECT
    countries.locl_ctry_id,
    countries.icc,
    countries.active,

    networks.locl_ntwrk_id,
    networks.locl_ctry_id,

    numberings.locl_ntwrk_id,
    numberings.ndc,
    numberings.size

FROM countries

LEFT JOIN networks
    ON networks.locl_ctry_id = countries.locl_ctry_id

LEFT JOIN numberings
    ON numberings.locl_ntwrk_id = networks.locl_ntwrk_id

WHERE
    countries.active = 'true'
AND numberings.locl_ntwrk_id NOTNULL
AND CONCAT(countries.icc, numberings.ndc) 
  LIKE LEFT('381645554330', CHAR_LENGTH(CONCAT(countries.icc, numberings.ndc)))
AND LENGTH('381645554330') = numberings.size

我想为一批数字运行此脚本，例如:

• 381645554330
• 381629000814
• 381644446555
• 38975300155
• 38975604099
• 38976330923
• 38977772090
• 38978250177
• 38970333730
• 38971388262
• 38972228855

在这里查看数据库结构:http://sqlfiddle.com/#!17/13ce29/27

它需要验证号码的Prefix 以及Length。

有什么建议可以实现吗？

Answer 1

将这批数字放在联合所有子查询中。

SELECT
    countries.locl_ctry_id,
    countries.icc,
    countries.active,

    networks.locl_ntwrk_id,
    networks.locl_ctry_id,

    numberings.locl_ntwrk_id,
    numberings.ndc,
    numberings.size

FROM countries

LEFT JOIN networks
    ON networks.locl_ctry_id = countries.locl_ctry_id

LEFT JOIN numberings
    ON numberings.locl_ntwrk_id = networks.locl_ntwrk_id

JOIN ( select '381645554330' as num
       union all
       select '38976330923‬‬‬‬'
       union all
       select '38975300155‬‬‬' ) batch_numbers 
  ON CONCAT(countries.icc, numberings.ndc) 
  LIKE LEFT(batch_numbers.num, CHAR_LENGTH(CONCAT(countries.icc, numberings.ndc)))
  AND LENGTH(batch_numbers.num) = numberings.size

WHERE
    countries.active = 'true'
AND numberings.locl_ntwrk_id NOTNULL