合并MySQL中的查询

问题描述：

As you can see down below, I got this query that gets the average of how long it takes to approve a number of certain forms. I'm filtering out by giving a date range, and the date range needs to include only business days (Mon-Fri) using the Calendar table.

SELECT 
fg2.form_descr AS 'form_description',

CONCAT(
    FLOOR(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed)))) / 24), ' days ',
    MOD(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed )))), 24), ' hours ',
    MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed)))), ' minutes'
) AS 'Average Approval Time',

COUNT(user_forms.form_id) AS ' number_of_forms'

FROM

forms.user_forms,
forms.forms,
forms.form_groups fg,
forms.form_groups fg2

WHERE
    user_forms.form_id = forms.form_id
    AND forms.form_group = fg.form_group_id
    AND fg.approver_group = fg2.form_group_id
    AND viewed > submitted
    AND submitted BETWEEN '2015-01-01' AND '2015-06-30'

GROUP BY 
    fg.approver_group

The results is something like:

form_description      Average Approval Time         number_of_forms

'Log In'             '1 days 2 hours 04 minutes'    '35'
'Programming'        '2 days 5 hours 22 minutes'    '100'
...and so on

Now, I have a table called Calendar which consists of two columns. One is date, and the other is is_holiday with no primary id and/or foreign key. The Calendar table is nothing but a list of dates that tells you if it is a holiday or not. Example:

Date           is_holiday

'2014-01-01'   '0'
'2014-01-02'   '0'
'2014-01-03'   '0'
and so on...

SELECT date 
FROM calendar
WHERE DAYOFWEEK(date) NOT IN (1,7) 
AND date BETWEEN '2015-01-01' AND '2015-06-30'
AND is_holiday = 0

How can I merger these 2 queries together? I have tried using two subqueries in the FROM clause, but I can't seem to get this. And yes, I did tried looking around, but I can't see to get this right.

正如您在下面看到的那样，我得到了这个查询，它可以获得批准数字所需时间的平均值某些形式。我通过提供日期范围进行过滤，日期范围只需要包含使用日历表的工作日（周一至周五）。 p>

  SELECT 
fg2.form_descr  AS'form_description'，
 
CONCAT（
 FLOOR（HOUR（SEC_TO_TIME（AVG（TIMESTAMPDIFF（SECOND，提交，查看））））/ 24），'days'，
 MOD（HOUR（SEC_TO_TIME）（AVG（TIMESTAMPDIFF）  （第二，提交，查看）））），24），'小时'，
分钟（SEC_TO_TIME（AVG（TIMESTAMPDIFF（第二，提交，查看）））），'分钟'
）AS'平均批准时间'  ，
 
 
COUNT（user_forms.form_id）AS'number_of_forms'
 
FROM 
 
forms.user_forms，
forms.forms，
forms.form_groups fg，
forms.form_groups fg2 
 
WHERE 
 user_forms.form_id  = forms.form_id 
 AND forms.form_group = fg.form_group_id 
 AND fg.approver_group = fg2.form_group_id 
 AND views＆gt; 提交
 AND提交BETWEEN'2015-01-01'和'2015-06-30'
 
GROUP BY 
 fg.approver_group 
  code>  pre> 
 
 结果 类似于： p> 
 
 
  form_description平均批准时间number_of_forms 
 
'登录''1天2小时04分钟''35'
'编程''2天 5小时22分钟''100'
 ...依此类推
  code>  pre> 
 
 现在，我有一个名为Calendar的表，它包含两列。 一个是日期，另一个是is_holiday，没有主要ID和/或外键。  Calendar表只是一个日期列表，告诉您它是否是假日。
例如： p> 
 
 
  Date is_holiday 
 
'2014-01-01  ''0'
'2014-01-02''0'
'2014-01-03''0'
等等... 
 
SELECT date 
FROM calendar 
WHERHER DAYOFWEEK（date）NOT  IN（1,7）
AND date BETWEEN'2015-01-01'''2015-06-30'
AND is_holiday = 0 
  code>  pre> 
 
 我该怎么办？ 合并这两个查询在一起？ 我尝试在FROM子句中使用两个子查询，但我似乎无法得到它。 是的，我确实试过环顾四周，但我看不出这一点。 p> 
  div>

答

