SQL自联接与不同日期的数据比较 [英] SQL Self-join with data comparison for different days

问题描述

我需要在 SQL 中比较两天的数据.我真的需要在单个查询中使用它，因为我需要在分页中使用结果.问题是，当我进行 self join 时，它会导致 重复列，因为 INNER JOIN 是笛卡尔积.

I need to compare data on two different days in SQL. And I really need that in a single query since I need to use the results in pagination. Problem is, when I'm doing a self join it's results in duplicate columns since INNER JOIN is a cartesian product.

这是 sql fiddle 上的代码

例如

SELECT * FROM `my_table` as t1 
INNER JOIN my_table t2 ON t1.quality = t2.quality
WHERE (
  t1.day = '2015-01-08' OR t1.day = '2015-01-09' OR 
  t2.day = '2015-01-08' OR t2.day = '2015-01-09'
)

两个问题:

1. 如何重写质量上每个 JOIN 获得一行的内容?
2. 如果某一天缺少某些相应的行(关于质量)，它仍然有效吗?

编辑

这是输入:

INSERT INTO my_table
(quality, quantity, day)
VALUES
('A', 1, '2015-01-15'),
('B', 2, '2015-01-15'),
('B', 0, '2015-01-10');

所需的输出是:

QUALITY  | QUANTITY  | T1.QUANTITY - T2.QUANTITY
A           1          0 (or NULL?)
B           2          2

说明:

每个日期都有唯一值.所以我们只看到 A 和 B 质量.对于 B- 在另一个日期有合适的 B.对于 A - 没有.因此，B 的差值为 2(较高的日期减去较低的日期).A 的差值是 0，因为没有什么可以减去.

There are unique values on each date. So we see only A and B quality. For B- there is appropriate B on another date. For A - there isn't. Therefore the difference for B is 2 (higher date minus lower). The difference for A is 0 because there's nothing to subtract from.

推荐答案

您得到了重复的列，因为在您拥有它时，您正在从 T1 和 T2 进行查询.因此，除非您明确说只显示 T1.*，否则它将从 BOTH 表别名引用中获取列.

You are getting duplicated columns because as you have it, you are querying from T1 AND T2. So unless you explicitly say show me only T1.* it will grab columns from BOTH table alias references.

由于您的查询是对日期进行 OR 运算，因此您可能也会得到笛卡尔结果.

Since your query is doing an OR on the dates, you are probably going to get a Cartesian result too.

现在知道你的表结构，但你可能会更好地使用类似......的显式查询

Now knowing your table structure, but you might be better with an explicit query something like...

SELECT
      t1.day,
      t2.day as OtherDay,
      t1.quality,
      t1.anotherColumn,
      t2.OtherAnotherColumn,
      t1.thirdColumn,
      t2.OtherThirdColumn
   FROM 
      my_table t1
         join my_table t2
            on t1.quality = t2.quality
           AND t2.day = '2015-01-09'
   where
      t1.day = '2015-01-08' 

根据(天，质量)在my_table"上建立索引以优化查询.您可以继续成对添加，即您尝试在第 1 天和第 2 天之间进行比较的列.T1 将仅返回与第一天相关联的那些，而 T2 别名将仅显示第二个日期的匹配条目.

Have an index on your "my_table" based on (day, quality) to optimize the query. And you can just keep adding in pairs, the columns you are trying to compare between day1 and day2. T1 will only return those associated with the first day, and the T2 alias will only show for matching entries for the second date.

现在，如果 T1 端只有条目，而没有对应的质量和日期的 T2 条目，但您仍然想看到这些，那么只需将 JOIN 更改为 LEFT JOIN.

Now, if there are only entries on the T1 side with no corresponding T2 entry for the quality and date in question, but you still want to see those, then just change the JOIN to a LEFT JOIN.

这篇关于SQL自联接与不同日期的数据比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！