从sql中的where子句中的重复数据返回重复值 [英] return duplicate value from duplicate data in where clause in sql
问题描述
我有一套类似于
kti001
kti001
kti002
等。
在一个数组中。
现在使用in子句如
从表中选择empid（'kti001 '，'kti001'，'kti002'）
返回不同的数据
kti001
kti002
使用或子句也返回相同的结果。
但是我想根据提供的数据返回数据，即使它是重复的。
怎么做？
注意：empid列表是动态的。我通过循环使where子句成为一部分通过empid list.So不给出静态查询或动态复杂的答案。
根据描述，两行包含
kti001
kti001
不重复。否则SELECT语句将返回这两个。
在SQL中如果要消除重复行，则需要指定DISTINCT 。由于您没有使用该关键字，因此允许重复。
如果说有唯一的选择是行之间存在差异，可能是尾随空格或一些东西。
要找出差异，请在动态数据上尝试这样的事情
选择 ' >' + empid + ' <' 来自 table
这应该可以帮助您可视化数据中的可能空间。
例如，使用IN运算符不会删除重复项，请考虑以下语句：
创建测试表
CREATE TABLE TestTable1（
EmpId VARCHAR （MAX）
）;
添加初始行
INSERT INTO TestTable1（EmpId） VALUES （' kti001'）;
INSERT INTO TestTable1（EmpId） VALUES （' kti001'）;
INSERT INTO TestTable1（EmpId） VALUES （' kti002'）;
选择不带DISTINCT
SELECT EmpId FROM TestTable1 WHERE EmpID in （' kti001'，' kti001'，< span class =code-string>' kti002'）
结果
EmpId
------
kti001
kti001
kti002
选择DISTINCT
SELECT DISTINCT EmpId FROM TestTable1 WHERE EmpID in （' kti001'，' kti001'，' kti002'）
结果
EmpId
------
kti001< - 重复删除
kti002
在开头添加一个带有额外空格的非重复行
INSERT INTO TestTable1（EmpId） VALUES （' kti001'）;
选择
SELECT EmpId FROM TestTable1 WHERE EmpID 中 （' kti001'，' kti001'，' kti002'）
结果，新添加的行未列出，因为它不满足条件
EmpId
------
kti001
kti001
kti002
列出表的内容
SELECT ' > ' + EmpId + ' <' FROM TestTable1
结果
（无列名）
--- -------------
> kti001<
> kti001<
> kti002<
> kti001<
无法说出额外的空间是问题还是其他问题，但主要的一点是没有DISTINCT关键字的结果集中没有删除重复项。
I have a set of empid like
kti001
kti001
kti002
etc.
in an array.
Now using "in" clause like
select empid from table where empid in ('kti001','kti001','kti002')
returns distinct data
kti001
kti002
using "or" clause also returning same results.
But I want to return data according to empid supplied even it is duplicate.
How to do it?
Note: empid list is dynamic.I'm making the where clause part by looping through the empid list.So don't give an answer which is for static query or for which making dynamic is complex.
Based on the description the two rows containing
kti001 kti001
are not duplicates. Otherwise the SELECT statement would return both of those.
In SQL if you want to eliminate duplicate rows you need to specifyDISTINCTin your query. Since you haven't used that keyword, duplicates are allowed.
Having that said the only option is that there is a difference between the rows, perhaps a trailing space or something.
To find out the differences have a try with something like this on your dynamic data
select '>' + empid + '<' from table
That should help you to visualize possible spaces within the data.
[Edit]
As an example that using an IN operator does not remove duplicates, consider the following statements:
Create the test table
CREATE TABLE TestTable1 ( EmpId VARCHAR(MAX) );
Add initial rows
INSERT INTO TestTable1 (EmpId) VALUES ('kti001'); INSERT INTO TestTable1 (EmpId) VALUES ('kti001'); INSERT INTO TestTable1 (EmpId) VALUES ('kti002');
Select without DISTINCT
SELECT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')
Result
EmpId ------ kti001 kti001 kti002
Select with DISTINCT
SELECT DISTINCT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')
Result
EmpId ------ kti001 <-- duplicate removed kti002
Add a non duplicate row with extra space in the beginning
INSERT INTO TestTable1 (EmpId) VALUES (' kti001');
Select
SELECT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')
Results, the newly added row isn't listed since it does not satisfy the condition
EmpId ------ kti001 kti001 kti002
List the content of the table
SELECT '>' + EmpId + '<' FROM TestTable1
Result
(No column name) ---------------- >kti001< >kti001< >kti002< > kti001<
Its impossible to say if an extra space is the problem or something else but the main point is that duplicates aren't removed from the result set without DISTINCT keyword.
这篇关于从sql中的where子句中的重复数据返回重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
