如何从表中的特定列获取类似值 [英] How to get the similar values from a particular column in a table

问题描述

我有一张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 ，< span class =code-digit> 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; 

解决方案

< blockquote> 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  ENAME 

F。

H i
为您的解决方案创建临时表并插入如下查询记录

 选择名称，ROW_NUMBER（） OVER （ PARTITION   BY  base.Name 订单 按名称） AS 重复
 来自 EMP_A  AS  base 
 其中 FirstName = '  10Feb2015' 

和主表搜索后加入临时表如加入条款

base.name = temp.main和duplicate = 2



使用此步骤我希望你得到你的解决方案

谢谢

Uttam katariya

根据解决方案＃2

试试这个

 选择 e.ENAME，DESIGNATION，DEPT，DATEOFJOIN < span class =code-keyword>来自员工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, 1993

How 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 ENAME

F.

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

这篇关于如何从表中的特定列获取类似值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！