当前位置： 文江博客话题详情

MySQL：选择 DISTINCT / UNIQUE，但返回所有列？

发布于 2024-11-09 09:40:27 字数 225 浏览 7 评论 0原文 

SELECT DISTINCT field1, field2, field3, ......
FROM table;

我正在尝试完成以下 SQL 语句，但我希望它返回所有列。
这可能吗？

像这样的东西：

SELECT DISTINCT field1, * 
FROM table;
原文 
SELECT DISTINCT field1, field2, field3, ......
FROM table;

I am trying to accomplish the following SQL statement, but I want it to return all columns.
Is this possible?

Something like this:

SELECT DISTINCT field1, * 
FROM table;

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（19）

尴尬癌患者 2024-11-16 09:40:27

您正在寻找一个 group by: ，

select *
from table
group by field1

有时可以使用不同的 on 语句来编写：

select distinct on field1 *
from table

但是，在大多数平台上，上述两种方法都不起作用，因为其他列上的行为未指定。 （第一个适用于 MySQL，如果您正在使用 MySQL。）

您可以获取不同的字段并坚持每次选择一个任意行。

在某些平台（例如 PostgreSQL、Oracle、T-SQL）上，这可以直接使用窗口函数来完成：

select *
from (
   select *,
          row_number() over (partition by field1 order by field2) as row_number
   from table
   ) as rows
where row_number = 1

在其他平台（MySQL、SQLite）上，您需要编写子查询来将整个表与其自身连接起来（示例），因此不推荐。

You're looking for a group by:

select *
from table
group by field1

Which can occasionally be written with a distinct on statement:

select distinct on field1 *
from table

On most platforms, however, neither of the above will work because the behavior on the other columns is unspecified. (The first works in MySQL, if that's what you're using.)

You could fetch the distinct fields and stick to picking a single arbitrary row each time.

On some platforms (e.g. PostgreSQL, Oracle, T-SQL) this can be done directly using window functions:

select *
from (
   select *,
          row_number() over (partition by field1 order by field2) as row_number
   from table
   ) as rows
where row_number = 1

On others (MySQL, SQLite), you'll need to write subqueries that will make you join the entire table with itself (example), so not recommended.

回复 原文
唠甜嗑 2024-11-16 09:40:27

从您问题的措辞中，我了解到您希望为给定字段选择不同的值，并为每个此类值选择列出同一行中的所有其他列值。大多数 DBMS 不允许使用 DISTINCT 或 GROUP BY 这样做，因为结果尚未确定。

可以这样想：如果您的 field1 出现多次，则将列出 field2 的值（假设您的 field1 具有相同的值） code> 位于两行中，但这两行中有两个不同的 field2 值）。

但是，您可以使用聚合函数（明确地用于您想要显示的每个字段）并使用 GROUP BY 而不是 DISTINCT：

SELECT field1, MAX(field2), COUNT(field3), SUM(field4), ....
FROM table GROUP BY field1

From the phrasing of your question, I understand that you want to select the distinct values for a given field and for each such value to have all the other column values in the same row listed. Most DBMSs will not allow this with neither DISTINCT nor GROUP BY, because the result is not determined.

Think of it like this: if your field1 occurs more than once, what value of field2 will be listed (given that you have the same value for field1 in two rows but two distinct values of field2 in those two rows).

You can however use aggregate functions (explicitely for every field that you want to be shown) and using a GROUP BY instead of DISTINCT:

SELECT field1, MAX(field2), COUNT(field3), SUM(field4), ....
FROM table GROUP BY field1
回复 原文
生生漫 2024-11-16 09:40:27

如果我正确理解你的问题，它与我刚刚遇到的问题类似。您希望能够将 DISTINCT 的可用性限制到指定字段，而不是将其应用于所有数据。

如果您在没有聚合函数的情况下使用 GROUP BY，则 GROUP BY 的任何字段都将是您的 DISTINCT 字段。

如果您进行查询：

SELECT * from table GROUP BY field1;

它将显示基于 field1 的单个实例的所有结果。

例如，如果您有一个包含名称、地址和城市的表。一个人有多个地址记录，但你只想要这个人的一个地址，你可以这样查询：

SELECT * FROM persons GROUP BY name;

结果将是该名字只出现一个实例及其地址，而另一个则被省略结果表。注意：如果您的字段具有原子值，例如名字、姓氏，您希望按这两个值进行分组。

SELECT * FROM persons GROUP BY lastName, firstName;

因为如果两个人具有相同的姓氏，并且您仅按姓氏进行分组，那么其中一个人将从结果中省略。你需要考虑这些事情。希望这有帮助。

If I understood your problem correctly, it's similar to one I just had. You want to be able limit the usability of DISTINCT to a specified field, rather than applying it to all the data.

