mysql - 在sql中，当我在将其中一列作为主键后使用 'group by'时，它没有在所需的组中显示输出？

Question

alter table dependent\n
add constraint con_second primary key(Bdate);\n

由于你们中的一些人要求提供完整的查询，我在这里给出。这是检索拥有 2 个以上女儿的所有员工的 Fname 的查询。希望现在已经清楚了。并且此处不能使用 order by。

Blockquote

   Select Fname from EMPLOYEE2 where Ssn in (select Essn from dependent 
   group by Essn,relationship having COUNT(relationship)>2 AND relationship="daughter";

987654321   Spouse
333445555   Spouse
123456789   Spouse
333445555   Son
333445555   Daughter
123456789   Son
123456789   Daughter

可以看出，上表既不按 Essn 也不按关系分组。据我所知，它按 Bdate 的升序显示输出，而 Bdate 是此处的主键。

Answer 1

分组依据和排序依据是两个完全不同的东西。在您的查询中，分组依据位于两个选定的字段上，除非存在重复的行，否则不会产生任何影响。例如，如果我复制您的查询的最后一行

drop table if exists t;
create table t
(essn int, relationship varchar(20));
insert into t values
(987654321 ,  'Spouse'),
(333445555 ,  'Spouse'),
(123456789 ,  'Spouse'),
(333445555 ,  'Son'),
(333445555 ,  'Daughter'),
(123456789 ,  'Son'),
(123456789 ,  'Daughter'),
(123456789 ,  'Daughter');

select Essn,relationship from t group by Essn,relationship;

删除重复项

+-----------+--------------+
| Essn      | relationship |
+-----------+--------------+
| 123456789 | Daughter     |
| 123456789 | Son          |
| 123456789 | Spouse       |
| 333445555 | Daughter     |
| 333445555 | Son          |
| 333445555 | Spouse       |
| 987654321 | Spouse       |
+-----------+--------------+
7 rows in set (0.00 sec)

使用 DISTINCT 可以(而且更好)实现同样的效果

select distinct essn,relationship from t;
+-----------+--------------+
| essn      | relationship |
+-----------+--------------+
| 987654321 | Spouse       |
| 333445555 | Spouse       |
| 123456789 | Spouse       |
| 333445555 | Son          |
| 333445555 | Daughter     |
| 123456789 | Son          |
| 123456789 | Daughter     |
+-----------+--------------+
7 rows in set (0.00 sec)

请注意，在这两种情况下，结果都没有特定的顺序 - 您需要使用 ORDER BY 来实现该结果

select distinct essn,relationship from t order by essn,relationship;
+-----------+--------------+
| essn      | relationship |
+-----------+--------------+
| 123456789 | Daughter     |
| 123456789 | Son          |
| 123456789 | Spouse       |
| 333445555 | Daughter     |
| 333445555 | Son          |
| 333445555 | Spouse       |
| 987654321 | Spouse       |
+-----------+--------------+
7 rows in set (0.00 sec)

添加键仅有助于提高您仍需要排序依据的性能。如果您的数据中没有重复项，则 DISTINCT 子句(和 GROUP BY)是无关紧要的，您只需要一个 order by。