sql - 在 Sql Server 中对多列使用 GROUP BY 时如何选择最长的字符串

Question

我的下表有重复值。我只想从中获取最大长度的文本。

+----+---------------------------------+-------------------+
| ID | Text                            | no                |
+----+---------------------------------+-------------------+
| 1  | lorem ipsum ipsum               | 8955\5445         |
+----+---------------------------------+-------------------+
| 1  | ipsum                           | 1879668\4554\4554 |
+----+---------------------------------+-------------------+
| 1  | lorem ipsum ipsum               | 5464              |
+----+---------------------------------+-------------------+
| 2  | lorem ipsum ipsum derome        | 13465465\54555\45 |
+----+---------------------------------+-------------------+
| 2  | lorem ipsum derome ipsum derome | 555454            |
+----+---------------------------------+-------------------+

我的查询:

select id,MAX(text),MAX(no) from table1 group by id

结果:

    +----+--------------------------+-----------+
| id | Text                     | no        |
+----+--------------------------+-----------+
| 1  | lorem ipsum ipsum        | 8955\5445 |
+----+--------------------------+-----------+
| 2  | lorem ipsum ipsum derome | 555454    |
+----+--------------------------+-----------+

预期输出:

   +----+--------------------------+-------------------+
| id | Text                     | no                |
+----+--------------------------+-------------------+
| 1  | lorem ipsum ipsum        | 1879668\4554\4554 |
+----+--------------------------+-------------------+
| 2  | lorem ipsum ipsum derome | 13465465\54555\45 |
+----+--------------------------+-------------------+

Answer 1

从您的预期结果来看，您似乎想要最长的文本和最高的数字。

让我们做一些测试数据；

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (ID int, Text varchar(50), no int)
INSERT INTO #TestData (ID, Text, no)
VALUES
(1,'lorem ipsum ipsum',8955)
,(1,'ipsum',6879668)
,(1,'lorem ipsum ipsum',5464)
,(2,'lorem ipsum ipsum derome',63465465)
,(2,'lorem ipsum derome ipsum derome',555454)

我将此作为子查询来分别获取两个最大值。第一个内部联接仅用于过滤，第二个内部联接返回 no 列中的最大值。

SELECT DISTINCT
t.id
,m.Text Text
,MAX(t.no) Number
FROM #TestData t
LEFT JOIN  --Get the max data length
    (
        SELECT DISTINCT
        t.ID
        ,t.Text
        FROM #TestData t
        JOIN 
            (
                SELECT 
                ID
                ,MAX(len(Text)) MaxLen
                FROM #TestData 
                GROUP BY ID
            ) mx 
        ON t.ID = mx.ID
        AND LEN(t.Text) = mx.MaxLen
    ) m 
ON t.ID = m.ID
LEFT JOIN --Get the max no
        (
        SELECT 
        ID
        ,MAX(LEN(no)) MaxNo
        FROM #TestData 
        GROUP BY ID
    ) n
ON t.ID = n.ID
AND LEN(t.no) = n.MaxNo
GROUP BY t.id, m.Text

结果出来了;

id  Text                            MaxNo
1   lorem ipsum ipsum               6879668
2   lorem ipsum derome ipsum derome 63465465