If you use GROUP BY without an aggregate function, which ever field you GROUP BY will be your DISTINCT filed.

If you make your query:

SELECT * from table GROUP BY field1;

It will show all your results based on a single instance of field1.

For example, if you have a table with name, address and city. A single person has multiple addresses recorded, but you just want a single address for the person, you can query as follows:

SELECT * FROM persons GROUP BY name;

The result will be that only one instance of that name will appear with its address, and the other one will be omitted from the resulting table. Caution: if your fileds have atomic values such as firstName, lastName you want to group by both.

SELECT * FROM persons GROUP BY lastName, firstName;

because if two people have the same last name and you only group by lastName, one of those persons will be omitted from the results. You need to keep those things into consideration. Hope this helps.

回复 原文
〆一缕阳光ご 2024-11-16 09:40:27

这是一个非常好的问题。我已经在这里阅读了一些有用的答案，但也许我可以添加更准确的解释。

只要不查询其他信息，使用 GROUP BY 语句减少查询结果的数量就很容易。假设您有下表“位置”。

--country-- --city--
 France      Lyon
 Poland      Krakow
 France      Paris
 France      Marseille
 Italy       Milano

现在查询

SELECT country FROM locations
GROUP BY country

将导致：

--country--
 France
 Poland
 Italy

但是，以下查询

SELECT country, city FROM locations
GROUP BY country

...在 MS SQL 中引发错误，因为您的计算机如何知道您想要读取三个法国城市“里昂”、“巴黎”或“马赛”中的哪一个“法国”右侧的字段？

为了更正第二个查询，您必须添加此信息。一种方法是使用函数 MAX() 或 MIN()，在所有候选值中选择最大或最小值。 MAX() 和 MIN() 不仅适用于数值，还可以比较字符串值的字母顺序。

SELECT country, MAX(city) FROM locations
GROUP BY country

将导致：

--country-- --city--
 France      Paris
 Poland      Krakow
 Italy       Milano

或：

SELECT country, MIN(city) FROM locations
GROUP BY country

将导致：

--country-- --city--
 France      Lyon
 Poland      Krakow
 Italy       Milano

只要您可以从字母（或数字）顺序的两端选择值，这些函数就是一个很好的解决方案。但如果情况并非如此呢？让我们假设您需要一个具有特定特征的值，例如以字母“M”开头。现在事情变得复杂了。

到目前为止，我能找到的唯一解决方案是将整个查询放入子查询中，并手动在其外部构造附加列：

SELECT
     countrylist.*,
     (SELECT TOP 1 city
     FROM locations
     WHERE
          country = countrylist.country
          AND city like 'M%'
     )
FROM
(SELECT country FROM locations
GROUP BY country) countrylist

将导致：

--country-- --city--
 France      Marseille
 Poland      NULL
 Italy       Milano

That's a really good question. I have read some useful answers here already, but probably I can add a more precise explanation.

Reducing the number of query results with a GROUP BY statement is easy as long as you don't query additional information. Let's assume you got the following table 'locations'.

--country-- --city--
 France      Lyon
 Poland      Krakow
 France      Paris
 France      Marseille
 Italy       Milano

Now the query

SELECT country FROM locations
GROUP BY country

will result in:

--country--
 France
 Poland
 Italy

However, the following query

SELECT country, city FROM locations
GROUP BY country

...throws an error in MS SQL, because how could your computer know which of the three French cities "Lyon", "Paris" or "Marseille" you want to read in the field to the right of "France"?

In order to correct the second query, you must add this information. One way to do this is to use the functions MAX() or MIN(), selecting the biggest or smallest value among all candidates. MAX() and MIN() are not only applicable to numeric values, but also compare the alphabetical order of string values.

SELECT country, MAX(city) FROM locations
GROUP BY country

will result in:

--country-- --city--
 France      Paris
 Poland      Krakow
 Italy       Milano

or:

SELECT country, MIN(city) FROM locations
GROUP BY country

will result in:

--country-- --city--
 France      Lyon
 Poland      Krakow
 Italy       Milano

These functions are a good solution as long as you are fine with selecting your value from the either ends of the alphabetical (or numeric) order. But what if this is not the case? Let us assume that you need a value with a certain characteristic, e.g. starting with the letter 'M'. Now things get complicated.

The only solution I could find so far is to put your whole query into a subquery, and to construct the additional column outside of it by hands:

SELECT
     countrylist.*,
     (SELECT TOP 1 city
     FROM locations
     WHERE
          country = countrylist.country
          AND city like 'M%'
     )
FROM
(SELECT country FROM locations
GROUP BY country) countrylist

will result in:

--country-- --city--
 France      Marseille
 Poland      NULL
 Italy       Milano
回复 原文
两仪 2024-11-16 09:40:27
SELECT  c2.field1 ,
        field2
