mySQL 时间范围之间 - 开始时间可以大于结束时间

Question

John Beasley

Asked: 2024-10-03 04:05:06 +0800 CST2024-10-03 04:05:06 +0800 CST 2024-10-03 04:05:06 +0800 CST

在一个查询中从表获取计数和日期并从另一个表获取计数和日期

772

我有一个查询，它从表中获取计数和日期。它看起来像这样：

SELECT 
 date(`date`) as 'Date'
 , sb.`send_id`
 , count(`id`) as 'SUBS' 
FROM `table1`
WHERE `date` BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date(`date`), `send_id`;

这会给我如下结果：

|    DATE    |   SEND_ID    |   SUBS    |
-----------------------------------------
| 2024-09-01 |  1111111     |    1900   | 
| 2024-09-01 |  2222222     |    1835   | 
| 2024-09-02 |  1111111     |    264    | 
| 2024-09-02 |  2222222     |    26     | 
-----------------------------------------

我有第二个查询，如下所示：

SELECT 
  date(`o_date`)
  , `o_send_id`
  , count(`id`) AS 'UNI'
FROM `table2`
WHERE `o_date` BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date(`o_date`), `o_send_id`;

这得到如下结果：

|    O_DATE    |   O_SEND_ID    |   UNITS  |
--------------------------------------------
| 2024-09-01   |    1111111     |    107   | 
| 2024-09-01   |    2222222     |    360   | 
| 2024-09-02   |    1111111     |    223   | 
| 2024-09-02   |    2222222     |    209   | 
--------------------------------------------

我想要做的是连接这些表以显示 SUB 和 UNITS。

因此结果看起来应该是这样的：

|    DATE    |   SEND_ID    |   SUBS    |   UNITS |
---------------------------------------------------
| 2024-09-01 |  1111111     |    1900   |   107   |
| 2024-09-01 |  2222222     |    1835   |   360   |
| 2024-09-02 |  1111111     |    264    |   223   |
| 2024-09-02 |  2222222     |    26     |   209   |
---------------------------------------------------

这是我尝试在单个日期连接表格的操作：

SELECT 
  date(tb1.`date`) as 'Date'
  , tb1.`send_id`
  , count(tb1.`id`) as 'SUBS' 
  , count(tb2.`id`) as 'UNITS'
FROM `table1` tb1
JOIN `table2` tb2 ON tb2.`o_send_id` = tb1.`send_id` AND tb2.`o_date` = tb1.`date`
WHERE (tb1.`date` BETWEEN '2024-09-01%' AND '2024-09-30%')
GROUP BY date(tb1.`date`), tb1.`send_id`;

它运行了一段时间，但最终还是崩溃了。我猜是因为有相当多的记录（数百万条）。我收到一条错误消息：

Error in processing request
Error code: 500
Error text: Internal Error (rejected)
It seems that the connection to server has been lost.
Please check your network connectivity and server status.

因此我尝试运行相同的查询，但这次只使用 1 个日期和 1 个 send_id，如下所示：

SELECT 
  date(tb1.`date`) as 'Date'
  , tb1.`send_id`
  , count(tb1.`id`) as 'SUBS' 
  , count(tb2.`id`) as 'UNITS'
FROM `table1` tb1
JOIN `table2` tb2 ON tb2.`o_send_id` = tb1.`send_id` AND tb2.`o_date` = tb1.`date`
WHERE (tb1.`date` LIKE '2024-09-30%') AND tb1.`send_id` = '1111111'
GROUP BY date(tb1.`date`), tb1.`send_id`;

该查询运行，但我得到如下结果：

|    DATE    |   SEND_ID    |   SUBS     |   UNITS   |
------------------------------------------------------
| 2024-09-30 |  1111111     |    35960   |   35960   |
------------------------------------------------------

尽管查询成功运行，但我不确定 SUBS 和 UNITS 发生了什么，因为它们的结果都是 36960。总数应该不同，而且不应该那么高。

不确定我做错了什么。

在我之前问过的一个问题中，有人建议我发布表格描述。

以下是表 1 的描述：

| Field   |   Type        | Null | Key  | Default |         Extra       |
-------------------------------------------------------------------------
| id      | int(11)       |  NO  |  PRI |  NULL   |  AUTO_INCREMENT     |  
| send_id | varchar(255)  |  YES |      |  NULL   |                     |   
| date    | datetime      |  YES |      |  NULL   |                     |
-------------------------------------------------------------------------

以下是表2的描述：

| Field     |   Type        | Null | Key  | Default |         Extra       |
---------------------------------------------------------------------------
| id        | int(11)       |  NO  |  PRI |  NULL   |  AUTO_INCREMENT     |  
| o_send_id | varchar(255)  |  YES |      |  NULL   |                     |    
| o_date    | datetime      |  YES |      |  NULL   |                     |
---------------------------------------------------------------------------

每个表的描述都没有什么特别之处。

综上所述，请帮我弄清楚如何在同一个查询中显示 SUBS 计数和 UNIT 计数。

表 1 的索引如下。

这是表2的索引。

我只用ID。

2 个回答

Voted

ValNik · Answer 1 · 2024-10-03T04:50:37+08:00

