选择两个日期之间的所有数据

发布于 2024-09-12 23:13:53 字数 367 浏览 3 评论 0原文

有必要为某些日期选择值，我知道如何做到这一点，但有一个问题无法解决。

我在 MySQL 中的表：

[User]. [Wins]. [DATE]
#
Ivan ....... 4 ..... 05/06/2010
#
Ivan ....... 3 ..... 06/15/2010
#
Ivan ........ 6 ..... 06/18/2010
#
Ivan ........ 1 ..... 29/06/2010

问题是，如果用户没有访问过该站点，则不会在数据库中创建一行，并且会错过获取的日期。

如何获得统计数据（例如胜利 0），例如从 06/06/2010 到 06/14/2010 期间？帮忙做一下这个查询。

原文

It is necessary to choose values for some dates, how to do this I know, but there is one problem that does not work to solve.

My table in MySQL:

[User]. [Wins]. [DATE]
#
Ivan ....... 4 ..... 05/06/2010
#
Ivan ....... 3 ..... 06/15/2010
#
Ivan ........ 6 ..... 06/18/2010
#
Ivan ........ 1 ..... 29/06/2010

The problem is that if the user has not visited the site, a row in the database is not created and missed the date obtained.

How do I get a statistics (such Wins 0) such as the period from 06/06/2010 to 06/14/2010? Help make a make this query.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

℉服软 2024-09-19 23:13:53

MySQL 没有递归功能，因此您只能使用 NUMBERS 表技巧 -

创建一个仅保存递增数字的表 - 使用 auto_increment 很容易做到：

如果存在`example`则删除表。`numbers`;
创建表“示例”。“数字”(
  `id` int(10) 无符号 NOT NULL 自动增量，
   主键（`id`）
) 引擎=InnoDB 默认字符集=latin1;

使用以下方式填充表：
```
插入数字
  （ID）
价值观
  （无效的）
```
...您需要的任意数量的值。
使用DATE_ADD< /a> 构建日期列表，根据 NUMBERS.id 值增加天数：
<前><代码>选择 x.*
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y')
从数字 n
WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 DAY) <= '2010-06-14 ) x

根据时间部分 LEFT JOIN 到数据表：

 SELECT x.ts AS 时间戳，
          COALESCE(COUNT(y.wins), 0) AS cnt
     FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y') AS ts
             从数字 n
            WHERE DATE_ADD('2010-06-06', INTERVAL n.id - 1 天) <= '2010-06-14') x
左连接表 y ON STR_TO_DATE(y.date, '%m/%d/%Y') = x.ts
 按 x.ts 分组

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

Create a table that only holds incrementing numbers - easy to do using an auto_increment:

DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Populate the table using:
```
INSERT INTO NUMBERS
  (id)
VALUES
  (NULL)
```
...for as many values as you need.

Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value:

SELECT x.*
  FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y') 
          FROM numbers n
         WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 DAY) <= '2010-06-14 ) x

LEFT JOIN onto your table of data based on the time portion:

   SELECT x.ts AS timestamp,
          COALESCE(COUNT(y.wins), 0) AS cnt
     FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY), '%m/%d/%Y') AS ts
             FROM numbers n
            WHERE DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY) <= '2010-06-14') x
LEFT JOIN TABLE y ON STR_TO_DATE(y.date, '%m/%d/%Y') = x.ts
 GROUP BY x.ts

回复收藏 0 原文

初熏 2024-09-19 23:13:53

尝试在 'wins' 字段上使用 'sum' 聚合函数，并将其（使用 where）限制为特定日期范围（因此 date<='6/14/2010' AND date>='06/06/2010'）。

所以像
SELECT SUM(wins) FROM [tbl_name] WHERE [DATE] Between [date1] AND [date2]

以获得每个日期范围内的获胜次数；添加“按 [用户] 分组”，以便查看每个用户在该日期范围内的获胜次数。

回复收藏 0 原文

作死小能手 2024-09-19 23:13:53

这是我怀疑可以解决问题的答案。我创建了一个临时表来为您提供日期列表（我使用带有开始日期和结束日期的循环插入该列表。计数器必须从 0 开始，因为您想包含开始日期本身）。

然后，我将 DateList 连接到 Wins 表，为您提供该范围内所有日期、用户及其在这些日期的访问的列表。我还没有测试过它，但我怀疑代码接近于您所需要的。

DECLARE Counter INT DEFAULT 0
DECLARE NumDays INT DEFAULT DATEDIFF(StartDate, EndDate)

CREATE TEMPORARY TABLE DateList
(
    [Date] DATETIME
)

WHILE Counter <= NumDays
    INSERT INTO DateList ([Date]) VALUES (DATE_ADD(StartDate, INTERVAL Counter Day))
    SET Counter = Counter + 1
END WHILE

SELECT
    [User]  
    ,COUNT(User) AS Wins
    ,DateList.[Date] AS [Date]
FROM
    DateList LEFT JOIN Wins ON Wins.[Date] = DateList.[Date]
WHERE
    DateList.[Date] BETWEEN StartDate AND EndDate
GROUP BY
    [User]
    ,DateList.[Date]
ORDER BY
    DateList.[Date]

编辑：我对我的连接和计数做了一些更改。我已经测试了MSSQL版本并且工作正常。

Here's an answer i suspect would do the trick. I create a temporary table to give you a list of the dates (which i insert using a loop with the start and end date. The counter has to start at 0 because you want to include the start date itself).

Then, i just join the DateList to the Wins table to give you the list of all dates in the range, the users and their visits on those dates. I haven't tested it but i suspect the code is close to being exactly what you need.

DECLARE Counter INT DEFAULT 0
DECLARE NumDays INT DEFAULT DATEDIFF(StartDate, EndDate)

CREATE TEMPORARY TABLE DateList
(
    [Date] DATETIME
)

WHILE Counter <= NumDays
    INSERT INTO DateList ([Date]) VALUES (DATE_ADD(StartDate, INTERVAL Counter Day))
    SET Counter = Counter + 1
END WHILE

SELECT
    [User]  
    ,COUNT(User) AS Wins
    ,DateList.[Date] AS [Date]
FROM
    DateList LEFT JOIN Wins ON Wins.[Date] = DateList.[Date]
WHERE
    DateList.[Date] BETWEEN StartDate AND EndDate
GROUP BY
    [User]
    ,DateList.[Date]
ORDER BY
    DateList.[Date]

Edit: I made some changes to my joins and my count. I've tested the MSSQL version and it works fine.

回复收藏 0 原文

~没有更多了~