使用日期循环 SQL 插入语句 [英] looping a SQL insert statement with dates

问题描述

我需要运行一个语句，该语句每次都会使用相同的信息填充数据库，但日期除外.

I need to run a statement that will populate a data base with the same information every time EXCEPT for the date.

我目前正在尝试这些方法，但没有任何运气.

im currently trying something along these lines and aren't having any luck.

DECLARE 
i_int BINARY_INTEGER := 1;
  BEGIN
    WHILE i_int <= 30 LOOP
      INSERT INTO bs_events (eventDate,eventTime,title,spaces,description,entryFee,payment_required,max_qty,allow_multiple) VALUES ('2011-03-i_int 00:00:00', '10:00','Carpet Cleaner 3000','4','This is rad','25.99','1','1','2');
      i_int := i_int + 1;
    END LOOP;
  END;
  /

我似乎无法让它工作，我陷入了一个代码漏洞，无法思考.

I can't seem to get it to work and im stuck in a code hole and cannot think straight.

我基本上希望日期从 01 到 30 增加一个，然后我将手动更改月份并在一年中的所有 12 个月再次运行脚本.

I basically want the date to up by one from 01-30 and then i will manually change the month and run the script again for all 12 months of the year.

推荐答案

考虑创建一个 日历表.

我在使用的任何数据库中创建的第一个表是一个数字表，从 1 到 1000000.这样的表有很多用途，例如在 SQL 中实现循环.此外，它还可用于生成我在使用的任何数据库上创建的第二个表:日历表.

The first table I create in any database I work with, is a table of numbers, 1 to 1000000. Such a table is useful for lots of purposes, like implementing loops in SQL. Also, it can be used to generate the second table I create on any database I work with: The Calendar table.

日历表的每个日期都有一行，从数据库中第一个记录的业务事件开始(加上一年左右).为所有相关的业务查询保留足够的未来日期(加上几年以确保安全).

The calendar table has one row for each date, starting at the first recorded business event in your database (plus a year or so). Keep enough future dates for all relevant business queries (plus a few years to be safe).

您的具体问题可以使用上述任一表来解决，但日历表方法会容易得多.

Your specific problem can be solved with either of the above tables, but the calendar table approach would be much easier.

我将在下面的 MySQL 中为您提供一个简单但有效的示例:

I'll give you a trivial but working example in MySQL below:

create table digits(n int);
insert into digits values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table numbers(
   n int not null
  ,primary key(n)
);

数字表只是一个工作表，一旦创建了实数表，它将被删除.数字表只有一列，它是主键.接下来，从 1 开始生成 100 万个连续整数.(这听起来很慢，但在我用了 2 年的笔记本电脑上，它实际上在 10 秒内完成).

The digits table is just a working table, it will be dropped once the real numbers table has been created. The numbers table has just one column, it's the primary key. Next, generate 1 million consecutive integers starting at 1. (It sounds slow, but it actually completes in under 10 sec on my 2 year old laptop).

insert 
  into numbers(n)
select 1 
      + (d1.n * 1)
      + (d2.n * 10)
      + (d3.n * 100)
      + (d4.n * 1000)
      + (d5.n * 10000)
      + (d6.n * 100000) as n
  from digits d1
      ,digits d2
      ,digits d3
      ,digits d4
      ,digits d5
      ,digits d6;

/* Drop the working table. */
drop table digits;

接下来，我将创建一个日历表.显然它目前非常无用，因为它没有任何有用的列.有用列的示例有 year、monthname、week number、isFirstMonthDay、isLastMonthDay、Financial Year、isHoliday、Holidayname、dayname、 Quarter、tertial.对于非标准时期，这是黄金时期.

Next, I'll create a calendar table. Obviously it is quite useless at the moment as it doesn't have any useful columns. Examples of useful columns are year, monthname, week number, isFirstMonthDay, isLastMonthDay, Financial Year, isHoliday, Holidayname, dayname, quarter, tertial. For non-standard periods, this is golden.

create table calendar(
   datum date not null
  ,primary key(datum)
);

好的，现在我们可以使用数字表作为行生成器来构建我们的日历表.假设我想生成 2000-01-01 和 2019-12-31 之间的所有日期.那将是 7305 天.很简单，只需从数字表中选择那么多行，然后将 int 列 N 添加到日期.这将创建一个递增日期列表.

Ok, so now we can use the numbers table for example, to act as a row generator to build our calendar table. Let's say I want to generate all dates between 2000-01-01 and 2019-12-31. That would be 7305 days. Easy, just select that many rows from the numbers table, and add the int column N to a date. This will create a list of increasing dates.

insert 
  into calendar(datum)
select date_add('1999-12-31', interval n day)
  from numbers
 where n <=7305;

完成.您可能会看到仅使用数字表是如何解决您的问题的?

Done. You could probably see how you could have solved your problem just by using the numbers table?

最后，这里有一个示例，说明如何使用日历表解决您的特定问题.如果您添加年份和月份的列，当然会更容易.在这里，我将您的问题解释为 在一年的剩余时间里，每个月的每一天都生成一个相同的行".

Finally, here is an example of how to solve your specific problem by using the calendar table. It would of course be even easier if you added columns for Year and Month. Here I'm interpreting your question to mean "Generate one identical row for each day of each month, for the rest of the year".

insert 
  into bs_events(
        eventDate,        eventTime,      title
       ,spaces,           description,    entryFee
       ,payment_required, max_qty,        allow_multiple
       )
 select datum,            '10:00',        'Carpet Cleaner 3000'
       ,'4',              'This is rad',  '25.99'
       ,'1',              '1',            '2'
   from calendar
  where datum >= date '2011-03-01'
    and datum <= date '2011-12-31';

这篇关于使用日期循环 SQL 插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！