FROM    (SELECT DISTINCT
                field1
         FROM   dbo.TABLE AS C
        ) AS c1
        JOIN dbo.TABLE AS c2 ON c1.field1 = c2.field1

SELECT  c2.field1 ,
        field2
FROM    (SELECT DISTINCT
                field1
         FROM   dbo.TABLE AS C
        ) AS c1
        JOIN dbo.TABLE AS c2 ON c1.field1 = c2.field1
回复 原文
︶￣淡然 2024-11-16 09:40:27

很好的问题@aryaxt——你可以说这是一个很好的问题，因为你5年前就问过这个问题，而我今天偶然发现了这个问题，试图找到答案！

我只是尝试编辑接受的答案以包含此内容，但万一我的编辑没有包含在内：

如果您的表不是那么大，并且假设您的主键是自动递增整数，您可以执行以下操作：

SELECT 
  table.*
FROM table
--be able to take out dupes later
LEFT JOIN (
  SELECT field, MAX(id) as id
  FROM table
  GROUP BY field
) as noDupes on noDupes.id = table.id
WHERE
  //this will result in only the last instance being seen
  noDupes.id is not NULL

Great question @aryaxt -- you can tell it was a great question because you asked it 5 years ago and I stumbled upon it today trying to find the answer!

I just tried to edit the accepted answer to include this, but in case my edit does not make it in:

If your table was not that large, and assuming your primary key was an auto-incrementing integer you could do something like this:

SELECT 
  table.*
FROM table
--be able to take out dupes later
LEFT JOIN (
  SELECT field, MAX(id) as id
  FROM table
  GROUP BY field
) as noDupes on noDupes.id = table.id
WHERE
  //this will result in only the last instance being seen
  noDupes.id is not NULL
回复 原文
心凉 2024-11-16 09:40:27

尝试

SELECT table.* FROM table 
WHERE otherField = 'otherValue'
GROUP BY table.fieldWantedToBeDistinct
limit x

Try

SELECT table.* FROM table 
WHERE otherField = 'otherValue'
GROUP BY table.fieldWantedToBeDistinct
limit x
回复 原文
千鲤 2024-11-16 09:40:27

您可以使用 WITH 子句来完成此操作。

例如：

WITH c AS (SELECT DISTINCT a, b, c FROM tableName)
SELECT * FROM tableName r, c WHERE c.rowid=r.rowid AND c.a=r.a AND c.b=r.b AND c.c=r.c

这还允许您仅选择在 WITH 子句查询中选择的行。

You can do it with a WITH clause.

For example:

WITH c AS (SELECT DISTINCT a, b, c FROM tableName)
SELECT * FROM tableName r, c WHERE c.rowid=r.rowid AND c.a=r.a AND c.b=r.b AND c.c=r.c

This also allows you to select only the rows selected in the WITH clauses query.

回复 原文
陌若浮生 2024-11-16 09:40:27

对于SQL Server，您可以使用dense_rank 和附加窗口函数来获取指定列上具有重复值的所有行和列。这是一个示例...

with t as (
    select col1 = 'a', col2 = 'b', col3 = 'c', other = 'r1' union all
    select col1 = 'c', col2 = 'b', col3 = 'a', other = 'r2' union all
    select col1 = 'a', col2 = 'b', col3 = 'c', other = 'r3' union all
    select col1 = 'a', col2 = 'b', col3 = 'c', other = 'r4' union all
    select col1 = 'c', col2 = 'b', col3 = 'a', other = 'r5' union all
    select col1 = 'a', col2 = 'a', col3 = 'a', other = 'r6'
), tdr as (
    select 
        *, 
        total_dr_rows = count(*) over(partition by dr)
    from (
        select 
            *, 
            dr = dense_rank() over(order by col1, col2, col3),
            dr_rn = row_number() over(partition by col1, col2, col3 order by other)
        from 
            t
    ) x
)

select * from tdr where total_dr_rows > 1

这是对 col1、col2 和 col3 的每个不同组合进行行计数。

For SQL Server you can use the dense_rank and additional windowing functions to get all rows AND columns with duplicated values on specified columns. Here is an example...

with t as (
    select col1 = 'a', col2 = 'b', col3 = 'c', other = 'r1' union all
    select col1 = 'c', col2 = 'b', col3 = 'a', other = 'r2' union all
    select col1 = 'a', col2 = 'b', col3 = 'c', other = 'r3' union all
    select col1 = 'a', col2 = 'b', col3 = 'c', other = 'r4' union all
    select col1 = 'c', col2 = 'b', col3 = 'a', other = 'r5' union all
    select col1 = 'a', col2 = 'a', col3 = 'a', other = 'r6'
), tdr as (
    select 
        *, 
        total_dr_rows = count(*) over(partition by dr)
    from (
        select 
            *, 
            dr = dense_rank() over(order by col1, col2, col3),
            dr_rn = row_number() over(partition by col1, col2, col3 order by other)
        from 
            t
    ) x
)

