SQL Server单SELECT无GROUP BY新增列统计唯一值出现次数

针对你的需求，以下几种可行的SQL Server实现方案，均无需使用GROUP BY，且可以直接封装为视图供Entity Framework查询：

方案1：关联子查询 #

你之前子查询返回1大概率是关联条件未同时匹配name1和name2，修正后的语句如下：

SELECT
    t.name1,
    t.name2,
    t.name3,
    -- 统计当前name1+name2分组下的唯一name3数量
    (SELECT COUNT(DISTINCT sub.name3)
     FROM YourTable sub
     WHERE sub.name1 = t.name1 AND sub.name2 = t.name2) AS UniqueName3Count
FROM YourTable t

方案2：OUTER APPLY #

你之前遇到的Invalid object name 'base'错误，是因为主查询表未正确设置别名或子查询中引用别名错误。修正后的语句：

SELECT
    base.name1,
    base.name2,
    base.name3,
    stats.UniqueName3Count
FROM YourTable base
OUTER APPLY (
    -- 统计当前分组的唯一name3数量
    SELECT COUNT(DISTINCT sub.name3) AS UniqueName3Count
    FROM YourTable sub
    WHERE sub.name1 = base.name1 AND sub.name2 = base.name2
) stats

方案3：窗口函数变通实现 #

SQL Server不支持直接使用COUNT(DISTINCT) OVER(PARTITION BY ...)，但可以通过DENSE_RANK()间接实现：

SELECT
    name1,
    name2,
    name3,
    -- 分组内最大的dense_rank值即为唯一name3的数量
    MAX(rnk) OVER(PARTITION BY name1, name2) AS UniqueName3Count
FROM (
    SELECT
        name1,
        name2,
        name3,
        -- 对每个name1+name2分组内的name3进行不重复排名
        DENSE_RANK() OVER(PARTITION BY name1, name2 ORDER BY name3) AS rnk
    FROM YourTable
) t

封装为视图 #

以上任意方案都可以直接创建为视图，以方案2为例：

CREATE VIEW vw_UniqueName3Stats
AS
SELECT
    base.name1,
    base.name2,
    base.name3,
    stats.UniqueName3Count
FROM YourTable base
OUTER APPLY (
    SELECT COUNT(DISTINCT sub.name3) AS UniqueName3Count
    FROM YourTable sub
    WHERE sub.name1 = base.name1 AND sub.name2 = base.name2
) stats

创建完成后，Entity Framework可以直接将该视图映射为实体类，实现高效查询。

性能优化建议 #

为提升查询效率，建议在YourTable上创建包含name3的非聚集索引：

CREATE NONCLUSTERED INDEX IX_YourTable_Name1_Name2
ON YourTable (name1, name2)
INCLUDE (name3);

内容的提问来源于stack exchange，提问作者user2177283