如何将日期与选择交叉? [英] How do I cross join the date to a selection?

问题描述

我需要一个查询，该查询将具有日期，用户名以及该日期的十二个operation_id之一.例如

I need a query that will have the date, a user's name, and one of twelve operation_id's for that date. eg

operations
"id";"name";
"1";"LASER CUTTING"
"2";"DEBURR"
"3";"MACHINING"
"4";"BENDING"
"5";"PEM"
"6";"WELDING"
"7";"PAINT PREPARATION"
"8";"PAINTING"
"9";"SILKSCREEN PREPARATION"
"10";"SILKSCREEN"
"11";"ASSEMBLY - PACKAGING"
"12";"LASER PREP";

和一个用户表

bob
jimmeh
jimbo

我在这些表之间进行交叉连接以获取如下信息:

I have a cross join between those tables to get something like this:

bob 1
bob 2
bob 3
bob 4
bob 5
bob 6
bob 7
bob 8
bob 9
bob 10
bob 11
bob 12
jimmeh 1
jimmeh 2
jimmeh 3
jimmeh 4
jimmeh 5
jimmeh 6
jimmeh 7
jimmeh 8
jimmeh 9
jimmeh 10
jimmeh 11
jimmeh 12
jimbo 1
jimbo 2
jimbo 3
jimbo 4
jimbo 5
jimbo 6
jimbo 7
jimbo 8
jimbo 9
jimbo 10
jimbo 11
jimbo 12

但是我也想在2011年1月1日到现在之间的每一天进行交叉联接，以便我可以使用此查询为我提供每一天每一操作的每个人的记录，以便我可以将其记录为枢纽.表，然后使用该表为每个用户每个操作的每周报告.

But I would also like to cross join every day between January 1st of 2011 and now so that I can use this query to give me a record for every person for every operation for every day so that I can put it in a pivot table and then use that to drive a report for each week for each user for each operation.

到目前为止，我从db.users联接操作中获得了一个简单的选择用户ID，其中Departmentid = 8

As of now I have a simple select userid from db.users join operations where departmentid = 8

我尝试过:

select 
  userid, 
    first_name, 
    last_name,
    operations.id,
    operations.name
from 
  protocase.tblusers 
join
  operations
join
  select (BETWEEN "2011-01-01" and NOW())
where 
  departmentid = 8 and 
    protocase.tblusers.active = 1

类似于人们将如何选择(1,2,3)或不是从表中选择的其他内容，但是我似乎无法弄清楚如何选择1月1日到现在之间的所有日期.这有可能吗?

Similar to how one would select (1,2,3) or something else that's not from a table, but I can't seem to figure out how I would select all the dates between January 1st and now. Is this even possible?

推荐答案

您可以创建这样的日期表

You could create a date table like this http://www.techrepublic.com/blog/datacenter/simplify-sql-server-2005-queries-with-a-dates-table/326

添加了存储过程以生成日期:

DROP PROCEDURE IF EXISTS datePopulate;
DELIMITER $$
CREATE PROCEDURE datePopulate( startDate datetime, numDays int)
BEGIN

declare currDate datetime default startDate;
declare i int default 1;

WHILE (i<=numDays) DO       

    INSERT INTO DateLookup(DateFull, fullYear, weekdayname) 
    VALUES(currDate, date_format(currDate, '%Y'), date_format(currDate, '%a'));
    SET i = i+1;
    SET currDate = DATE_ADD(currDate , INTERVAL 1 DAY);

END WHILE;

END $$
DELIMITER ;

过程一旦创建，就可以这样调用:

Once procedure is created, it can be called like this:

CALL datePopulate('2011-01-01', 30);

这将在2011年1月1日开始的30天内填充表格.

This will populate the table with 30 days starting at 2011-01-01.

我没有在insert语句中添加所有列.尽管使用

I didn't add all the columns in the insert statement. Should be pretty straight forward to add though using information from here.

这篇关于如何将日期与选择交叉?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！