我有一张EMP_A表。以下是表值 - ENAME DEPT-NAME指定薪资日期
FARAH RESEARCH ANALYST 30000 Dec 03 , 1991 TOMAR SALES SALESMAN 15000 9月 08 , 2001 SAXENA SALES SALESMAN 12500 9月 28 , 1999 TOMAR SALES SALESMAN 14500 2月 22 , 1997 ANAND RESEARCH CLERK 11000 Jan 12 , 1993如何通过sim找到员工ilar名字,并显示他们的名称,部门和加入数据。 请帮帮我。 我已经尝试过以下查询,但有一些错误。
SELECT ENAME,DESIGNATION,DEPT_NAME,DATE_OF_JOIN,COUNT(*) AS CountOf FROM EMP_A GROUP BY ENAME,DATE_OF_JOIN HAVING COUNT(*)> 1;解决方案
Bittu14, 如果您正在寻找具有相同名称(不相似)的重复条目,您的查询是:
SELECT ENAME,COUNT(*) AS CountOf FROM EMP_A GROUP BY ENAME HAVING COUNT(*)> 1;然后,如果您需要显示这些重复名称的其他字段,您可以考虑将其用作子查询:
SELECT ENAME,DESIGNATION,DEPT_NAME,DATE_OF_JOIN FROM EMP_A WHERE ENAME IN ( SELECT ENAME, COUNT(*) AS CountOf FROM EMP_A GROUP BY ENAME HAVING COUNT(*)> 1 ) ORDER BY ENAMEF。
H i 为您的解决方案创建临时表并插入如下查询记录 选择名称,ROW_NUMBER() OVER ( PARTITION BY base.Name 订单 按名称) AS 重复 来自 EMP_A AS base 其中 FirstName = ' 10Feb2015' 根据解决方案#2 试试这个 选择 e.ENAME,DESIGNATION,DEPT,DATEOFJOIN 来自员工e,( SELECT eName,ROW_NUMBER() OVER ( PARTITION BY ename 订单 按 ename) AS 重复 来自 employee ) AS temp WHERE temp.ENAME = e.Ename 和 duplicate = 2
I have one EMP_A table. Below is the table value - ENAME DEPT-NAME DESIGNATION SALARY DATE-OF-JOIN
FARAH RESEARCH ANALYST 30000 Dec 03, 1991 TOMAR SALES SALESMAN 15000 Sep 08, 2001 SAXENA SALES SALESMAN 12500 Sep 28, 1999 TOMAR SALES SALESMAN 14500 Feb 22, 1997 ANAND RESEARCH CLERK 11000 Jan 12, 1993How I will find the employees with similar names and display their designation, department and data of join. Please help me out. I have tried this below query, but some error is there.
SELECT ENAME, DESIGNATION, DEPT_NAME, DATE_OF_JOIN, COUNT(*) AS CountOf FROM EMP_A GROUP BY ENAME, DATE_OF_JOIN HAVING COUNT(*)>1; 解决方案 Bittu14, If you are looking for duplicate entry with the same name (not similar) your query is: SELECT ENAME, COUNT(*) AS CountOf FROM EMP_A GROUP BY ENAME HAVING COUNT(*)>1;Then, if you need to show additional fields for these duplicate names you may consider using it as subquery:
SELECT ENAME, DESIGNATION, DEPT_NAME, DATE_OF_JOIN FROM EMP_A WHERE ENAME IN ( SELECT ENAME, COUNT(*) AS CountOf FROM EMP_A GROUP BY ENAME HAVING COUNT(*)>1 ) ORDER BY ENAMEF.
Hi for your solution create temp table and insert record like below query select Name,ROW_NUMBER() OVER( PARTITION BY base.Name order by name) AS duplicate from EMP_A AS base where FirstName = '10Feb2015'and after search from main table with join of temp table like in join clause base.name = temp.main and duplicate = 2 by using this step i hope you get your solution thanks Uttam katariya
As per solution #2 try this Select e.ENAME, DESIGNATION, DEPT, DATEOFJOIN from Employee e , ( SELECT eName,ROW_NUMBER() OVER( PARTITION BY ename order by ename) AS duplicate from employee ) AS temp WHERE temp.ENAME = e.Ename and duplicate = 2更多推荐
如何从表中的特定列获取类似值
发布评论