由MAX挑战LEFT OUTER JOIN查询分组 [英] Challenging LEFT OUTER JOIN query grouping by MAX

问题描述

我有以下两张表：

  BillingMatrixDefinition 
  -  id 
  - 金额
 
 BillingMatrix 
  - 定义（FK到上面的表）
  -  service_id（FK）
  -  provider_id（FK）
  - 金额（十进制）
  

我需要获取所有具有 service_id 和<$的BillingMatrixDefinitions我指定的c $ c> provider_id 。这是我现在使用的SQL查询：

pre $ select def.id，service_id，provider_id，
.amount 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或service_id为空）
和（provider_id = 24或provider_id为空）


这给了我以下结果：

  id service_id provider_id金额
 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 
  

但是，我需要获得每个ID的结算金额，因此我只能收到 ONE 项/金额为每个 id 。在这种情况下，我想获取 service_id = 24 的项目，如果不存在，那么在 service_id = NULL 。

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

  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 
  

请注意，现在没有重复的条目1，我使用行项目service_id已经被输入（如果存在，使用那个，否则使用NULL）。

解决方案

Anothr方式：

  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金额
 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; 
  

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:

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.

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?

解决方案

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  ; 

这篇关于由MAX挑战LEFT OUTER JOIN查询分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！