MySQL：是否可以使用没有表的值来填充SELECT？ [英] MySQL: Is it possible to 'fill' a SELECT with values without a table?

问题描述

我需要显示每一个月的订单总数。但是在几个月内没有数据，但是我想显示这个月份（总值为零）。我可以每年提供12个记录的帮助月，但是有没有办法获得一个月的时间，没有引入新的表？

I need to display the total of 'orders' for each year and month. But for some months there is no data, but I DO want to display that month (with a total value of zero). I could make a helpertable 'months' with 12 records for each year, but is there maybe a way to get a range of months, without introducing a new table?

如：

SELECT [all year-month combinations between january 2000 and march 2011] 
FROM DUAL AS years_months

有没有人有这样做的想法？你可以用某种公式来选择SELECT，即可快速创建数据！

Does anybody have an idea how to do this? Can you use SELECT with some kind of formula, to 'create' data on the fly?!

更新：

发现自己：
从日期范围生成日期

这个问题中接受的答案是我正在寻找的。可能不是最简单的方法，但它可以做到以下所需：根据公式填充select数据。

The accepted answer in this question is kind of what I'm looking for. Maybe not the easiest method, but it does what I want: fill a select with data, based on a formula....

要创建一个表与过去十年的所有月份一起飞行：

To 'create' a table on the fly with all months of the last 10 years:

SELECT CONCAT(MONTHNAME(datetime), ' ' , YEAR(datetime)) AS YearMonth,
       MONTH(datetime) AS Month,
       YEAR(datetime) AS Year 
FROM (
    select (curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH) as datetime
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    LIMIT 120
) AS t
ORDER BY datetime ASC 

我必须承认，这是非常异国情调，但它可以工作...

I must admit, this is VERY exotic, but it DOES work...

我可以使用这个选择加入我的订单表，并获得每个月的总计，即使在某个月没有数据。

I can use this select to join it with my 'orders'-table and get the totals for each month, even when there is no data in a certain month.

但是使用数字或日历表可能是最好的选择，所以我要使用它。

But using a 'numbers' or 'calendar' table is probably the best option, so I'm going to use that.

推荐答案

如果可能，尽量远离生成数据。它使非常简单的查询变得复杂，但最重要的是：它使优化器无法结束。

If at all possible, try to stay away from generating data on the fly. It makes very simple queries ridiculusly complex, but above all: it confuses the optimizer to no end.

如果需要一系列整数，请使用静态的整数表。如果您需要一系列日期，数月或其他日期，请使用日历表。除非你正在处理一些真正非凡的要求，否则静态表就是要走的路。

If you need a series of integers, use a static table of integers. If you need a series of dates, months or whatever, use a calendar table. Unless you are dealing with some truly extraordinary requirements, a static table is the way to go.

我举了一个例子，说明如何创建数字表和最小日历表（仅日期）在此答案。

I gave an example on how to create a table of numbers and a minimal calendar table(only dates) in this answer.

如果你有这些表，就很容易解决你的查询。

If you have those tables in place, it becomes easy to solve your query.

<将订单数据汇总到MONTH。

1. 加入月份表（或与日期表不同的MONTH）

这篇关于MySQL：是否可以使用没有表的值来填充SELECT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！