mysql - 如何从基于 where 子句和子查询的数据列表中派生标题

Question

我正在尝试生成一个从两个表派生的列表。最终结果应该是:

Date     A    OHS     OMSN    OMSS
date1    1     1        2       3
date2    4     5        6       7
date...   ..    ..      ..      ..

日期范围是根据我生成的日历表生成的，并且是正确连接的，因此即使其他建筑物的计数为 0，它们也会被包含在内。在每个建筑物下都有一个计数，应该简单地计算每个建筑物的工单天。这是我到目前为止的查询:

Select calendar.datefield As 'Date',
  Count(FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d')) As 'Count'
From workorders Right Join
  calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') =
      calendar.datefield)
Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d')
Group By calendar.datefield

此查询为我提供了特定范围内每天所有地点的工作订单总数。包括这个很好，但我想要再有 3 列，由每个特定位置过滤。如果我像这样添加建筑物名称:

Select calendar.datefield As 'Date', workorders.location,
  Count(FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d')) As 'Count'
From workorders Right Join
  calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') =
      calendar.datefield)
Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d')
Group By calendar.datefield, workorders.location

然后该表为我提供了我需要的数据，但没有提供我正在查找的列。数据将是这样的:

2011-09-08  OHS     27
2011-09-09  OHS     24
2011-09-10  NULL    0
2011-09-11  NULL    0
2011-09-12  OHS     23
2011-09-13  OHS     18
2011-09-13  OMS-N   1
2011-09-14  OHS     20

我想通过选择日期、然后是 OHS 的数字、OMSN 的数字、OMSS 的数字等来从此查询中提取信息...

必须有一种更简单的方法来做到这一点，我确实花了一整天的时间试图将这个查询拼凑在一起。

希望有人能帮忙。在过去的几个月里，我快速学习了 mysql，非常感谢任何帮助。

__进一步审查:

SELECT calendar.datefield, tOHS.Count AS 'OHS'
FROM calendar
LEFT JOIN (
SELTCT workorders.school, Count( workorders.dateSubmitted ) AS 'Count', FROM_UNIXTIME( workorders.dateSubmitted, '%Y-%m-%d' ) AS test
FROM workorders
RIGHT JOIN calendar ON ( FROM_UNIXTIME( workorders.dateSubmitted, '%Y-%m-%d' ) = calendar.datefield )
WHERE calendar.datefield
BETWEEN '2011-08-30'
AND date_format( now( ) , '%Y-%m-%d' )
AND School = 'OHS'
GROUP BY calendar.datefield, workorders.school
)tOHS ON calendar.datefield = tOHS.test
WHERE calendar.datefield
BETWEEN '2011-08-30'
AND date_format( now( ) , '%Y-%m-%d' )

这将返回第一个位置 OHS 的日期和每日计数。当我尝试这个时:

select calendar.datefield, tOHS.Count as 'OHS'
from calendar
LEFT JOIN 
(
Select workorders.school, Count(workorders.dateSubmitted) As 'Count', FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') as test
        From workorders Right Join
      calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') =
          calendar.datefield)
    Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d') AND School = 'OHS'
    Group By calendar.datefield,workorders.school
    ) tOHS
 on calendar.datefield = tOHS.test
 LEFT JOIN
 (
Select workorders.school, Count(workorders.dateSubmitted) As 'Count', FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') as test
        From workorders Right Join
      calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') =
          calendar.datefield)
    Where calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d') AND School = 'OMS-S'
    Group By calendar.datefield,workorders.school
    ) tOMSS
 on calendar.datefield = tOHS.test
 WHERE calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d')

它破坏了整个查询。我看到所有列都重复相同的日期和相同的值。我使用了错误的连接吗？

Answer 1

经过几个小时的研究和重新思考，我决定了如何做到这一点。基本上，我们需要选择每个字段(calendar.datefield、位置 1 的计数、位置 2 的计数、位置 3 的计数...)

为了获得计数，我对右连接到日历的子查询进行了 LEFT OUTER JOIN。这使得子查询返回每一天的计数(按每个日历日分组)，并将其左外连接(如几秒钟前提到的)到calendar.datefield，然后通过日期a和b之间的where子句进行过滤.

然后冲洗并重复每个子查询的左外连接。我不是 SQL 大师，也从未上过学，所以它可能没有优化或完美，但它可以工作，而且工作得相当快。我很高兴能弄清楚这一点。

    select calendar.datefield, (ifnull(tOHS.Count,0)+ifnull(tOMSS.Count,0)+ifnull(tOMSN.Count,0)) as 'Total', ifnull(tOHS.Count,0) as 'OHS', ifnull(tOMSS.Count,0) as 'OMS-S', ifnull(tOMSN.Count,0) as 'OMS-N'
from calendar

LEFT OUTER JOIN 
(
Select workorders.school, Count(workorders.dateSubmitted) As 'Count', FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') as 'Date'
        From workorders Right Join
      calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') =
          calendar.datefield)
    Where School = 'OHS'
    Group By calendar.datefield,workorders.school
    ) tOHS
 on calendar.datefield = tOHS.Date

 LEFT OUTER JOIN 
(
Select workorders.school, Count(workorders.dateSubmitted) As 'Count', FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') as 'Date'
        From workorders Right Join
      calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') =
          calendar.datefield)
    Where School = 'OMS-S'
    Group By calendar.datefield,workorders.school
    ) tOMSS
 on calendar.datefield = tOMSS.Date

 LEFT OUTER JOIN 
(
Select workorders.school, Count(workorders.dateSubmitted) As 'Count', FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') as 'Date'
        From workorders Right Join
      calendar On (FROM_UNIXTIME(workorders.dateSubmitted, '%Y-%m-%d') =
          calendar.datefield)
    Where School = 'OMS-N'
    Group By calendar.datefield,workorders.school
    ) tOMSN
 on calendar.datefield = tOMSN.Date


 WHERE calendar.datefield Between '2011-08-30' And date_format(now(), '%Y-%m-%d')

这将返回范围 a 到 b 的日期，并给出 3 座建筑物和每座建筑物的总计。我有 4 栋建筑物，但通过将每个 LEFT OUTER JOIN 复制并粘贴到 on 子句并更改变量，然后为该计数添加新的选择，可以轻松添加任意数量的建筑物。

哇，这东西可能会让人困惑。希望有一天它能帮助别人:-)

比尔