通过一个不同的列选择记录的子组 [英] Select a subgroup of records by one distinct column

问题描述

很抱歉，如果以前已经回答过这个问题，但是所有相关问题似乎都与我的目的不符。

Sorry if this has been answered before, but all the related questions didn't quite seem to match my purpose.

我有一个表格，如下所示：

I have a table that looks like the following:

ID  POSS_PHONE    CELL_FLAG
=======================
1   111-111-1111  0
2   222-222-2222  0
2   333-333-3333  1
3   444-444-4444  1

我只想为插入内容选择不同的ID值，但是我不在乎哪个特定的ID得到

I want to select only distinct ID values for an insert, but I don't care which specific ID gets pulled out of the duplicates.

例如（有效的SELECT是）：

For Example(a valid SELECT would be):

1   111-111-1111  0
2   222-222-2222  0
3   444-444-4444  1

在拥有CELL_FLAG列之前，我只是这样使用聚合函数：

Before I had the CELL_FLAG column, I was just using an aggregate function as so:

SELECT ID, MAX(POSS_PHONE)
FROM TableA
GROUP BY ID    

但是我做不到：

SELECT ID, MAX(POSS_PHONE), MAX(CELL_FLAG)...

因为我会丢失行内的完整性，对吗？

because I would lose integrity within the row, correct?

我已经看到一些使用CTE的类似示例，但是再一次，没有什么合适的。

I've seen some similar examples using CTEs, but once again, nothing that quite fit.

所以也许CTE可以解决这个问题，或者某种类型的自联接子查询？我现在处于封锁状态，所以看不到任何其他解决方案。

So maybe this is solvable by a CTE or some type of self-join subquery? I'm at a block right now, so I can't see any other solutions.

推荐答案

只需将您的汇总子查询并加入它：

Just get your aggregation in a subquery and join to it:

SELECT a.ID, sub.Poss_Phone, CELL_FLAG
FROM TableA as a
INNER JOIN (SELECT ID, MAX(POSS_PHONE) as [Poss_Phone]
            FROM TableA
            GROUP BY ID) Sub
    ON Sub.ID = a.ID and SUB.Poss_Phone = A.Poss_Phone

这将保持未聚合字段之间的完整性，但仍会给您 MAX（Poss_Phone）每个 ID 。

This will keep integrity between your non-aggregated fields but still give you the MAX(Poss_Phone) per ID.

这篇关于通过一个不同的列选择记录的子组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！