查詢first_name至少包含2個元音且每個元音出現次數相等的行

我有以下問題：顯示表中列 first_name 包含至少 2 個元音（a、e、i、o、u）的所有行，并且每個元音的出現次數相同。

有效示例：Alexander，“e”出現 2 次，“a”出現 2 次。那是正確的。

無效示例：Jonathan，它有2個元音（a，o），但是“o”出現一次，“a”出現兩次，出現次數不等。

我已經通過計算每個元音解決了這個問題，然后驗證了每個案例（AE、AI、AO 等。很快，2、3、4、5 的每個組合）。有了這個解決方案，我有一個很長的 WHERE。有沒有更短更優雅更簡單的方法？

uj5u.com熱心網友回復：

這就是我在 MS SQL Server 2019 中的 TSQL 中解決它的方法。我知道這不是您想要的。只是一個有趣的嘗試。感謝那。

DROP TABLE IF EXISTS #Samples

SELECT n.Name
INTO   #Samples
FROM
       (
           SELECT 'Ravi' AS Name
           UNION
           SELECT 'Tim'
           UNION
           SELECT 'Timothe'
           UNION
           SELECT 'Ian'
           UNION
           SELECT 'Lijoo'
           UNION
           SELECT 'John'
           UNION
           SELECT 'Jami'
       ) AS n

SELECT   g.Name,
         IIF(MAX (g.Repeat) = MIN (g.Repeat) AND SUM (g.Appearance) >= 2, 'Valid', 'Invalid') AS Validity
FROM
         (
             SELECT      v.value,
                         s.Name,
                         SUM (LEN (s.Name) - LEN (REPLACE (s.Name, v.value, ''))) AS Repeat,
                         SUM (IIF(s.Name LIKE '%'   v.value   '%', 1, 0)) AS Appearance
             FROM        STRING_SPLIT('a,e,i,o,u', ',') AS v
             CROSS APPLY #Samples AS s
             GROUP BY    v.value,
                         s.Name 
         ) AS g
WHERE    g.Repeat > 0
GROUP BY g.Name 

輸出

我們可以用臨時表替換 STRING_SPLIT 以支持較低版本

DROP TABLE IF EXISTS #Vowels

SELECT C.Vowel
INTO   #Vowels
FROM
       (
           SELECT 'a' AS Vowel
           UNION
           SELECT 'e'
           UNION
           SELECT 'i'
           UNION
           SELECT 'o'
           UNION
           SELECT 'u'
       ) AS C

SELECT   g.Name,
         IIF(MAX (g.Repeat) = MIN (g.Repeat) AND SUM (g.Appearance) >= 2, 'Valid', 'Invalid') AS Validity
FROM
         (
             SELECT      v.Vowel,
                         s.Name,
                         SUM (LEN (s.Name) - LEN (REPLACE (s.Name, v.Vowel, ''))) AS Repeat,
                         SUM (IIF(s.Name LIKE '%'   v.Vowel   '%', 1, 0)) AS Appearance
             FROM        #Vowels AS v
             CROSS APPLY #Samples AS s
             GROUP BY    v.Vowel,
                         s.Name
         ) AS g
WHERE    g.Repeat > 0
GROUP BY g.Name

uj5u.com熱心網友回復：

從 Oracle 12 開始，您可以使用：

SELECT name
FROM   table_name
       CROSS JOIN LATERAL(
         SELECT 1
         FROM   (
           -- Step 2: Count the frequency of each vowel
           SELECT letter,
                  COUNT(*) As frequency
           FROM   (
             -- Step 1: Find all the vowels
             SELECT REGEXP_SUBSTR(LOWER(name), '[aeiou]', 1, LEVEL) AS letter
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT(LOWER(name), '[aeiou]')
           )
           GROUP BY letter
         )
         -- Step 3: Filter out names where the number of vowels are
         --   not equal or the vowels do not occur at least twice
         --   and there are not at least 2 different vowels.
         HAVING MIN(frequency) >= 2
         AND    MIN(frequency) = MAX(frequency)
         AND    COUNT(*)       >= 2
       );

其中，對于樣本資料：

CREATE TABLE table_name (name) AS
SELECT 'Alexander' FROM DUAL UNION ALL
SELECT 'Johnaton' FROM DUAL UNION ALL
SELECT 'Anna' FROM DUAL;

輸出：

姓名
亞歷山大

db<>在這里擺弄

標籤：sql 甲骨文

上一篇：加入時sql無效識別符號

下一篇：Athena/PrestoEscape下劃線