显示每位员工的不同记录 [英] Display distinct record for each employee

问题描述

我有两个表约会应用程序和教职员工。它们是具有相同emp ID的多条记录

I have two tables appointment app and faculty fac. they are multiple record with same emp ids

1. 我需要通过 app.employeeid = fac.empid加入两个表（这里我将获得重复的记录）


2. 和 app.begindate< = now和app.enddate> = now和app.percent> ; .50 （这里我也会得到重复的记录，因为empid（具有相同id的多个记录）可以具有相同的开始日期，结束日期和> .50）


3. max（app.amt）（在这里我也会得到重复的记录，因为有可能empid（多个具有相同id的记录）具有相同的amt）。最终在最后，我想选择distinct（max（app.amt））以仅获得单个记录为空。

1. I need to join two tables by app.employeeid=fac.empid (here i will get duplicate records)
2. and app.begindate<=now and app.enddate>=now and app.percent>.50 (here also i will get duplicate records because empid(multiple records with same id) can have same begindate,end date and >.50 )
3. max(app.amt) (here also i will get duplicate records because there can be a chance where empid(multiple records with same id) have same amt) . Finally at the end i want to select distinct(max(app.amt)) to get only single record for empid.

查询：

select max(app.amt) ,fac.email,fac.employee_id,fac.empid,fac.first_name,fac.last_name,fac.department_id,fac.titlecode,app.begindate,app.enddate,app.percent,
app.title_name,app.department_name from faculty fac, appointment app where 
app.employeeid=fac.empid and app.begindate <= now() and app.enddate >= now() and app.percent>.50  group by fac.empid
/

我遇到了一个问题，我正在获取max（app.amt），但相应的列值与max amt记录未正确匹配。值是随机的。我希望显示确切的相应值。

i am having an issue i am getting max(app.amt) but corresponding column values are not properly matched with max amt record.the other column values are taking in random.I want the exact corresponding values to display.

约会表中几个文件的采样日期：

sample date in appointment table for few fileds:

SELECT app.amt, fac.email, fac.employee_id, fac.empid, 
                 fac.first_name, fac.last_name, fac.department_id, fac.titlecode,
                 app.begindate, app.enddate, app.percent,
                 app.title_name, app.department_name 
FROM appointment app
LEFT JOIN appointment app2                                    -- self-join to locate  max
ON app.employee_id = app2.employee_id AND app.amt < app2.amt --  of app.amt
INNER JOIN faculty fac ON app.employee_id=fac.empid           -- join to fac
WHERE app2.amt IS NULL                                        -- isolate rows with     max(app.amt)
AND app.begindate <= NOW() 
AND app.enddate >= NOW() 
AND app.percent>.50 group by fac.empid


employee_id begindate enddate     amt        percent  department_name
5528        7/1/2011  9/30/2011   0          1        m1
5528        7/1/2011  9/30/2011   193100     1        m1
5528        10/1/2011 6/30/2013   79000      1        m1
5528        10/1/2011 6/30/2013   118500     1        m2
5528        10/1/2011 6/30/2013   0          1        m2

推荐答案

不仅要选择MAX（ ），但与之对应的行，对于每个employee_id，通常使用LEFT JOIN将表与其自身连接。 （这也称为最大组，在此标记下，关于stackoverflow的问题很多。）

To select not only a MAX(column) but the row corresponding to it, for each employee_id, you usually use a LEFT JOIN to join the table to itself. (This is also known as "greatest-n-per-group" and there are a number of questions in this vein on stackoverflow under this tag).

例如为每个 employee_id 及其相应的行选择 max（app.amt ）做：

e.g. to select the max(app.amt) per employee_id with its corresponding row you would do:

SELECT app.*
FROM appointment app
LEFT JOIN appointment app2
 ON app.employee_id = app2.employee_id AND app.amt < app2.amt
WHERE app2.amt IS NULL;

为什么这样做？从本质上讲，这是用 amt 选择行，对于该员工，没有 amt

Why does this work? In essence, this says "pick the row with the amt for which there is no higher amt for that employee", for each employee.

LEFT JOIN约会app2 ON app.employee_id = app2.employee_id 加入 app 放在 employee_id 上（请注意，这就是您要 GROUP BY ）。这将生成一个约会表，其中每个 employee_id amt c $ c>。

