Каморка

CREATE TABLE Charge
(
	id INTEGER NOT NULL,
	code1 INTEGER NULL,
	code2 INTEGER NULL,
	code3 INTEGER NULL,
	amount INTEGER NOT NULL
);

id, code1, code2, code3 are not null in a request. I need to select only one amount with as many specified codes as possible. The table can have several records with any or all codes set to null for specific id, but duplicates are not allowed (i.e. there is no two records with id=#id#, code1=#code1#, code2=null, code3=null).
I think the following select is not the most efficient way to do it (#id#, #code1#, #code2#, #code3# are some values).

Код: Выделить всё

    SELECT amount FROM
      (
      SELECT 1 AS n, amount FROM Charge
      WHERE id=#id# AND code1=#code1# AND code2=#code2# AND code3=#code3#
      UNION
      SELECT 2 AS n, amount FROM Charge
      WHERE id=#id# AND code1=#code1# AND code2=#code2# AND code3 is null
      UNION
      SELECT 3 AS n, amount FROM Charge
      WHERE id=#id# AND code1=#code1# AND code2 is null AND code3=#code3#
      UNION
      SELECT 4 AS n, amount FROM Charge
      WHERE id=#id# AND code1=#code1# AND code2 is null AND code3 is null
      UNION
      SELECT 5 AS n, amount FROM Charge
      WHERE id=#id# AND code1 is null AND code2=#code2# AND code3=#code3#
      UNION
      SELECT 6 AS n, amount FROM Charge
      WHERE id=#id# AND code1 is null AND code2=#code2# AND code3 is null
      UNION
      SELECT 7 AS n, amount FROM Charge
      WHERE id=#id# AND code1 is null AND code2 is null AND code3=#code3#
      UNION
      SELECT 8 AS n, amount FROM Charge
      WHERE id=#id# AND code1 is null AND code2 is null AND code3 is null
      ORDER BY n
      LIMIT 1) AS s

Thanks.

In T-SQL consider something like this:

select top 1 amount
from Charge
where id=#id#
and coalesce(code1, #code1#)=#code1#
and coalesce(code2, #code2#)=#code2#
and coalesce(code3, #code3#)=#code3#
order by
case when code1 is not null then 1 else 0 end +
case when code2 is not null then 1 else 0 end +
case when code3 is not null then 1 else 0 end
desc

Yep, it works. Thanks!

Каморка

SQL question

SQL question