Home / Questions / 276930
Accepted
Bryan Romero
Asked: 2020-07-06 01:07:14 +0800 CST 2020-07-06 01:07:14 +0800 CST

查询比较同一张表的几个字段

  • 772

我有这张桌子： 

+----+------------+-----------+
| ID | VALID_FROM | VALID_TO  |
+----+------------+-----------+
| A  | 28-JUN-19  | 28-JUN-19 |
+----+------------+-----------+
| A  | 28-JUN-19  | 29-JUN-19 | //penultimo de A
+----+------------+-----------+
| A  | 28-JUN-19  | 30-JUN-19 | //ultimo de A
+----+------------+-----------+
| B  | 28-JUN-19  | 30-JUN-19 | // lo ignoramos
+----+------------+-----------+
| C  | 27-JUN-19  | 28-JUN-19 |
+----+------------+-----------+
| C  | 28-JUN-19  | 28-JUN-19 |
+----+------------+-----------+
| C  | 28-JUN-19  | 29-JUN-19 |
+----+------------+-----------+
| C  | 29-JUN-19  | 30-JUN-19 | //penultimo de C
+----+------------+-----------+
| C  | 29-JUN-19  | 01-JUL-19 | //ultimo de C
+----+------------+-----------+

我需要一个查询，对于每个当前组A,B,C返回我组的VALID_FROM最后一个元素VALID_TO大于同一组的倒数第二个元素的记录。

如果一个组只有一行，则不进行比较（如 B 的情况）。

我创建的查询是这样的：

SELECT *
FROM
    myTabla g1
INNER JOIN
    myTabla g2 ON g2.valid_from > g1.valid_to 
WHERE
    g1.ID = g2.ID;

我不知道逻辑是否正确，也不知道如何只在每组的最后一个和倒数第二个之间进行比较。

mysql

1 Answers

  1. Best Answer

    要通过 [ID] 获取最后一条和倒数第二条记录，需要一个字段来对它们进行排序，在这种情况下，使用带有连续数字的 [N_REGISTRATION] 列，它也可以是一个日期时间字段。

    在查询示例中，没有符合请求条件的记录，因此在此响应的示例数据中输入了组“D”。

    -- PASO 1: CREAR TABLA TEMPORAL CON EL CAMPO [N_BY_ID] PARA IDENTIFICAR EL ÚLTIMO (N_BY_ID=1) Y PENÚLTIMO (N_BY_ID=2) REGISTRO POR [ID].
SELECT *
INTO #TB_TEMPORAL
FROM
(
    SELECT [N_REGISTRO]
          ,[ID]
          ,[VALID_FROM]
          ,[VALID_TO]
          ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY N_REGISTRO ASC) AS N_BY_ID --IDENTIFICA EL ULTIMO (N_BY_ID=1) Y PENÚLTIMO (N_BY_ID=2) REGISTRO POR [ID].
      FROM [dbo].[myTabla]
     --ORDER BY [N_REGISTRO] DESC
 ) AS TB
 WHERE TB.N_BY_ID < 3 -- FILTRO PARA OBTENER SOLO LOS ÚLTIMOS Y PENÚLTIMOS REGISTROS POR [ID].

-- PASO 2: OBTENER LOS REGISTRO QUE CUMPLEN LA CONDICIÓN SOLICITADA.
SELECT TB1.*
FROM #TB_TEMPORAL TB1, #TB_TEMPORAL TB2 --CROSS JOIN CON SIGO MISMA.
WHERE TB1.VALID_FROM > TB2.VALID_TO -- FILTRA SOLO LOS CASOS DONDE [VALID_FROM] ES MAYOR QUE [VALID_TO].
      AND TB1.N_BY_ID = 1 -- FILTRA LOS ÚLTIMOS REGISTROS DE TB1.
      AND TB2.N_BY_ID = 2 -- FILTRA LOS PENÚLTIMOS REGISTROS DE TB2.
      AND TB1.ID = TB2.ID -- FILTRA LOS REGISTROS CON IGUAL [ID].

-- DATOS DE EJEMPLO
CREATE TABLE [dbo].[myTabla](
    [N_REGISTRO] [int] NOT NULL,
    [ID] [nchar](10) NOT NULL,
    [VALID_FROM] [date] NOT NULL,
    [VALID_TO] [date] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (8, N'A         ', CAST(0xD53F0B00 AS Date), CAST(0xD63F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (7, N'A         ', CAST(0xD53F0B00 AS Date), CAST(0xD73F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (6, N'B         ', CAST(0xD53F0B00 AS Date), CAST(0xD73F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (5, N'C         ', CAST(0xD43F0B00 AS Date), CAST(0xD53F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (4, N'C         ', CAST(0xD53F0B00 AS Date), CAST(0xD53F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (3, N'C         ', CAST(0xD53F0B00 AS Date), CAST(0xD63F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (2, N'C         ', CAST(0xD63F0B00 AS Date), CAST(0xD73F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (1, N'C         ', CAST(0xD63F0B00 AS Date), CAST(0xD83F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (9, N'D         ', CAST(0xDE3F0B00 AS Date), CAST(0xDF3F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (10, N'D         ', CAST(0xDC3F0B00 AS Date), CAST(0xDD3F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (11, N'D         ', CAST(0xDB3F0B00 AS Date), CAST(0xDC3F0B00 AS Date))

GO
    • 1