对特定列使用DISTINCT [英] Using DISTINCT for specific columns

问题描述

select distinct  employee_id, first_name,  commission_pct, department_id from
employees;

当我使用上述查询时，它会导致上述所有属性的独特组合。由于employee_id（作为雇员的主键）是唯一的，因此查询将产生表中的所有行。

When I use the above query it results in distinct combination of all the attributes mentioned. As employee_id (being the primary key for employees) is unique, the query results in producing all the rows in the table.

我想要一个具有不同结果集Commission_pct和department_id的组合。那么查询应该如何形成。当我尝试在中间添加 DISTINCT 作为

I want to have a result set that has distinct combination of commission_pct and department_id. so how the query should be formed. When I tried to include the DISTINCT in the middle as

select   employee_id, first_name, distinct commission_pct, department_id from
    employees; 

这会导致错误

ORA-00936-missing expression

如何形成查询结果只有佣金和Department_id的组合。表来自oracle的 HR 模式。

How to form a query which results have only distinct combination of commission and department_id.The table is from HR schema of oracle.

推荐答案

您无法请求。您不能选择所有雇员ID，而只能拥有不同的Commission_pct和department_id。

What you request is impossible. You cannot select all the employee ids but have only distinct commission_pct and department_id.

因此，请仔细考虑一下，您想显示的内容：

So think it over, what you want to show:

• 所有唯一的佣金，部门编号？


• 所有唯一的佣金，部门编号和相关雇员的数量？


• 所有不同的Commission_pct，department_id和相关雇员用逗号分隔吗？


• 所有雇员，但是当Commission_pct和department_id与前一行相同时为空吗？

• All distinct commission_pct, department_id only?
• All distinct commission_pct, department_id and the number of relevant employees?
• All distinct commission_pct, department_id and the relevant employees comma separated?
• All employees, but with nulls when commission_pct and department_id are the same as in the line before?

第一个可以用DISTINCT解决。第二和第三个使用GROUP BY（加上count或listagg）。最后一个可以通过解析函数LAG来解决。

The first can be solved with DISTINCT. The second and third with GROUP BY (plus count or listagg). The last would be solved with the analytic function LAG.

这篇关于对特定列使用DISTINCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！