требуемый для получения всех дат в результате mysql
У меня есть mysql вызываемый пользователь таблицы (идентификатор, имя, join_on), соединение на является полем даты, что я хочу, должен показать в каждый день, сколько использования был создан, я могу использовать группу, но это только даст мне даты, когда пользователи будут добавлены как если дата
4/12/10 5 users added
4/13/10 2 users added
4/15/10 7 users added
здесь дата 14.04.10 отсутствует, и я хочу перечислить всех дат через один месяц. У меня есть одно решение для него путем составления другой таблицы только для добавления даты, и та таблица будет оставленное соединение моя пользовательская таблица на join_on и даст общий результат, но я не хочу делать это что касается создания этого, я должен создать и добавить записи в таблице даты, предложите другой подход для того, чтобы сделать так.
Спасибо.
3 ответа
Существует подход, который может сделать это на чистом SQL, но имеет ограничения.
Сначала вам нужно иметь числовую последовательность 1,2,3 ... n в виде строк (предположим, что select row from rows вернет это).
Затем вы можете выйти из соединения и преобразовать в даты в зависимости от количества дней между мин. И макс.
select @min_join_on := (select min(join_on) from user);
select @no_rows := (select datediff(max(join_on), @min_join_on) from user)+1;
предоставит вам необходимое количество строк, которые затем вы можете использовать, чтобы
select adddate(@min_join_on, interval row day) from rows where row <= @no_rows;
вернул требуемую последовательность дат, после чего вы можете выполнить левое присоединение к таблице пользователей.
Использование переменных можно избежать, если вы используете подзапросы, я разбил их для удобства чтения.
Проблема в том, что количество строк в таблице rows должно быть больше, чем @no_rows.
Для 10 000 строк вы можете работать с диапазонами дат до 27 лет, со 100 000 строками вы можете работать с диапазонами дат до 273 лет (это очень плохо, но я боюсь, что если вы этого не сделаете хотите использовать хранимые процедуры, это будет выглядеть неудобно).
Итак, если вы можете работать с такими фиксированными диапазонами дат, вы даже можете заменить таблицу запросом, например,
SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=0) r
, который будет производить 10 000 строк от 1 до 10 000, и это будет не очень неэффективно.
Итак, в конце концов, это можно сделать в одном запросе.
create table user(id INT NOT NULL AUTO_INCREMENT, name varchar(100), join_on date, PRIMARY KEY(id));
mysql> select * from user;
+----+-------+------------+
| id | name | join_on |
+----+-------+------------+
| 1 | user1 | 2010-04-02 |
| 2 | user2 | 2010-04-04 |
| 3 | user3 | 2010-04-08 |
| 4 | user4 | 2010-04-08 |
+----+-------+------------+
4 rows in set (0.00 sec)
insert into user values (null, 'user1', '2010-04-02'), (null, 'user2', '2010-04-04'), (null, 'user3', '2010-04-08'), (null, 'user4', '2010-04-08')
SELECT date, count(id)
FROM (
SELECT adddate((select min(join_on) from user), row-1) as date
FROM (
SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (SELECT @row:=0) r ) n
WHERE n.row <= ( select datediff(max(join_on), min(join_on)) from user) + 1
) dr LEFT JOIN user u ON dr.date = u.join_on
GROUP BY dr.date
+------------+-----------+
| date | count(id) |
+------------+-----------+
| 2010-04-02 | 1 |
| 2010-04-03 | 0 |
| 2010-04-04 | 1 |
| 2010-04-05 | 0 |
| 2010-04-06 | 0 |
| 2010-04-07 | 0 |
| 2010-04-08 | 2 |
+------------+-----------+
7 rows in set (0.00 sec)
может быть проще просто использовать GROUP BY, а затем в вашем фактическом коде добавьте недостающие даты (или выполните итерацию по всему диапазону дат и выведите ноль, если дата отсутствует в результатах запроса).
Не все нужно решать в SQL, и многие вещи легче решить в другом месте. :)
SELECT * FROM TABLE WHERE DATE LIKE '4/%/10'
Это даст вам все данные за апрель 2010 г.
Точно так же вы можете получить данные за любой месяц, указав числовое значение месяца, т.е. 4 в данном случае