选择唯一身份，然后选择双打之一 [英] Select uniques, and one of the doubles

问题描述

我有一个包含A，B和C列的表. A列可能有重复项.

I have a table with columns A, B and C. Column A might have duplicates.

我需要一个查询，该查询将为我提供一个在A列中具有唯一值的结果集，并且我不在乎它可能需要重复哪些内容.

I need a query that will get me a resultset with unique values in column A, and I don't care which possible duplicate it takes.

我对其余数据一无所知.

I don't know anything beforehand about the rest of the data.

一个例子可能是:

A    B    C
1    8    8
1    7    7
2    10   10

在这种情况下，我想选择:

In this case I'd want to select:

A    B    C
1    x    x
2    10   10

x =选择哪个值都没有关系.

x = It doesn't matter which value it would pick.

亲切的问候，

Matthias Vance

Matthias Vance

修改

我以为我找到了解决方法:

I thought I found my solution with:

SELECT * FROM (
   SELECT * FROM test GROUP BY a
) table_test;

但是那根本不起作用.

这将导致:

[Microsoft][ODBC Excel Driver] Cannot group on fields selected with '*'

推荐答案

尝试一下:

select A, B, C
from test x
where not exists (select *
                  from test y
                  where y.A = x.A
                        and (y.B < x.B or (y.B = x.B and y.C < x.C))
order by A

但是因为它包含相关子查询，所以它可能很慢. (OTOH，从理论上讲，数据库引擎至少有可能将其优化为我在下面显示的内容.)

But since it contains correlated subquery it might be slow. (OTOH it is at least theoretically possible for database engine to optimize it into something I show below.)

SQL之外的东西呢? 您将如何处理结果?

What about something outside SQL? What are you going to do with result?

如果要使用某些程序来处理它，为什么不这样做:

If you are going to process it with some program, why not just get:

select A, B, C from test order by A, B, C

然后执行类似的操作:

prev_a = None
for a, b, c in get_query_result():
    if a != prev_a:
        prev_a = a
        yield (a, b, c)

在您的应用程序中?

我不了解PHP，但我想应该是这样的:

I don't know PHP but I guess it would be something like this:

$query = "SELECT a,b,c FROM test ORDER BY a,b,c";
$result = odbc_exec($connect, $query);
$prev_a = NULL;  # I don't know what you would normally use here in PHP
while (odbc_fetch_row($result)) {
  $a = odbc_result($result, 1);
  if (is_null($prev_a) or $a != $prev_a) { 
    $b = odbc_result($result, 2);
    $c = odbc_result($result, 3);
    print("A = $a, B = $b, C = $c\n");
    $prev_a = $a;
  }
}

这篇关于选择唯一身份，然后选择双打之一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！