mySQL查询多个分组依据

Question

如何将这两个查询合并为1，并在大表上高效运行？

SELECT  field1, count(1) as requestCount
FROM table1
WHERE date_complete >= '2012-06-12 00:00:00' 
      AND date_complete <= '2012-07-12 23:59:59' 
GROUP BY field1

SELECT field2, count(1) as completeCount
FROM table1
WHERE date_complete >= '2012-06-12 00:00:00' 
      AND date_complete <= '2012-07-12 23:59:59' 
GROUP BY field2

表保存涉及多个用户的进程的信息。例如，假设第一个人创建了请求，第二个人完成了请求，第三个人通过提交来结束请求。

我想要计算每个用户在特定的时间范围内请求、完成和归档的数量

我希望这两个组合看起来像

+----------------+--------------+
| field1         | requestCount |
+----------------+--------------+
| PJB            |            1 |
| RFD            |            6 |
| YAS            |            4 |
+----------------+--------------+

+

+---------+---------------+
| field2  | completeCount |
+---------+---------------+
| PJB     |            4  |
| YAS     |            5  |
+---------+---------------+

=

+----------------+--------------+---------------+
| Username       | requestCount | completeCount |
+----------------+--------------+---------------+
| PJB            |            1 |             4 |
| RFD            |            6 |             0 |
| YAS            |            4 |             5 |
+----------------+--------------+---------------+

Answer 1

select  o.USERNAME, a.requestCount , b.completeCount from (SELECT  USERNAME
FROM owner_login_pass )
 as o left join (SELECT field1, count(1) as requestCount
FROM table1
WHERE date_complete between '2012-06-12 00:00:00' and '2012-07-12 23:59:59' 
GROUP BY field1) as a on o.USERNAME=a.field1
left join (SELECT field2, count(1) as completeCount
FROM table1
WHERE date_complete between '2012-06-12 00:00:00' and '2012-07-12 23:59:59' 
GROUP BY field2) as b on o.USERNAME=b.field2

您需要确保field1包含您需要为其显示数据的所有用户。否则，您还需要连接到用户表。

Answer 2

我在这里只是猜测，但我认为你需要：

SELECT
    u .Username
  , t1.requestCount
  , t2.completeCount
FROM
        ( SELECT field1 AS Username
          FROM table1
          UNION
          SELECT field2
          FROM table2
        ) AS u
    LEFT JOIN
        ( SELECT field1
               , COUNT(*) AS requestCount
          FROM table1
          WHERE date_complete >= '2012-06-12' 
            AND date_complete < '2012-07-13' 
          GROUP BY field1
        ) AS t1  
            ON t1.field1 = u.Username
    LEFT JOIN
        ( SELECT field2
               , COUNT(*) AS completeCount
          FROM table1
          WHERE date_complete >= '2012-06-12' 
            AND date_complete < '2012-07-13' 
          GROUP BY field2 
        ) AS t2
            ON t2.field2 = u.Username   
;

如果您有user表，只需用user AS u替换第一个派生表

Answer 3

我不认为您可以将它们组合在一起(除了使用UNION)，但是您可以通过使用：WHERE date_complete BETWEEN '2012-06-12 00:00:00' AND '2012-07-12 23:59:59'略微改进它。