T-SQL | 字符串“操作”和聚合

Question

我有以下情景。

源表1。

CREATE TABLE #Table1 
(
     Div varchar(10), 
     Dept varchar(10), 
     States varchar(10)
)

INSERT INTO #Table1
   SELECT 'Div1','Dept1','CA,NV,TX'
   UNION ALL
   SELECT 'Div2','Dept2','MI,OH,IN'
   UNION ALL
   SELECT 'Div3','Dept2','NY,NJ,PA'
   UNION ALL
   SELECT 'Div4','Dept1',NULL

来源表格2

CREATE TABLE #Table2 
(
    Div varchar(10), 
    Dept varchar(10), 
    States varchar(10)
)

INSERT INTO #Table2
   SELECT 'Div1','Dept1','CA'
   UNION ALL
   SELECT 'Div1','Dept1','NV, TX'
   UNION ALL
   SELECT 'Div1','Dept1','TX, CA'
   UNION ALL
   SELECT 'Div1','Dept1','CA, NV'
   UNION ALL
   SELECT 'Div2','Dept2','MI, OH'
   UNION ALL
   SELECT 'Div2','Dept2','MI, IN'
   UNION ALL
   SELECT 'Div2','Dept2','OH'
   UNION ALL
   SELECT 'Div3','Dept2','NY, NJ, PA'

期望的输出

CREATE TABLE #Table3 
(
    Div varchar(10), 
    Dept varchar(10), 
    States varchar(50)
)

INSERT INTO #Table3
SELECT 'Div1','Dept1','CA - (3), NV - (2), TX - (2)'
UNION ALL
SELECT 'Div2','Dept2','MI - (2), OH - (2), IN - (1)'
UNION ALL
SELECT 'Div3','Dept2','NY - (1), NJ - (1), PA - (1)'
UNION ALL
SELECT 'Div4','Dept1',NULL

SELECT * FROM #Table1
SELECT * FROM #Table2
SELECT * FROM #Table3

DROP TABLE #Table1
DROP TABLE #Table2
DROP TABLE #Table3

中译英：

SQLFIDDLE

目标：基于#Table1和#Table2，在Div和Dept字段上连接两个表，然后聚合States字段中不同状态的计数，并创建一个输出，其中包括Div、Dept和States，并在每个状态旁边打印出该状态的唯一计数。

我不确定如何实现这一点。 我正在尝试使用LIKE，但无法弄清楚如何使其动态。 我会继续尝试看看能否解决问题。 我想在这里发布这个问题，看看是否可以得到一些帮助。

谢谢

更新：

期望输出

Div     Dept    States
Div1    Dept1   CA - (3), NV - (2), TX - (2)
Div2    Dept2   MI - (2), OH - (2), IN - (1)
Div3    Dept2   NY - (1), NJ - (1), PA - (1)
Div4    Dept1   NULL

- 007

---

你能在问题中添加你想要的输出吗？ - Code Different

@ZoffDino 期望的输出是 #Table3 数据集。 - Lamak

不应该将多个值存储在单个字段中，如果进行规范化，这将更加容易。 - Hart CO

我猜状态计数只基于Table2中出现的内容？ - Code Different

“States”列应该被规范化。 - mxix

Answer 1

首先，您需要将#Temp1和#Temp2中的连接值拆分。有多种方法可以做到这一点，我将使用Aaron Bertrand在这篇精彩的博客中描述的数字表方法。因此，我们需要一个数字表，可以通过以下方式完成：

;WITH n AS
(
    SELECT  x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
)
SELECT Number = x
INTO #Numbers
FROM n
WHERE x BETWEEN 1 AND 8000;

然后，您需要实际执行拆分操作，然后使用组合方法对结果进行分组拼接：

