Выбрать все данные между двумя датами

Question

Необходимо выбрать значения для некоторых дат, как это сделать, я знаю, но есть одна проблема, которую не удается решить.

Моя таблица в 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 по 14.06.2010? Помогите сделать запрос.

Answer 1

MySQL не имеет рекурсивной функциональности, поэтому вам нужно использовать табличный трюк NUMBERS -

1. Создайте таблицу, которая содержит только увеличивающиеся числа - это легко сделать с помощью 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;
   

2. Заполните таблицу, используя:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ... столько ценностей, сколько вам нужно.

3. Используйте DATE_ADD для построения списка дат, увеличивая дни на основе значения NUMBERS.id:

   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
   

4. СЛЕДУЮЩИЙ ПРИСОЕДИНЯЙТЕСЬ к своей таблице данных, основанной на временной части:

      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
   

Answer 2

Попробуйте агрегатную функцию sum в поле wins и ограничьте ее (используя where) конкретным диапазоном дат (поэтому date<='6/14/2010' AND date>='06/06/2010').

Так что-то вроде SELECT SUM(выигрывает) ОТ [tbl_name] ГДЕ [ДАТА] между [дата1] И [дата2]

получить количество побед в каждом диапазоне дат; добавьте 'group by [user]', чтобы увидеть количество побед каждого пользователя в этом диапазоне дат.

Answer 3

Вот ответ, который я подозреваю, поможет. Я создаю временную таблицу, чтобы дать вам список дат (который я вставляю, используя цикл с датой начала и окончания. Счетчик должен начинаться с 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, и она отлично работает.