The LEFT JOIN appointment app2 ON app.employee_id=app2.employee_id joins app to itself on the employee_id (note this is what you wanted to GROUP BY). This produces a table of appointment with every possible pair of amt for each employee_id.

然后，我们还指定 app.amt< app2.amt 。因此，我们仅显示（app.amt，app2.amt）对的行，前者少于后者。
因为这是左联接，所以如果我们碰巧找到一个 app.amt 可以在 app2 中找不到具有相同employee_id和 greater app2.amt ，然后将 app2.amt 设置为NULL。当我们找到该员工的最棒的 app.amt 时，就会精确地发生。

Then, we also specify that app.amt < app2.amt. So we only display rows where the (app.amt,app2.amt) pair has the former less than the latter. Since this is a LEFT JOIN, if we happen to find an app.amt for which we can't find a corresponding row in app2 with the same employee_id and a greater app2.amt, then app2.amt is set to NULL. This occurs precisely when we've found the greatest app.amt for that employee.

因此，使用此<< c $ c> LEFT JOIN 调整您的查询以获得相应的行方法：

So, adapt your query using this "LEFT JOIN to myself to get the corresponding row" method:

SELECT app.amt, fac.email, fac.employee_id, fac.empid, 
                     fac.first_name, fac.last_name, fac.department_id, fac.titlecode,
                     app.begindate, app.enddate, app.percent,
                     app.title_name, app.department_name 
FROM appointment app
LEFT JOIN appointment app2                                    -- self-join to locate max
 ON app.employee_id = app2.employee_id AND app.amt < app2.amt --  of app.amt
INNER JOIN faculty fac ON app.employee_id=fac.empid           -- join to fac
WHERE app2.amt IS NULL                                        -- isolate rows with max(app.amt)
  AND app.begindate <= NOW() 
  AND app.enddate >= NOW() 
  AND app.percent>.50

所以，唯一改变的是：

• 添加了 LEFT JOIN约会app2 ... WHERE app2.amt为NULL 以找到最大 app.amt 每个员工身份。


• 更改了您的 FROM应用程序，fac .. WHERE app.employeeid = fac .empid 转换为 JOIN （请参见下面的注释）


• 不需要 GROUP BY 或 MAX（app.amt）- INNER JOIN 很重要

• added a LEFT JOIN appointment app2 ... WHERE app2.amt IS NULL to locate the row with the max app.amt for each employeeid.
• changed your FROM app, fac .. WHERE app.employeeid=fac.empid into a JOIN (see note below)
• no need for the GROUP BY or MAX(app.amt) -- the INNER JOIN takes care of that.

关于转换 FROM应用程序的说明，请注意...在何处加入条件到从应用程序LEFT JOIN fac ON join_condition ：与从多个表中进行选择并加入 WHERE 的联接相比，联接的效率更高。

Note on converting the FROM app, fac ... WHERE join_condition to FROM app LEFT JOIN fac ON join_condition: a join tends to be more efficient than selecting from multiple tables and joining in the WHERE.

什么类型使用的联接数（ app 到 fac ）可能会影响您的结果；要恢复以前的行为，请使用 INNER JOIN 。这将仅显示雇员在 app 表和 fac中都存在的行表。

What type of join you use (app to fac) may make a difference to your results; to recover your former behaviour use INNER JOIN. This will only display rows where the employee exists in both the app table and the fac table.

使用 LEFT JOIN 的效率要比 INNER JOIN ，结果将是相同的 除非您的约会表中有一名员工，但该员工在教职员工表中没有条目。然后，对于所有 fac.xxx 字段，它们将在结果列表中显示为 NULL 。

Using a LEFT JOIN is slightly more efficient than the INNER JOIN, and the results will be identical unless there is an employee in your appointments table who does not have an entry in the faculty table. Then they will be displayed in the results list with NULL for all the fac.xxx fields.

使用正确加入将显示教师表中的每个员工，无论他们是否有约会-如果没有约会它们仍将显示在您的结果中，只是所有与约会相关的字段都使用 NULL 。

Using a RIGHT JOIN will show every employee from the faculty table regardless of whether they have appointments or not -- if they have no appointments they'll still be shown in your results, just with NULL for all their appointment-related fields.

JOIN的细微差别（啊，但这是另一个问题）。

Just subtle differences in the JOINs (ahh, but that's another question).

这篇关于显示每位员工的不同记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！