我在检索DISTINCT记录时遇到问题。场景如下:现在我的查询是
Select a,b,c from TABLE_NAME
(COMPLEX_INNER JOIN LOGIC)
我想要前两列值应该唯一的记录。我知道可以做到
GROUP BY
条款。因此查询将变为
Select a,b,c from TABLE_NAME
(COMPLEX_INNER JOIN LOGIC)
GROUP BY a,b
但由于c并没有出现在SQL服务器的聚合函数或组中,所以出现了以下错误:
选择列表中的列"c"无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中
您可以将查询放入CTE中,并使用row_number()函数来确定要获取哪些行
类似这样的东西:
with C as
(
Select a,b,c,
row_number() over(partition by a, b order by SomeColumn) as rn
from TABLE_NAME
--(COMPLEX_INNER JOIN LOGIC)
)
select a, b, c
from C
where rn = 1
工作样品:
declare @T table
(
a int,
b int,
c int
)
insert into @T values
(1, 1, 1),
(1, 1, 2),
(2, 2, 1),
(2, 2, 2)
;with C as
(
select a, b, c,
row_number() over(partition by a, b order by c) as rn
from @T
)
select a, b, c
from C
where rn = 1
结果:
a b c
----------- ----------- -----------
1 1 1
2 2 1
像这样使用
select c,q.a,q.b from TABLE_NAME inner join
(
Select a,b from TABLE_NAME
(COMPLEX_INNER JOIN LOGIC)
GROUP BY a,b) q
on q.a=TABLE_NAME.a