在INNER JOIN SQL查询上使用GROUP BY和ORDER BY [英] Using GROUP BY and ORDER BY on an INNER JOIN SQL query

问题描述

我正在使用以下查询从三个表中为客户分组工作时间和费用，一个表用于客户，一个表用于工作时间，一个用于费用:

I am using the following query to group work times and expenses for clients from three tables, one for clients, one for work times and one for expenses:

SELECT  a.*,
        COALESCE(b.totalCount, 0) AS CountWork,
        COALESCE(b.totalAmount, 0) AS WorkTotal,
        COALESCE(c.totalCount, 0) AS CountExpense,
        COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM    clients A
        LEFT JOIN
        (
            SELECT  Client, 
                    COUNT(*) totalCount,
                    SUM(Amount) totalAmount
            FROM    work_times
            WHERE   DATE BETWEEN '2013-01-01' AND '2013-02-01'
            GROUP   BY Client
        ) b ON a.Client = b.Client
        LEFT JOIN
        (
            SELECT  Client, 
                    COUNT(*) totalCount,
                    SUM(Amount) totalAmount
            FROM    expenses
            WHERE   DATE BETWEEN '2013-01-01' AND '2013-02-01'
            GROUP   BY Client
        ) c ON a.Client = c.Client
WHERE   b.Client IS NOT NULL OR
        c.Client IS NOT NULL

您可以在此处中看到查询工作.

You can see the query working in a fiddle here.

我正在尝试修改此查询，以使每个客户每个月都有一个行，并按月和客户进行排序.我正在尝试通过以下修改后的查询来做到这一点:

I am trying to amend this query so that there is a row for each client for each month sorted by month and then client. I am trying to do so with the following amended query:

SELECT  a.*,
        COALESCE(b.totalCount, 0) AS CountWork,
        COALESCE(b.totalAmount, 0) AS WorkTotal,
        COALESCE(c.totalCount, 0) AS CountExpense,
        COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM    clients A
        LEFT JOIN
        (
            SELECT  Client, 
                    COUNT(*) totalCount,
                    SUM(Amount) totalAmount,
                    SUBSTR(Date, 1, 7) as Month
            FROM    work_times
            GROUP   BY Month,Client
            ORDER BY Month
        ) b ON a.Client = b.Client
        LEFT JOIN
        (
            SELECT  Client, 
                    COUNT(*) totalCount,
                    SUM(Amount) totalAmount,
                    SUBSTR(Date, 1, 7) as Month
            FROM    expenses
            GROUP   BY Month,Client
            ORDER BY Month,Client
        ) c ON a.Client = c.Client
WHERE   b.Client IS NOT NULL OR
        c.Client IS NOT NULL

您可以在此处看到修改后的查询.

You can see the amended query in action here.

虽然工作不完全正确.尽管有2013年1月的工作时间和2013年2月的费用(因此应该有2行)，但客户B仅返回了一行，因此看来这些行是由客户订购的，而不是月份.有人可以建议如何修改查询以获得所需的输出，对于第二个小提琴中的示例，该输出将是:

It's not working quite right though. Only one row is returned for Client B even though there is a work time in January 2013 and an expense in February 2013 (so there should be 2 rows) and it appears that the rows are being ordered by Client as opposed to Month. Could someone suggest how to amend the query to get the desired output which for the example on the second fiddle would be:

╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗
║ CLIENT ║ COUNTWORK ║ WORKTOTAL ║ COUNTEXPENSE ║ EXPENSETOTAL ║
╠════════╬═══════════╬═══════════╬══════════════╬══════════════╣
║ A      ║         1 ║        10 ║            1 ║           10 ║
║ B      ║         1 ║        20 ║            0 ║            0 ║
║ A      ║         1 ║        15 ║            0 ║            0 ║
║ B      ║         0 ║        0  ║            1 ║           10 ║
║ C      ║         1 ║        10 ║            0 ║            0 ║
╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝

推荐答案

除非我在需求中缺少某些内容，否则您需要做的是获取客户和日期的列表，然后将其加入您的子查询中.因此，您的查询将是:

Unless I am missing something in the requirements, what you need to do is get a list of the clients and the dates and then join that to your subqueries. So your query will be:

SELECT a.*,
  COALESCE(b.totalCount, 0) AS CountWork,
  COALESCE(b.totalAmount, 0) AS WorkTotal,
  COALESCE(c.totalCount, 0) AS CountExpense,
  COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM 
(
  select distinct c.Client, d.Month
  from clients c
  cross join
  (
    select SUBSTR(Date, 1, 7) as Month
    from work_times
    union 
    select SUBSTR(Date, 1, 7) as Month
    from expenses
  ) d
) A
LEFT JOIN
(
  SELECT  Client, 
    COUNT(*) totalCount,
    SUM(Amount) totalAmount,
    SUBSTR(Date, 1, 7) as Month
  FROM    work_times
  GROUP   BY Month,Client
  ORDER BY Month,Client
) b 
  ON a.Client = b.Client
  and a.month = b.month
LEFT JOIN
(
  SELECT  Client, 
    COUNT(*) totalCount,
    SUM(Amount) totalAmount,
    SUBSTR(Date, 1, 7) as Month
  FROM    expenses
  GROUP   BY Month,Client
  ORDER BY Month,Client
) c 
  ON a.Client = c.Client
  and a.month = c.month
WHERE   b.Client IS NOT NULL OR
        c.Client IS NOT NULL
order by a.month, a.client

请参见带演示的SQL小提琴.

结果是:

| CLIENT |   MONTH | COUNTWORK | WORKTOTAL | COUNTEXPENSE | EXPENSETOTAL |
--------------------------------------------------------------------------
|      A | 2013-01 |         1 |        10 |            1 |           10 |
|      B | 2013-01 |         1 |        20 |            0 |            0 |
|      A | 2013-02 |         1 |        15 |            0 |            0 |
|      B | 2013-02 |         0 |         0 |            1 |           20 |
|      C | 2013-02 |         1 |        10 |            0 |            0 |

这篇关于在INNER JOIN SQL查询上使用GROUP BY和ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！