在STRING

本文介绍了在STRING_AGG中产生DISTINCT值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在SQL Server 2017中使用STRING_AGG函数.我想创建与COUNT(DISTINCT <column>)相同的效果.我尝试了STRING_AGG(DISTINCT <column>,',')，但这不是合法的语法.

I'm using the STRING_AGG function in SQL Server 2017. I'd like to create the same effect as COUNT(DISTINCT <column>). I tried STRING_AGG(DISTINCT <column>,',') but that is not legal syntax.

我想知道是否有T-SQL解决方法.这是我的示例:

I'd like to know if there is a T-SQL work-around. Here is my sample:

WITH Sitings AS ( SELECT * FROM (VALUES (1, 'Florida', 'Orlando', 'bird'), (2, 'Florida', 'Orlando', 'dog'), (3, 'Arizona', 'Phoenix', 'bird'), (4, 'Arizona', 'Phoenix', 'dog'), (5, 'Arizona', 'Phoenix', 'bird'), (6, 'Arizona', 'Phoenix', 'bird'), (7, 'Arizona', 'Phoenix', 'bird'), (8, 'Arizona', 'Flagstaff', 'dog') ) F (ID, State, City, Siting) ) SELECT State, City, COUNT(DISTINCT Siting) [# Of Types], STRING_AGG(Siting,',') Animals FROM Sitings GROUP BY State, City

上面的结果如下:

+---------+-----------+--------------+-------------------------+ | State | City | # Of Types | Animals | +---------+-----------+--------------+-------------------------+ | Arizona | Flagstaff | 1 | dog | | Florida | Orlando | 2 | dog,bird | | Arizona | Phoenix | 2 | bird,bird,bird,dog,bird | +---------+-----------+--------------+-------------------------+

输出正是我想要的，除了我希望为Phoenix Phoenix列出的级联动物"是DISTINCT，像这样:

The output is exactly what I want, except I want the concatenated "Animals" listed for Phoenix Arizona to be DISTINCT, like this:

+---------+-----------+--------------+--------------------+ | State | City | # Of Types | Animals | +---------+-----------+--------------+--------------------+ | Arizona | Flagstaff | 1 | dog | | Florida | Orlando | 2 | dog,bird | | Arizona | Phoenix | 2 | bird,dog | +---------+-----------+--------------+--------------------+

有什么想法吗?

使用更大的真实数据集时，出现"Animals"列超过8000个字符的错误.

When I use my real data set, which is much larger, I get an error about the "Animals" column exceeding 8000 characters.

我认为我的问题与这一个，除了我的示例更简单.

My question I think is the same as this one, except my example is much simpler.

推荐答案

这是一种实现方法.

由于您还希望获得不同的计数，因此只需将行分组两次即可完成.第一个GROUP BY将删除重复项，第二个GROUP BY将产生最终结果.

Since you want the distinct counts as well, it can be done simply by grouping the rows twice. The first GROUP BY will remove duplicates, the second GROUP BY will produce the final result.

WITH Sitings AS ( SELECT * FROM (VALUES (1, 'Florida', 'Orlando', 'bird'), (2, 'Florida', 'Orlando', 'dog'), (3, 'Arizona', 'Phoenix', 'bird'), (4, 'Arizona', 'Phoenix', 'dog'), (5, 'Arizona', 'Phoenix', 'bird'), (6, 'Arizona', 'Phoenix', 'bird'), (7, 'Arizona', 'Phoenix', 'bird'), (8, 'Arizona', 'Flagstaff', 'dog') ) F (ID, State, City, Siting) ) ,CTE_Animals AS ( SELECT State, City, Siting FROM Sitings GROUP BY State, City, Siting ) SELECT State, City, COUNT(1) AS [# Of Sitings], STRING_AGG(Siting,',') AS Animals FROM CTE_Animals GROUP BY State, City ORDER BY State ,City ;

结果

+---------+-----------+--------------+----------+ | State | City | # Of Sitings | Animals | +---------+-----------+--------------+----------+ | Arizona | Flagstaff | 1 | dog | | Arizona | Phoenix | 2 | bird,dog | | Florida | Orlando | 2 | bird,dog | +---------+-----------+--------------+----------+

如果仍然收到超过8000个字符的错误消息，则将值强制转换为varchar(max)在STRING_AGG之前.

If you are still getting an error message about exceeding 8000 characters, then cast the values to varchar(max) before STRING_AGG.

类似

STRING_AGG(CAST(Siting AS varchar(max)),',') AS Animals