You should be able to get what you want by joining the calendar table. Something like this may do it:

 SELECT 
    fg2.form_descr AS 'form_description',

    CONCAT(
        FLOOR(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed)))) / 24), ' days ',
        MOD(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed )))), 24), ' hours ',
        MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, dateSubmitted, viewed)))), ' minutes'
    ) AS 'Average Approval Time',

    COUNT(user_forms.form_id) AS ' number_of_forms'

    FROM

    forms.user_forms,
    forms.forms,
    forms.form_groups fg,
    forms.form_groups fg2
    forms.calendar c

    WHERE
        user_forms.form_id = forms.form_id
        AND forms.form_group = fg.form_group_id
        AND fg.approver_group = fg2.form_group_id
        AND c.`date` = date(viewed)
        AND viewed > submitted
        AND viewed BETWEEN '2015-01-01' AND '2015-06-30'
        AND dayofweek(viewed) NOT IN (1,7)
        AND c.is_holiday = 0

GROUP BY 
    fg.approver_group

This should exclude any rows where 'viewed' falls on either a holiday or a weekend.

Unless, of course, what you want to do is exclude any days that are a holiday or a weekend from these calculations: TIMESTAMPDIFF(SECOND, submitted, viewed) in which case its an entirely different kettle of fish.

Assuming that a form is never submitted, or viewed, on a weekend or public hoilday (big assumption), all you need to do is subtract the number of days that fall into that category, and lay between submitted and viewed. This should do that.

     SELECT 
            fg2.form_descr AS 'form_description',

            CONCAT(
                FLOOR(
                  HOUR(SEC_TO_TIME(
                    AVG(
                      TIMESTAMPDIFF(SECOND, submitted, viewed)))) / 24) -
                      (SELECT(COUNT(*) 
                         FROM calendar 
                         WHERE `date` BETWEEN submitted AND viewed 
                           AND (DAYOFWEEK(`date`) in (1,7) 
                             OR is_holiday = 1)
                      ), ' days ',
                MOD(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed )))), 24), ' hours ',
                MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, dateSubmitted, viewed)))), ' minutes'
            ) AS 'Average Approval Time',
   COUNT(user_forms.form_id) AS ' number_of_forms'

        FROM

        forms.user_forms,
        forms.forms,
        forms.form_groups fg,
        forms.form_groups fg2
        forms.calendar c

        WHERE
            user_forms.form_id = forms.form_id
            AND forms.form_group = fg.form_group_id
            AND fg.approver_group = fg2.form_group_id
            AND c.`date` = date(viewed)
            AND viewed > submitted
            AND viewed BETWEEN '2015-01-01' AND '2015-06-30'    
    GROUP BY 
        fg.approver_group

here is a demo query on some fake data showing how this theory works. it should apply to what you are doing.

答

This is untested, But should work. I'm using [ ... ] to shorten the code down to just the new pieces - leave your original query as-is.

SELECT
    [ ... ],
    is_holiday

FROM
    [ ... ],
    calendar ON calendar.Date = viewed

WHERE
    [ ... ]
    AND calendar.Date = viewed

GROUP BY
    [ ... ]

Essentially you're just joining the calendar table where the dates match and grabbing the corresponding is_holiday field.

If you're looking specifically for holidays use AND calendar.Date <> '0' (or use AND calendar.Date = '0' if you want non-holidays) after calendar.Date = viewed

Also, you don't need the lookup table to figure if the date is a weekend - it's the same date as in the first table.

DAYOFWEEK(viewed) NOT IN (1,7)

Edit

Here it is altogether.

SELECT 
fg2.form_descr AS 'form_description',

CONCAT(
    FLOOR(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed)))) / 24), ' days ',
    MOD(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed )))), 24), ' hours ',
    MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed)))), ' minutes'
) AS 'Average Approval Time',

COUNT(user_forms.form_id) AS ' number_of_forms',
is_holiday

FROM

forms.user_forms,
forms.forms,
forms.form_groups fg,
forms.form_groups fg2,
calendar ON calendar.Date = viewed

WHERE
    user_forms.form_id = forms.form_id
    AND forms.form_group = fg.form_group_id
    AND fg.approver_group = fg2.form_group_id
    AND viewed > submitted
    AND submitted BETWEEN '2015-01-01' AND '2015-06-30'
    AND calendar.Date = viewed
    AND is_holiday = '0'
    AND DAYOFWEEK(calendar.Date) NOT IN (1,7)

GROUP BY 
    fg.approver_group

合并MySQL中的查询

相关推荐