Jay Modi's questions

目前我有 2 个表，一个列表表和一个日志表。通过以下查询，我试图获取特定日期的产品列表，并返回正确的输出。

with X as (
  select 
    l.*,
    (select status_from from logs
         where logs.refno = l.refno
           and logs.logtime >= '2021-10-01'
         order by logs.logtime limit 1) logstat
  from listings l
  where l.added_date < '2021-10-01'
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X)
SELECT 
  status.text,
  COUNT(Y.id) AS c 
from status
left join Y on Y.stat20211001 = status.code
group by status.code, status.text;

这给出了这样的输出：

在这里，我按 1 个日期过滤了查询，在本例中为 2021-10-01。现在我有 2 个输入表单，用户可以在其中选择一个从日期和一个到日期。所以我希望能够获取提供的日期范围之间的所有数据。所以基本上，如果我选择 2021-10-01 和 2021-10-02 之间的日期，它应该显示该日期及其之间的所有内容。输出应如下所示：

dbfiddle：https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5e0b8d484a41ac9104d0fb002e7f9a3c

我已格式化表格以使用以下查询以逐行方式显示条目：

with X as (
select l.*,
(select status_from from logs where logs.refno = l.refno and logs.logtime >= '2021-10-01' order by logs.logtime limit 1) logstat
from listings l
where l.added_date < '2021-10-01'
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X)
SELECT
sum(case when status.text= 'Action' and Y.id is not null then 1 else 0 end) as `Action`,
sum(case when status.text= 'Draft' and Y.id is not null then 1 else 0 end) as `Draft`,
sum(case when status.text= 'Let' and Y.id is not null then 1 else 0 end) as `Let`,
sum(case when status.text= 'Sold' and Y.id is not null then 1 else 0 end) as `Sold`,
sum(case when status.text= 'Publish' and Y.id is not null then 1 else 0 end) as `Publish`
from status
left join Y on Y.stat20211001 = status.code

现在我只需要它来显示一个新的日期列，该列显示该特定日期的所有数据，并为每个日期都有一个新条目。

这是 dbfiddle 以便更好地理解，阅读问题时请参考：https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b89a233c62983a23f94120eb3779a831

我有 2 个表，称为列表和日志表。列表表包含产品参考号及其当前状态。因此，假设如果它的状态是当前发布并且稍后出售，则状态更新为已售。这里是参考号。在此表中是唯一的，因为 1 个产品的状态可以更改。

现在我有另一个名为 Logs 的表，该表记录了在特定时间范围内特定产品（由 refno 引用）发生的所有状态更改。列表表中的初始条目没有记录在这里，但是一旦它的状态发生变化，该条目就会显示在这里。

假设我有以下列表表（'D' => 'Draft', 'A' => 'Action', 'Y' => 'Publish', 'S' => 'Sold', 'N' => '让'）：

INSERT INTO listings VALUES
 (3, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'),
 (5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), 
 (10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04');

到目前为止，每个状态下的总计数将给出：

但是，如果我只想要 2020-10-01 的条目计数，它将在所有状态下显示 0，除了已售出，它会显示 1。

现在，在 2020 年 10 月 1 日到今天的这段时间里，如上所示，在列表表中输入了一些值，而且对于某些情况，状态也发生了变化。状态表：

INSERT INTO logs VALUES
 (1, 'Let','Action','2021-06-01','LP01'), (2, 'Action','Draft','2021-10-01','LP01'), 
 (3, 'Draft','Publish','2021-10-02','LP01'), (4, 'Action','Let','2021-10-06','LP06');

现在在我的列表中显示的是状态更改后的值。所以现在要获取特定日期的总计数，我让我的语句引用日志表中的日期并分别减去 status_to，并添加 status_from。对此的查询在上面提供的 dbfiddle 中。在这里，我让它返回发生在 2021 年 10 月 1 日或之前的数据，但它没有给出正确的输出。

此查询的另一个问题是我无法返回最初发生的条目的数据。例如，就像我上面提到的，2020-10-01 上的数据值应该显示 1 在已售出，而 0 在其他所有内容（期望的输出）下，但它不会这样做，因为日志表中没有记录何时最初添加了一个新条目。

所以基本上我在这里想要的是查询检查我的两个表中发生在今天和查询中指定的日期之间的事务，并减少在我在查询中指定的日期之后发生的任何事务。如果您想更简单地解释我想要实现的目标，请参考：