主页 / dba / 问题 / 301332
Accepted
Jay Modi
Asked: 2021-10-20 02:41:26 +0800 CST

以按日期分组的按日期顺序显示所有数据

  • 772

目前我有 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 之间的日期，它应该显示该日期及其之间的所有内容。输出应如下所示：

日期 发布 行动 让 卖 草稿
2021-10-01 0 3 0 1 1
2021-10-02 0 2 0 1 2

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

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

mysql

2 个回答

  1. Best Answer

    看看下面和dbfiddle.uk

    with Y as (
  with recursive D (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n+1, day + interval 1 day from D
      where day + interval 1 day < '2021-10-15'
  ) select * from D
), X as (
  select Y.day,
         l.*,
         (select status_from from logs
            where logs.refno = l.refno
              and logs.logtime >= Y.day
            order by logs.logtime
            limit 1) logstat
    from listings l, Y
    where l.added_date <= Y.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    group by X.day, stat_day
)
select Z.day,
  sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft,
  sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action,
  sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish,
  sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold,
  sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let
  from Z
  group by Z.day
  order by Z.day;
    • 1

  2. 从 3 列表开始

    SELECT date, status, count(*)
    FROM ...
    GROUP BY date, status

    然后“pivot”，使用上面的作为派生表（即FROM (SELECT...)）。

    不要打扰WITH，它只会混淆问题。

    • 0

相关问题