SQL - 从一列到两列的日期的所有组合 [英] SQL - All combinations of dates from a single column into two columns

问题描述

我有一个名为Data"的 SQL 表，日期在第一列，第二列是一个数值，每天更新.

I have a SQL table called "Data", dates are in the first column and a number value in the second column which is updated daily.

我想根据第二列中的值查看某些日期并创建一个新表，该表将日期的所有组合显示为两列，包括计算的天数差异列.

I would like to view certain dates based on the value in second column and create a new table which shows all combinations of dates into two columns including a calculated days difference column.

创建的表需要随着数据"表的更新而自动更新.

The created table needs to update automatically as the "Data" table is updated.

任何帮助将不胜感激.

推荐答案

考虑在同一个表 Data 上使用 CROSS JOIN，以避免反向重复和相同日期:

Consider a CROSS JOIN on same table, Data, that avoids reverse duplicates and same dates:

SELECT d1.`Date`, d2.`Date`, DATEDIFF(d1.Date, d2.Date) AS `Days`
FROM Data d1
CROSS JOIN Data d2
WHERE d1.`Date` < d2.`Date`
  AND d1.`Value` = 100 AND d2.`Value` = 100

等价于 INNER JOIN 和 ON 子句:

Equivalently with INNER JOIN and ON clause:

SELECT d1.`Date`, d2.`Date`, DATEDIFF(d1.Date, d2.Date) AS `Days`
FROM Data d1
INNER JOIN Data d2
 ON  d1.`Value` = d2.`Value`
 AND d1.`Value` = 100
 AND d1.`Date` < d2.`Date`

为了包含在新表中，构建表 Table100，并使用上述选择查询运行 INSERT INTO ... SELECT.WHERE 子句中的 NOT EXISTS 是为了避免重复行.

And for inclusion in new table, build table, Table100, and run an INSERT INTO ... SELECT using above select query. The NOT EXISTS in WHERE clause is to avoid duplicate rows.

-- RUN ONLY ONCE
CREATE TABLE Table100 (
  `Date1` Date,
  `Date2` Date,
  `Days` Integer
);

-- RUN AFTER EACH Data UPDATE
INSERT INTO Table100 (Date1, Date2, Days)
SELECT d1.`Date`, d2.`Date`, DATEDIFF(d1.Date, d2.Date) AS `Days`
FROM Data d1
INNER JOIN Data d2
 ON  d1.`Value` = d2.`Value`
 AND d1.`Value` = 100
 AND d1.`Date` < d2.`Date`

WHERE NOT EXISTS 
    (SELECT 1 FROM Table100 t
     WHERE t.`Date1` = d1.`Date`
       AND t.`Date2` = d2.`Date`);

这篇关于SQL - 从一列到两列的日期的所有组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！