SQL逗號分隔值比較

我在將Available列中的值與列中的值進行比較時遇到了挑戰Required。它們都用逗號分隔。

我想要實作的是，如果列中的值都在Required列中找到，Available那么它會給我一個匹配，1如果列0中的一個或多個值在列Required中丟失Available

我想在 SQL 中實作這一點。

uj5u.com熱心網友回復：

如果我正確理解了這個問題，則可以選擇一種基于STRING_SPLIT()適當的方法JOIN：

樣本資料：

SELECT *
INTO Data 
FROM (VALUES
   ('One, Two, Three', 'One, Three'),
   ('One, Three',      'Three, Five'),
   ('One, Two, Three', 'Two')
) v (Available, Required)

陳述：

SELECT 
   Available, Required,
   CASE
      WHEN EXISTS (
         SELECT 1
         FROM STRING_SPLIT(Required, ',') s1
         LEFT JOIN STRING_SPLIT(Available, ',') s2 ON TRIM(s1.[value]) = TRIM(s2.[value])
         WHERE s2.[value] IS NULL      
      ) THEN 0
      ELSE 1
   END AS Match
FROM Data

結果：

Available       Required    Match
---------------------------------
One, Two, Three One, Three  1
One, Three      Three, Five 0
One, Two, Three Two         1

uj5u.com熱心網友回復：

Zhorov 解決方案的變體。

它使用基于集合的運算子EXCEPT。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (Available VARCHAR(100), Required VARCHAR(100));
INSERT INTO @tbl (Available, Required) VALUES
('One, Two, Three', 'One, Three'),
('One, Three',      'Three, Five'),
('One, Two, Three', 'Two');
-- DDL and sample data population, end

SELECT t.*
   , [Match] = CASE
      WHEN EXISTS (
        SELECT TRIM([value]) FROM STRING_SPLIT(Required, ',')
        EXCEPT
        SELECT TRIM([value]) FROM STRING_SPLIT(Available, ',')
      ) THEN 0
      ELSE 1
   END
FROM @tbl AS t;

輸出

 ----------------- ------------- ------- 
|    Available    |  Required   | Match |
 ----------------- ------------- ------- 
| One, Two, Three | One, Three  |     1 |
| One, Three      | Three, Five |     0 |
| One, Two, Three | Two         |     1 |
 ----------------- ------------- ------- 

uj5u.com熱心網友回復：

您需要進行交叉連接以查看所有可用值，您的查詢將是：

    SELECT t.*
    ,case when SUM(CASE 
        WHEN t1.Available LIKE '%'   t.Required   '%'
            THEN 1
        ELSE 0
        END) > 0  THEN 1  ELSE 0 END AS [Match_Calculated]
FROM YOUR_TABLE t
CROSS JOIN YOUR_TABLE  t1
GROUP BY t.Available
    ,t.Required
    ,t.Match

這是一個dbfiddle

uj5u.com熱心網友回復：

您可以使用“STRING_SPLIT”來實作您的請求

;with Source as 
(
    select 1 id,'One,Two,Three' Available,'One,Three' Required
    union all
    select 2 id,'One,Three' Available,'Three,Five' Required
    union all
    select 3 id,'One,Two,Three' Available,'Two' Required
)
,AvailableTmp as 
(
    SELECT t.id,
            x.value
       FROM Source t
            CROSS APPLY (SELECT trim(value) value
                                FROM string_split(t.Available, ',')) x
)
,RequiredTmp as 
(
SELECT t.id,
       x.value
       FROM Source t
            CROSS APPLY (SELECT trim(value) value
                                FROM string_split(t.Required, ',')) x
)
,AllMatchTmp as 
(
select a.id
        ,1 Match
    From RequiredTmp a 
        left join AvailableTmp b on a.id=b.id and a.value = b.value 
    group by a.id
    having max(case when b.value is null then 1 else 0 end ) = 0
)
select a.id
        ,a.Available
        ,a.Required
        ,ISNULL(b.Match,0) Match
    from Source a
        left join AllMatchTmp b on a.id = b.id

uj5u.com熱心網友回復：

另一種使用方式STRING_SPLIT

DECLARE @data TABLE (Available VARCHAR(100), [Required] VARCHAR(100), 
                       INDEX IX_data(Available,[Required]));
INSERT @data 
VALUES ('One, Two, Three', 'One, Three'),('One, Three', 'Three, Five'),
       ('One, Two, Three', 'Two');

SELECT
  Available  = d.Available,
  [Required] = d.[Required],
  [Match]    = MIN(f.X)
FROM        @data                                          AS d
CROSS APPLY STRING_SPLIT(REPLACE(d.[Required],' ',''),',') AS split
CROSS APPLY (VALUES(REPLACE(d.[Available],' ','')))        AS cleaned(String)
CROSS APPLY (VALUES(IIF(split.[value] NOT IN 
              (SELECT s.[value] FROM STRING_SPLIT(cleaned.String,',') AS s),0,1))) AS f(X)
GROUP BY    d.Available, d.[Required];

標籤：sql sql服务器 tsql

上一篇：簡單的嵌入式Tomcat10示例

下一篇：使用SQLServer的唯一HASHID限制為20個字符