通过MAX挑战LEFT OUTER JOIN查询分组

I have the following two tables:

我有以下两个表:

BillingMatrixDefinition
    - id
    - amount

BillingMatrix
   - definition (FK to table above)
   - service_id (FK)
   - provider_id (FK)
   - amount (Decimal)

I need to get all BillingMatrixDefinitions that have the service_id and provider_id that I specify. Here is the SQL query I currently have:

我需要获取具有我指定的service_id和provider_id的所有BillingMatrixDefinitions。这是我目前的SQL查询:

select def.id, service_id, provider_id, 
 (case when matrix.amount is not null then matrix.amount else def.amount end) amount
    from billing_billingdefinition def 
        left outer join billing_billingmatrix matrix
        on matrix.definition_id=def.id 
    where (service_id = 25 or service_id is null)
      and (provider_id = 24 or provider_id is null)

This gives me the following results:

这给了我以下结果:

id      service_id  provider_id   amount
1       25          24            200.00
1       NULL        24            300.00
2       NULL        24            800.00
3       NULL        NULL          750.00
5       NULL        NULL          450.00
6       NULL        NULL          750.00

However, I need to get the billing amount per id, so I can only get ONE item/amount for each id. In which case, I want to get the item where the service_id=24, and if that doesn't exist, then get it where service_id=NULL.

但是,我需要获得每个ID的计费金额,因此我只能为每个ID获得一个项目/金额。在这种情况下,我想获取service_id = 24的项目,如果不存在,则获取service_id = NULL的位置。

The correct query should give me the following results:

正确的查询应该给我以下结果:

id      service_id  provider_id   amount
1       25          24            200.00
2       NULL        24            800.00
3       NULL        NULL          750.00
5       NULL        NULL          450.00
6       NULL        NULL          750.00

Notice how now there is no duplicate entry for 1, and I use the line item where a service_id has been entered (use that one if it exists, else use NULL). What would be the correct query to do this?

注意现在如何没有1的重复条目,并且我使用输入了service_id的行项目(如果存在则使用该项目,否则使用NULL)。这样做的正确查询是什么?

6 个解决方案

#1

Anothr way:

SELECT 
    def.id                                              AS id, 
    COALESCE(matrix.service_id, matrix2.service_id)     AS service_id, 
    COALESCE(matrix.provider_id, matrix2.provider_id)   AS provider_id, 
    COALESCE(matrix.amount, matrix2.amount, def.amount) AS amount 
FROM 
        billing_billingdefinition AS def 
    LEFT JOIN 
        billing_billingmatrix AS matrix 
            ON  matrix.definition_id = def.id 
            AND matrix.service_id = 25 
            AND matrix.provider_id = 24 
    LEFT JOIN 
        billing_billingmatrix AS matrix2
            ON  matrix2.definition_id = def.id 
            AND matrix2.service_id IS NULL
            AND matrix2.provider_id = 24  ; 

#2

Try something along these lines (utilizing a temporary table):

尝试沿着这些方向(使用临时表):

CREATE TEMPORARY TABLE Results
select def.id, service_id, provider_id, 
 (case when matrix.amount is not null then matrix.amount else def.amount end) amount
    from billing_billingdefinition def 
        left outer join billing_billingmatrix matrix
        on matrix.definition_id=def.id 
    where (service_id = 25 or service_id is null)
      and (provider_id = 24 or provider_id is null);

SELECT * 
FROM Results r1
WHERE IFNULL(r1.service_id, 0) = 
      ( SELECT MAX(IFNULL(r2.service_id, 0)) 
        FROM Results r2 
        WHERE r2.id = r1.id
      );

SQL Fiddle for the 2nd part only (uses already created Results table)

SQL Fiddle仅适用于第二部分(使用已创建的结果表)

#3

You need to aggregate the amount using max() (and of course add a group-by clause) so you get the non-null value if one exists:

您需要使用max()聚合金额(当然还要添加group-by子句),以便获得非null值(如果存在):

select
    def.id, service_id, provider_id, 
    max(case when matrix.amount is not null then matrix.amount else def.amount end) amount
    from billing_billingdefinition def 
    left outer join billing_billingmatrix matrix
        on matrix.definition_id=def.id 
    where (service_id = 25 or service_id is null)
    and (provider_id = 24 or provider_id is null)
   group by def.id, service_id, provider_id

#4

Something like this might work as well.

这样的事情也可能有用。

select def.id, service_id, provider_id, IFNULL(matrix.amount,def.amount) amount
    from billing_billingdefinition def 
    left outer join (select definition_id, max(service_id) as maxsid from billing_billingmatrix matrix group by definition_id) as t1
        on def.id = t1.definition_id 
left outer join billing_billingmatrix matrix
    on matrix.definition_id=def.id and maxsid <=> service_id

#5

I believe PM 771's answer works here as well, but I decided to use a subselect in the OUTER JOIN table, to pre-filter the results before joining.

我相信PM 771的答案也适用于此,但我决定在OUTER JOIN表中使用子选择,以在加入前预先过滤结果。

Here is the final SQL that worked for this:

以下是适用于此的最终SQL:

SELECT *,
       (CASE WHEN matrix.amount IS NOT NULL THEN matrix.amount ELSE def.amount END) calculated_amount,
    FROM billing_billingdefinition def
    LEFT OUTER JOIN
       (SELECT t.* FROM (
        select * from billing_billingmatrix 
        where (provider_id=25 
        or provider_id is null) 
        and (service_id=24 or service_id is null)
        ORDER BY service_id DESC
          ) t GROUP BY t.definition_id) matrix
            ON matrix.definition_id=def.id

#6

Try this:

SELECT BMD.id, BM.service_id, BM.provider_id, IFNULL(BM.amount, BMD.amount) AS amount 
FROM BillingMatrixDefinition BMD
LEFT JOIN BillingMatrix BM ON BMD.id = BM.definition_id AND (BM.service_id = 25 OR BM.provider_id = 24)
GROUP BY BMD.id;