postgresql - Prioritizing one row over another, if it exists - Stack Overflow

admin管理员组

文章数量:1201412

I have a commission_rates table with the following data:

id	kind	rate	merchant_id	publisher_id
1	standard	0.15	1	NULL
2	standard	0.15	2	NULL
3	repeat_customer	0.1	3	NULL
4	custom	0.5	3	408
5	standard	0.08	3	NULL

I have a commission_rates table with the following data:

id	kind	rate	merchant_id	publisher_id
1	standard	0.15	1	NULL
2	standard	0.15	2	NULL
3	repeat_customer	0.1	3	NULL
4	custom	0.5	3	408
5	standard	0.08	3	NULL

A row where publisher_id is null means that this rate applies to all publishers. So, if my publisher ID was 10 and I wanted to see a result set of applicable commissions for that publisher, it would return rows 1,2,3 and 5 i.e. not row 4 because that row is a rate customization for publisher ID 408.

The idea is to avoid explicitly defining a rate for every merchant <> publisher relationship. The rates for each publisher <> merchant are 99% the same, but for a few customizations where a different rate was agreed between the two parties. So a null value in the publisher_id column means that this is the rate used for all publishers.

A row where publisher_id is not null means that this publisher has a customized rate for the merchant.

For example, row 4 features a custom rate for publisher 408. If we query for publisher 408, row 4 should be returned in place of the standard rate for all publishers (row 5).

So a query for publisher 408 would return:

id	kind	rate	merchant_id	publisher_id
1	standard	0.15	1	NULL
2	standard	0.15	2	NULL
3	repeat_customer	0.1	3	NULL
4	custom	0.5	3	408

But a query for any other publisher would return:

id	kind	rate	merchant_id	publisher_id
1	standard	0.15	1	NULL
2	standard	0.15	2	NULL
3	repeat_customer	0.1	3	NULL
5	standard	0.08	3	NULL

• postgresql

Share Improve this question Follow edited Jan 21 at 23:42 Tim Fletcher asked Jan 21 at 22:30 Tim FletcherTim Fletcher 7,38611 gold badge3737 silver badges3333 bronze badges 7

• Perhaps a rethink of my data model is in order... – Tim Fletcher Commented Jan 21 at 22:31
• 1 Your edit lost me. How are you deciding that merchant_id = 3 gets the custom rate when the custom rate has no merchant_id associated with it? Also you need to explain further the relationship between publisher and merchant. Then there is A row where publisher_id is null means that this rate applies to all publishers.. If a publisher_id is null how is related to a publisher? – Adrian Klaver Commented Jan 21 at 23:25
• Ugh. I knew I would mess up that edit. Row 4's merchant_id should be 3. I've attempted to clarify the null is all publishers idea. Thanks. – Tim Fletcher Commented Jan 21 at 23:42
• So, isn't WHERE publisher_id=my_id OR publisher_id IS NULL enough? – Tim Roberts Commented Jan 21 at 23:44
• @TimRoberts. That would not work as you would get custom 0.5 3 408 and standard 0.08 3 NULL. – Adrian Klaver Commented Jan 21 at 23:49

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 1

You seem to be looking for

SELECT *
FROM commission_rates cr_outer
WHERE publisher_id = 408
   OR publisher_id IS NULL AND kind <> 'standard'
   OR publisher_id IS NULL AND NOT EXISTS(
        SELECT *
        FROM commission_rates cr_inner
        WHERE cr_outer.merchant_id = cr_inner.merchant_id
          AND cr_inner.publisher_id = 408
      );

本文标签： postgresqlPrioritizing one row over anotherif it existsStack Overflow