select * from tdr where total_dr_rows > 1

This is taking a row count for each distinct combination of col1, col2, and col3.

回复 原文
北方。的韩爷 2024-11-16 09:40:27
select min(table.id), table.column1
from table 
group by table.column1

select min(table.id), table.column1
from table 
group by table.column1
回复 原文
静待花开 2024-11-16 09:40:27
SELECT *
FROM tblname
GROUP BY duplicate_values
ORDER BY ex.VISITED_ON DESC
LIMIT 0 , 30

在 ORDER BY 中，我刚刚在这里放置了示例，您也可以在其中添加 ID 字段

SELECT *
FROM tblname
GROUP BY duplicate_values
ORDER BY ex.VISITED_ON DESC
LIMIT 0 , 30

in ORDER BY i have just put example here, you can also add ID field in this

回复 原文
谁与争疯 2024-11-16 09:40:27

在其他地方找到了这个，但这是一个有效的简单解决方案：

 WITH cte AS /* Declaring a new table named 'cte' to be a clone of your table */
 (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
 FROM MyTable /* Selecting only unique values based on the "id" field */
 )
 SELECT * /* Here you can specify several columns to retrieve */
 FROM cte
 WHERE rn = 1

Found this elsewhere here but this is a simple solution that works:

 WITH cte AS /* Declaring a new table named 'cte' to be a clone of your table */
 (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
 FROM MyTable /* Selecting only unique values based on the "id" field */
 )
 SELECT * /* Here you can specify several columns to retrieve */
 FROM cte
 WHERE rn = 1
回复 原文
千秋岁 2024-11-16 09:40:27

这样只需 1 个查询即可获得 2 个唯一列
从 '{path}' 组中选择不同的 col1,col2 by col1,col2
如果需要，您可以增加列

In this way can get 2 unique column with 1 query only
select Distinct col1,col2 from '{path}' group by col1,col2
you can increase your columns if need

回复 原文
十年九夏 2024-11-16 09:40:27

将 GROUP BY 添加到要检查重复项的字段
您的查询可能看起来像

SELECT field1, field2, field3, ......   FROM table GROUP BY field1

将检查 field1 以排除重复记录

，或者您可能会查询像

SELECT *  FROM table GROUP BY field1

field1 的重复记录已从 SELECT 中排除

Add GROUP BY to field you want to check for duplicates
your query may look like

SELECT field1, field2, field3, ......   FROM table GROUP BY field1

field1 will be checked to exclude duplicate records

or you may query like

SELECT *  FROM table GROUP BY field1

duplicate records of field1 are excluded from SELECT

回复 原文
独自唱情﹋歌 2024-11-16 09:40:27

只需将所有字段包含在 GROUP BY 子句中即可。

Just include all of your fields in the GROUP BY clause.

回复 原文
一抹淡然 2024-11-16 09:40:27

可以通过内部查询来完成

$query = "SELECT * 
            FROM (SELECT field
                FROM table
                ORDER BY id DESC) as rows               
            GROUP BY field";

It can be done by inner query

$query = "SELECT * 
            FROM (SELECT field
                FROM table
                ORDER BY id DESC) as rows               
            GROUP BY field";
回复 原文
迷爱 2024-11-16 09:40:27
SELECT * from table where field in (SELECT distinct field from table)

SELECT * from table where field in (SELECT distinct field from table)
回复 原文
哭了丶谁疼 2024-11-16 09:40:27

如果表中所有三列的值都是唯一的，则 SELECT DISTINCT FIELD1、FIELD2、FIELD3 FROM TABLE1 有效。

例如，如果您的名字有多个相同的值，但所选列中的姓氏和其他信息不同，则该记录将包含在结果集中。

SELECT DISTINCT FIELD1, FIELD2, FIELD3 FROM TABLE1 works if the values of all three columns are unique in the table.

If, for example, you have multiple identical values for first name, but the last name and other information in the selected columns is different, the record will be included in the result set.

回复 原文
软糖 2024-11-16 09:40:27

如果多行中的 field1 具有相同的值，我建议使用

SELECT  * from table where field1 in 
(
  select distinct field1 from table
)

这种方式，所有记录都将被返回。

I would suggest using

SELECT  * from table where field1 in 
(
  select distinct field1 from table
)

this way if you have the same value in field1 across multiple rows, all the records will be returned.

回复 原文
~没有更多了~

关于作者

少女净妖师

暂无简介

0 文章
0 评论
673 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

已经忘了多久

文章 0 评论 0

15867725375

文章 0 评论 0

LonelySnow

文章 0 评论 0

走过海棠暮

文章 0 评论 0

轻许诺言

文章 0 评论 0

信馬由缰

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文