MySQL中的NOT DISTINCT查询 [英] NOT DISTINCT query in mySQL

问题描述

有人要求我在一个简单的员工数据库列上为此创建查询:

I have been asked to create a query for this on a simple employee database columns include:

ninumber-名字-姓氏-地址-SuperVisorNiNumber

ninumber - first name - last name - address - SuperVisorNiNumber

所有员工和主管都在同一张表中，并按其编号进行引用.我被要求构建的查询是:

The employees and supervisors are all held in the same table and are referenced by their ninumbers. The query I've been asked to build is:

v.查找NI编号，雇员的姓氏和名字以及主管与该主管共享的主管的NI编号，以及雇员和主管在部门8中的工作.您将在实践2的查询vi中两次引用雇员关系.结果应显示在标题为雇员NI号"，名字"，姓氏"和主管NI号"的列中.

v. Find the NI Numbers, first and last names of employees and NI numbers of supervisors where the employees share that supervisor and both employees and supervisors work in department 8. You will refer to the employee relation twice was done in query vi of Practical 2. Your results should be presented in columns with the following titles ‘Employee NI number’, ‘First Name’, ‘Last Name’ and ‘Supervisor NI Number’.

因此，我创建了此查询:

Therefore I created this query:

SELECT e1.ninumber,
       e1.fname,
       e1.minit,
       e1.lname,
       e1.address,
       e1.superNiNumber,
       COUNT(*) AS nrOfOccurences
FROM   employee AS e1,
       employee AS e2
WHERE  e1.dno = 8
   AND e1.superNiNumber = e2.ninumber
   AND e2.dno = 8
GROUP  BY e1.superNiNumber
HAVING COUNT(*) > 1

我无法进行明确的查询来解决问题的这一部分-员工在哪里共享该主管".该查询返回行的分组，这又隐藏了我要显示的某些行.

I couldn't do a not distinct query to work out this part of the question - "where the employees share that supervisor". This query returns a grouping of the rows which in turn hides some of the rows I want to show.

我的问题是:我的查询对这个问题是否正确，是否可以在mySQL中执行NON DISTINCT查询以使数据库返回所有字段，而不是将它们分组在一起.

My question is: Is my query correct for the question and can I do a NON DISTINCT query in mySQL to get the database to return all of the fields instead of grouping them together.

我的查询无法查询结果

NInumber    fname  minit    lname   address    supervisorNiNum     number of occerences
666666601   Jill    J   Jarvis  6234 Lincoln, Antrim, UK    666666600   2
666666607   Gerald  D   Small   122 Ball Street, Letterkenny, IRL   666666602   3
666666604   Billie  J   King    556 WAshington, Antrim, UK  666666603   2

谢谢.

推荐答案

在您的结果表列说明中，没有看到minit, address and number of occurrences.因此，我会将您的选择简化为:

In your result table column description, I see no minit, address and number of occurrences. Therefore I would simplify your select to:

SELECT e1.ninumber,
       e1.fname,
       e1.lname,
       e1.superNiNumber,
FROM   employee AS e1,
       employee AS e2
WHERE  e1.dno = 8
       AND e1.superNiNumber = e2.ninumber
       AND e2.dno = 8
       and (select count(*) from employee e3
            where e3.superNiNumber = e1.superNiNumber) > 1;

这篇关于MySQL中的NOT DISTINCT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！