;WITH T1 AS
(
    SELECT *
    FROM #Table1 T
    OUTER APPLY (SELECT Item = SUBSTRING(T.States, Number,
                                         CHARINDEX(',',T.States + ',', Number) - 
                                         Number)
                 FROM #Numbers
                 WHERE Number <= CONVERT(INT, LEN(T.States))
                 AND SUBSTRING(',' + T.States, Number, LEN(',')) = ',') N
), T2 AS
(
    SELECT *
    FROM #Table2 T
    OUTER APPLY (SELECT Item = SUBSTRING(T.States, Number,
                                         CHARINDEX(', ',T.States + ', ', Number) - 
                                         Number)
                 FROM #Numbers
                 WHERE Number <= CONVERT(INT, LEN(T.States))
                 AND SUBSTRING(', ' + T.States, Number, LEN(', ')) = ', ') N
), T3 AS
(
    SELECT T1.Div, T1.Dept, T1.Item, COUNT(*) N
    FROM T1 
    LEFT JOIN T2
        ON T1.Div = T2.Div
        AND T1.Dept = T2.Dept
        AND T1.Item = T2.Item
    GROUP BY T1.Div, T1.Dept, T1.Item
)
SELECT  A.Div, 
        A.Dept, 
        States = STUFF((SELECT  ',' + CONVERT(VARCHAR(20), Item) + 
                                ' - (' + CAST(N AS VARCHAR(4)) + ')'
                        FROM T3 
                        WHERE Div = A.Div
                        AND Dept = A.Dept
                    FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)'),1,1,'')
FROM T3 A
ORDER BY Div, Dept, Item

结果如下：

╔══════╦═══════╦════════════════════════════╗
║ Div  ║ Dept  ║           States           ║
╠══════╬═══════╬════════════════════════════╣
║ Div1 ║ Dept1 ║ CA - (3),NV - (2),TX - (2) ║
║ Div1 ║ Dept1 ║ CA - (3),NV - (2),TX - (2) ║
║ Div1 ║ Dept1 ║ CA - (3),NV - (2),TX - (2) ║
║ Div2 ║ Dept2 ║ IN - (1),MI - (2),OH - (2) ║
║ Div2 ║ Dept2 ║ IN - (1),MI - (2),OH - (2) ║
║ Div2 ║ Dept2 ║ IN - (1),MI - (2),OH - (2) ║
║ Div3 ║ Dept2 ║ NJ - (1),NY - (1),PA - (1) ║
║ Div3 ║ Dept2 ║ NJ - (1),NY - (1),PA - (1) ║
║ Div3 ║ Dept2 ║ NJ - (1),NY - (1),PA - (1) ║
║ Div4 ║ Dept1 ║ NULL                       ║
╚══════╩═══════╩════════════════════════════╝

- Lamak

---

感谢 @Lamak 的帮助！真的非常感激。干杯！ - 007

Answer 2

虽然您的要求很苛刻，但作为开发者，我们必须使用现有的工具。以下是一种广泛使用公共表达式（CTE）的解决方案：

;WITH
    CTE1 AS
    (
        SELECT      Div, Dept,
                    REPLACE(States,' ','') + ',' AS States
        FROM        Table2
    ),
    CTE2 AS
    (
        SELECT      c1.Div, c1.Dept,
                    LEFT(c1.States,CHARINDEX(',', c1.States)-1)                 AS IndividualState,
                    RIGHT(c1.States,LEN(c1.States)-CHARINDEX(',', c1.States))   AS RemainingStates
        FROM        CTE1    c1
        UNION ALL
        SELECT      c2.Div, c2.Dept,
                    LEFT(c2.RemainingStates,CHARINDEX(',', c2.RemainingStates)-1),
                    RIGHT(c2.RemainingStates,LEN(c2.RemainingStates) - CHARINDEX(',', c2.RemainingStates))
        FROM        CTE2    c2
        WHERE       LEN(c2.RemainingStates) > 0
    ),
    CTE3 AS
    (
        SELECT      Div, Dept,
                    IndividualState,
                    COUNT(*)            AS StateCount
        FROM        CTE2
        GROUP BY    Div, Dept, IndividualState
    ),
    CTE4 AS
    (
        SELECT      t1.Div, t1.Dept,
                    (
                        SELECT  c3.IndividualState + ' - (' + CONVERT(varchar(10),c3.StateCount) + '), ' 
                        FROM    CTE3 c3
                        WHERE   c3.Div = t1.Div AND c3.Dept = t1.Dept
                        FOR XML PATH('')
                    )       AS States
        FROM        Table1  t1
    )

SELECT  Div, Dept,
        LEFT(States, LEN(States) - 1) AS States
FROM    CTE4

说明

1. CTE1 清理 Table2 中的数据：去除空格，末尾加逗号
2. CTE2 进行规范化处理
3. CTE3 进行计数
4. CTE4 进行最终组装，将 CA | 3 放入 CA - (3), ...

最后的 SELECT 去除末尾逗号以使输出更整洁。

为了更好地理解每一步，您可以将最终的 SELECT 替换为 SELECT * FROM CTE1、SELECT * FROM CTE2 等。

- Code Different

Answer 3

理想情况下，这些数据应该被规范化，因为这很混乱。考虑到你现在只能使用这种结构，我认为最好的方法是使用一个州的驱动表或使用其中一种可用的分割函数来分离出分隔符值，然后使用它，将每个州放在自己的行中：

;WITH cte AS (SELECT DISTINCT b.Div,b.Dept,a.abbrs 
                FROM #States a
                RIGHT JOIN #Table1 b
                 ON ','+REPLACE(b.States,' ','')+',' LIKE '%,'+a.abbrs+',%'
              )
     ,cte2 AS (SELECT b.Div,b.Dept,a.abbrs 
                FROM #States a
                JOIN #Table2 b
                 ON ','+REPLACE(b.States,' ','')+',' LIKE '%,'+a.abbrs+',%'
              )
     ,cte3 AS (SELECT a.Div,a.Dept,a.abbrs,CAST(COUNT(b.abbrs)AS VARCHAR(25)) CT 
                FROM  cte a
                LEFT JOIN cte2 b
                 ON a.Dept = b.Dept
                 AND a.Div = b.Div
                 AND a.abbrs = b.abbrs
                GROUP BY a.div,a.dept,a.abbrs
              )
SELECT DISTINCT
      Div,Dept
      ,STUFF((SELECT DISTINCT ',' +   abbrs+'-('+CT+')'
                                    FROM cte3 b                         
                                    WHERE a.Div = b.Div
                                      AND a.Dept = b.Dept
                                    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                                    ,1,1,'')

FROM  cte3 a

示例: SQL Fiddle

注意:

• cte1 - 从table1创建一个不同的div/dept/state列表
• cte2 - 从table2创建所有div/dept/state的列表
• cte3 - 对div/dept/state进行聚合以获取计数

输出结果为:

|  DIV |  DEPT |               STATES |
|------|-------|----------------------|
| Div1 | Dept1 | CA-(3),NV-(2),TX-(2) |
| Div2 | Dept2 | IN-(1),MI-(2),OH-(2) |
| Div3 | Dept2 | NJ-(1),NY-(1),PA-(1) |
| Div4 | Dept1 |               (null) |

更新了代码片段以包含您的 NULL 行并添加了输出。

- Hart CO

---

感谢@Goat CO的帮助，真的非常感激。干杯！ - 007