只有一列不同 - 只显示第一个重复行 [英] Distinct on only one column - only display FIRST duplicate row

问题描述

我在 PHP 中使用 SQL 查询来显示用户"表中的 5 列.这是我的代码:

I'm using an SQL query in PHP to display 5 columns from my "users" table. Here is my code for that:

SELECT DISTINCT account_id, full_name, email, login, phone, updated_at, last_request_at, unconfirmed_email FROM $table WHERE id < '300'

基本上，该表有两种类型的用户 - 管理员帐户和我们称之为子用户"的用户 - 他们仍然是用户，但没有管理员权限，并且是由其父管理员帐户创建的.因此，父管理员帐户及其子用户都共享相同的account_id".这是一个示例(对不起，这是垃圾):

Basically, this table has 2 types of user - admin accounts and what we'll call "subusers" - they're still users but they don't have admin privileges, and they were created by their parent admin accounts. Because of this, the parent admin account and its subusers all share the same "account_id" Here's an example (sorry it's rubbish):

| account_id | full_name   | Superhero?  |
| 1          | Batman      |  1          |
| 1          | Robin       |  1          |
| 1          | Magneto     |  0          |
| 2          | Spiderman   |  1          |
| 2          | The Hulk    |  1          |
| 2          | Wolverine   |  1          |
| 3          | Professor X |  1          |
| 4          | Cyclops     |  1          |
| 4          | Shrek       |  0          |
| 4          | Superman    |  1          |
| 4          | Bob         |  0          |

所以你可以假装蜘蛛侠制作了绿巨人和金刚狼的账户.这告诉我蜘蛛侠有一个管理员帐户，因为他是 account_id "2" 的第一个实例.

So you can pretend that Spiderman made The Hulk's and Wolverine's accounts. This tells me that Spiderman has an admin account because he's the first instance of the account_id "2".

如您所见，虽然 full_name 是唯一的，但有许多重复的 account_id，我想对其进行改进，使其仅显示每个 ID 的第一个实例 - 只有 4 个不同的 account_id，所以它应该只显示 4 个条目，如下所示:

So as you can see, while full_name is unique, there are many duplicate account_id's, I would like to refine it so that that it only displays the first instance of each ID - there are only 4 different account_id's so it should only show 4 entries, like so:

| account_id | full_name   | Superhero?  |
| 1          | Batman      |  1          |
| 2          | Spiderman   |  1          |
| 3          | Professor X |  1          |
| 4          | Cyclops     |  1          |

我怎样才能做到这一点?

How can I achieve this?

推荐答案

您可能应该添加另一列.现在可以使用 GROUP BY 子句为每个 account_id 获取不同的记录，但所有非聚合列的结果可能有歧义.您必须有一些您批准的订单或组内的指示符来确定每个 accout_id 的哪个记录是第一".列标记每个组中的哪条记录是第一个查询很简单.没有它，您必须接受一些命令，告诉查询哪个记录是第一".例如 full_name 的字母顺序:

You should probably add another column. Now it is possible to get distinct record for every account_id using GROUP BY clause but results of all nonagreggated columns can be ambigius. You have to have some order you approve or indicator inside group to determine which record for every accout_id is "first". With column marking which record in each group is first query is simple. Without it you have to accept some order telling query which record is "first". On example alphabetical order of full_name:

SELECT account_id, 
       full_name, 
       email, 
       login, 
       phone, 
       updated_at, 
       last_request_at, 
       unconfirmed_email 
  FROM table1 WHERE full_name IN (
    SELECT MIN(full_name) 
      FROM table1 
      GROUP BY account_id 
      WHERE id < '300'
  )

这篇关于只有一列不同 - 只显示第一个重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！