Sql Query，查询同一个表两次（可能）

I have a SQL query I am trying to write but haven't been able to come up with the solution.

我有一个我想写的SQL查询但是无法提出解决方案。

2 entries in my table that are related, are related through the first 3 characters of the ID. When a new item is added that needs to be related to a certain entry, the first 3 characters are added, and then the 2nd two are incremented by one to create the new ID. When a completely sepere entry is needed a unique 3 digit string is used and starts the second two chars with "00". There may be some that have large gaps in the last 2 columns because of deleted data (I don't want these in my query)

我的表中有2个相关的条目通过ID的前3个字符相关联。当添加需要与某个条目相关的新项目时，将添加前3个字符，然后将第2个字符加1以创建新ID。当需要完全单独的条目时，使用唯一的3位数字符串并以“00”开始后两个字符。由于删除了数据，可能会有一些在最后两列中存在较大间隙（我在查询中不希望这些）

What I would like to do is get only the rows where there is another row with the same first 3 characters and 1 less from the count of the last 2.

我想要做的只是获取另一行中具有相同前3个字符的行，并且与最后2个字符的数量相比减少1。

Table Example:

表格示例：

ID    |  Date 
====== ========
11100 | 07/12
22211 | 07/13
12300 | 07/14
11101 | 07/14
11400 | 07/16
22212 | 07/16

The Query should only return these elements because there exsists another entry with the same first 3 chars and one less from the last 2 chars .

Query应该只返回这些元素，因为存在另一个具有相同前3个字符的条目，并且与最后2个字符相比较少一个。

ID    |  Date 
====== ========
11101 | 07/14
22212 | 07/16

3 个解决方案

#1

Looks like a simple JOIN will do it;

看起来简单的JOIN会做到这一点;

SELECT a.*
FROM Table1 a
JOIN Table1 b
  ON a.id/100 = b.id/100
 AND a.id = b.id + 1

An SQLfiddle to test with.

一个要测试的SQLfiddle。

You can also write it as an EXISTS query;

您也可以将其编写为EXISTS查询;

SELECT a.* 
FROM Table1 a
WHERE EXISTS (
  SELECT 1 FROM Table1 b WHERE b.id = a.id-1
     AND a.id/100 = b.id/100
)

Another SQLfiddle.

另一个SQLfiddle。

#2

Declare @a table(ID Varchar(10),  [Date] varchar(10))
Insert into @a
Select '11100','07/12'
UNION  Select '22211','07/13'
UNION  Select '12300','07/14'
UNION  Select '11101','07/14'
UNION  Select '11400','07/16'
UNION  Select '22212','07/16'

Select a.* from
@a a
JOIN
(
Select SubString(ID,1,3) + RIGHT('0'+Cast(MAX(Cast(SubString(ID,4,2) as int)) as Varchar(10)),2) as ID
from @a
group by SubString(ID,1,3)
Having Count(*)>1
) x
on x.ID=a.ID

#3

Try this

尝试这个

SELECT T1.ID,T1.[Date] FROM Table1 T1 INNER JOIN  
(
  SELECT LEFT(ID,3) AS ID,MAX([Date]) AS [Date] FROM Table1
  GROUP BY LEFT(ID,3)
  HAVING COUNT(LEFT(ID,3)) > 1
) T2 ON LEFT(T1.ID,3) = T2.ID
AND T1.[Date] = T2.[Date]

SQL FIDDLE DEMO

SQL FIDDLE DEMO

#1