选择具有两列的记录具有相似的值 [英] Select records having two columns have similar value

问题描述

大家好，

我从sql server数据库中选择记录时有查询。我有下表：





VendorCode |   BankAccountNo |   PAN_Number

12345         | 100185265255  | ASLOK7856F

45263          | 100185265255  | ASLOK7856F

45252         | 152623565656  | ASLOK7856F







我想选择供应商代码12345和45263的记录。因为这两家供应商有相同的PAN_Number和BankAccountNo相同。请帮我写一个给定场景的选择查询。



提前完成。

解决方案

假设你的桌子被称为供应商

  SELECT  a.VendorCode  FROM 供应商 AS  a  WHERE   EXISTS  
（
  SELECT  b.BankAccountNo，b.PAN_Number  FROM 供应商 AS  b 
  WHERE  a.BankAccountNo = b.BankAccountNo  AND  a.PAN_Number = b.PAN_Number  AND  a.VendorCode<> b.VendorCode 
） 

这是一个简单的解决方案：

（我认为你的表名是供应商）



<前郎=sql> 选择 v2.vendorcode
来自供应商v1，供应商v2
其中 v1.vendorcode<> v2.vendorcode 和
v1.bankaccountno = v2。 bankaccountno 和
v1.pan_number = v2.pan_number

好​​运

有几种方法可以达到这个目的。



1）使用 HAVING [ ^ ]子句

2）使用子查询，例如：

  DECLARE   @ tmp   TABLE （VendorCode  INT ，BankAccountNo  V ARCHAR （ 30 ），PAN_Number  VARCHAR （ 30 ））
 
  INSERT   INTO   @ tmp （VendorCode，BankAccountNo，PAN_Number）
  SELECT   12345 ，'  100185265255'，'  ASLOK7856F'  UNION   ALL  
  SELECT   45263 ，'  100185265255'，'  ASLOK7856F'  UNION   ALL  
  SEL ECT   45252 ，'  152623565656'，'  ASLOK7856F' 
 
  SELECT  * 
  FROM   @ tmp  
 
  SELECT  * 
  FROM （
  SELECT  VendorCode，BankAccountNo，
（ SELECT  COUNT（B.BankAccountNo） FROM   @ tmp   AS  B  WHERE  B. BankAccountNo = T.BankAccountNo） AS  CountOfBankAccountNo，
 PAN_Number，
（ SELECT  COUNT （PAN_Number） FROM   @ tmp   AS  P  WHERE  P.PAN_Number = T.PAN_Number） AS  CountOfPAN_Number 
  FROM   @ tmp   AS  T 
） AS  Src 
  WHERE  CountOfBankAccountNo> 1 或 CountOfPAN_Number> 1 
  ORDER   BY  VendorCode 

3）使用常用表格表达式 [ ^ ]（ CTE [ ^ ]）

4）使用 PATH with FOR XML [ ^ ]



你会在MSDN上找到更多：

常见解决方案：查找和/或删除重复行 [ ^ ]

使用DISTINCT消除重复项 [ ^ ]

和CodeProject知识库：

要查找重复的行表 [ ^ ]

在SQL中查找重复值服务器 [ ^ ]

如何在表格中查找重复值。 [ ^ ]

Hi All,
I have query while selecting records from sql server database. I have following table:


VendorCode |  BankAccountNo |  PAN_Number
12345          | 100185265255   | ASLOK7856F
45263          | 100185265255   | ASLOK7856F
45252          | 152623565656   | ASLOK7856F



I want to select record of vendorcode 12345 and 45263. Because these two vendors have same PAN_Number and same BankAccountNo. Please help me to write a select query for given scenario.

Thanx in advance.

解决方案

Assuming your table is called vendors

SELECT a.VendorCode FROM vendors AS a WHERE EXISTS
(
   SELECT b.BankAccountNo, b.PAN_Number FROM vendors AS b
   WHERE a.BankAccountNo = b.BankAccountNo AND a.PAN_Number = b.PAN_Number AND a.VendorCode <> b.VendorCode
)

Here is a simple solution :
(I supposed that your table name is vendor)

select v2.vendorcode
  from vendor v1, vendor v2
where v1.vendorcode<>v2.vendorcode and
 v1.bankaccountno = v2.bankaccountno and
 v1.pan_number = v2.pan_number

Good Luck

There are several ways to achieve that.

1) Using HAVING[^] clause
2) Using subqueries, for example:

DECLARE @tmp TABLE (VendorCode INT,  BankAccountNo VARCHAR(30),  PAN_Number VARCHAR(30))

INSERT INTO @tmp (VendorCode,  BankAccountNo,  PAN_Number)
SELECT 12345, '100185265255', 'ASLOK7856F' UNION ALL
SELECT 45263, '100185265255', 'ASLOK7856F' UNION ALL
SELECT 45252, '152623565656', 'ASLOK7856F'

SELECT *
FROM @tmp

SELECT *
FROM (
    SELECT VendorCode, BankAccountNo,
        (SELECT COUNT(B.BankAccountNo) FROM @tmp AS B WHERE B.BankAccountNo = T.BankAccountNo) AS CountOfBankAccountNo,
        PAN_Number,
        (SELECT COUNT(PAN_Number) FROM @tmp AS P WHERE P.PAN_Number = T.PAN_Number ) AS CountOfPAN_Number
    FROM @tmp AS T
    ) AS Src
WHERE CountOfBankAccountNo>1 OR CountOfPAN_Number >1
ORDER BY VendorCode

3) Using Common Table Expressions[^] (CTE[^])
4) Using PATH with FOR XML[^]

More you'll find on MSDN:
Common solutions for: Find and/or Delete Duplicate Rows [^]
Eliminating Duplicates with DISTINCT[^]
and CodeProject Knowledge Base:
To find duplicate rows in table[^]
Find duplicate values in SQL Server[^]
How to find duplicate values in a table.[^]

这篇关于选择具有两列的记录具有相似的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！