你可以将你的两个查询合并为

select coalesce(first.Date,second.Date) as `date`
  ,coalesce(first.`send_id`,second.`send_id`)
  ,coalesce(first.SUBS,0) Subs
  ,coalesce(first.UNI,0) Uni
from
(  SELECT 
   date(`date`) as 'Date'
   , sb.`send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
 )first
left join (
  SELECT date(`o_date`)
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)second on first.date=second.date and first.send_id=second.send_id

如果date或o_send_id在 1 个子查询输出中存在而在另一个子查询输出中不存在，则可能会丢失行。

更新1：

更可靠的案例

ID	日期	发送 ID
1	2024-09-01	100
2	2024-09-01	101
3	2024-09-02	101

ID	o_日期	o_发送_id
1	2024-09-01	100
2	2024-09-01	101
3	2024-09-01	101
4	2024-09-11	104

select `date`
  ,send_id
  ,sum(Subs) subs
  ,sum(UNI)  Uni
from
(  SELECT date(`date`) as `date`, `send_id` , count(`id`) as 'SUBS' ,0 UNI
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
  union all
  SELECT date(`o_date`) `date` , `o_send_id` ,0 SUBS ,count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)u
group by `date`,send_id

日期	发送 ID	潜艇	统一
2024-09-01	100	1	1
2024-09-01	101	1	2
2024-09-02	101	1	0
2024-09-11	104	0	1

更新2：

在第一个例子中，我们丢失了 date=2024-09-11' 和 send_id=104

select coalesce(first.Date,second.Date) as `date`
  ,coalesce(first.`send_id`,second.`o_send_id`) send_id
  ,coalesce(first.SUBS,0) Subs
  ,coalesce(second.UNI,0) Uni
  ,first.*, second.*
from
(  SELECT 
   date(`date`) as 'Date'
   , `send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
 )first
left join (
  SELECT date(`o_date`) Date
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)second on first.date=second.date and first.send_id=second.o_send_id

日期	发送 ID	潜艇	统一	日期	发送 ID	替补	日期	o_发送_id	统一
2024-09-01	100	1	1	2024-09-01	100	1	2024-09-01	100	1
2024-09-01	101	1	2	2024-09-01	101	1	2024-09-01	101	2
2024-09-02	101	1	0	2024-09-02	101	1	无效的	无效的	无效的

小提琴

Schwern · Answer 2 · 2024-10-03T04:23:26+08:00

复合索引(send_id, date)或许((date(date)), send_id)可能会提高所有查询的性能。

您可以连接分组查询，而不是连接表。

注意：您不需要在日期间查询中添加 %，MySQL 可能会忽略它。请date 按照文档中的建议将其明确转换为。

with table1_count as (
  SELECT 
   date(`date`) as 'Date'
   , sb.`send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`date`), `send_id`
),
table2_count as (
  SELECT 
    date(`o_date`) as 'Date'
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`o_date`), `o_send_id`
)
select 
  t1c.date,
  t1c.send_id,
  t1c.subs,
  t2c.uni
from table1_count t1c
left join table2_count t2c on 
  t1c.date = t2c.date and 
  t1c.send_id = t2c.o_send_id
order by t1c.date, t1c.subs

这种方法的缺点是，如果表 1 中缺少某些天数，但表 2 中有这些天数，则它们不会出现在查询中。

由于您使用的是没有的 MySQL 5.7 with，因此可以使用子查询重写它们。

select 
  t1c.date,
  t1c.send_id,
  t1c.subs,
  t2c.uni
from (
  SELECT 
    date(`date`) as 'Date'
    , sb.`send_id`
    , count(`id`) as 'SUBS' 
    FROM `table1`
    WHERE `date` BETWEEN date('2024-09-01') AND date('2024-09-30')
    GROUP BY date(`date`), `send_id`
) t1c
left join (
  SELECT 
    date(`o_date`) as 'Date'
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`o_date`), `o_send_id`
) t2c on 
  t1c.date = t2c.date and 
  t1c.send_id = t2c.o_send_id
order by t1c.date, t1c.subs

在一个查询中从表获取计数和日期并从另一个表获取计数和日期

`(表达式，左值) = 右值` 在 C 或 C++ 中是有效的赋值吗？为什么有些编译器会接受/拒绝它？

何时应使用 std::inplace_vector 而不是 std::vector？

在 C++ 中，一个不执行任何操作的空程序需要 204KB 的堆，但在 C 中则不需要

如果 T 既不可构造、不可复制、也不可移动，那么我可以拥有 std::optional<T> 吗？

为什么我可以定义一个 constinit 的 std::string 实例？如果对象需要动态初始化，constinit 不是被禁止的吗？

如何分配以后放置的新“如同新”

PowerBI 目前与 BigQuery 不兼容：Simba 驱动程序与 Windows 更新有关

将 NULL 和 nullptr 传递给模板参数有什么区别？

AdMob：MobileAds.initialize() - 对于某些设备，“java.lang.Integer 无法转换为 java.lang.String”

我正在尝试仅使用海龟随机和数学模块来制作吃豆人游戏

在一个查询中从表获取计数和日期并从另一个表获取计数和日期

2 个回答

相关问题