sql - 使用 TSQL 按字段值限制结果

Question

我正在尝试从表中获取结果，以便它们受到相似字段值数量的限制。例如，这里有一些示例记录:

FieldValue (nvarchar)
----------
id1/mode3/path
id2/mode2/path
id3/mode3/path
id4/mode1/path
id5/mode3/path
id6/mode2/path
id7/mode2/path
id8/mode3/path

在这种情况下，我只希望每种模式最多有 2 条记录，因此结果应该是:

id1/mode3/path
id2/mode2/path
id3/mode3/path
id4/mode1/path
id6/mode2/path

我如何在 TSQL (SQL Server 2012) 中执行此操作？

[更新] 注意:id、mode 和 path 不是单独的字段。它们是名为 FieldValue 的字段中的串联文本值(例如“id1/mode3/path”)。

Answer 1

您可以使用窗口函数和字符串操作函数来拆分数据:

WITH cte AS
( SELECT 
       FieldValue
      ,[id]        = LEFT(FieldValue, CHARINDEX('/', FieldValue)-1)
      ,[mode]      = SUBSTRING(FieldValue,
                               CHARINDEX('/', FieldValue)+1,
                               CHARINDEX('/',RIGHT(FieldValue,LEN(FieldValue)
                               -CHARINDEX('/', FieldValue)))-1)
  FROM #tab
), cte2 AS
(
  SELECT FieldValue, r = ROW_NUMBER() OVER (PARTITION BY mode ORDER BY id)
  FROM cte
)
SELECT FieldValue
FROM cte2
WHERE r <= 2
ORDER BY FieldValue;

LiveDemo

输出:

╔════════════════╗
║   FieldValue   ║
╠════════════════╣
║ id1/mode3/path ║
║ id2/mode2/path ║
║ id3/mode3/path ║
║ id4/mode1/path ║
║ id6/mode2/path ║
╚════════════════╝