Firebird, как выбрать идентификаторы, которые соответствуют всем элементам в наборе

Я использую Firebird 2.1.

Это стол: IDs, Labels

Для одного и того же идентификатора может быть несколько меток:

10 Peach
10 Pear
10 Apple
11 Apple
12 Pear
13 Peach
13 Apple

Допустим, у меня есть набор наклеек, а именно.: (яблоко, груша, персик).

Как я могу написать один выбор, чтобы вернуть все идентификаторы, которые имеют все метки, связанные в данном наборе? Желательно указать набор в строке, разделенной запятыми, например: ("Apple", "Pear", "Peach") - это должно вернуть ID = 10.

Спасибо!

3 ответа

Решение

Как я и просил, я публикую свою более простую версию ответа Пикроу. Я проверил это на своем Firebird, который является версией 2.5, но OP (Стив) проверил это на 2.1, и он также работает.

SELECT id
FROM table
WHERE label IN ('Apple', 'Pear', 'Peach')
GROUP BY id
HAVING COUNT(DISTINCT label)=3

Это решение имеет тот же недостаток, что и решение Pilcrow... вам нужно знать, сколько значений вы ищете, поскольку условие HAVING = должно соответствовать условию WHERE IN. В этом отношении ответ Эда является более гибким, поскольку он разбивает объединенный параметр строки значения и подсчитывает значения. Таким образом, вам просто нужно изменить один параметр вместо двух условий, которые я и Пилкоу используем.

OTOH, если речь идет об эффективности, я бы предпочел (но я абсолютно не уверен), что подход CTE Эда может быть менее оптимизируемым движком Firebird, чем тот, который я предлагаю. Firebird очень хорош в оптимизации запросов, но я не знаю, сможет ли он сделать это, когда вы используете CTE таким способом. Но WHERE + GROUP BY + HAVING следует оптимизировать, просто имея индекс (id,label).

В заключение, если время выполнения имеет значение в вашем случае, то вам, вероятно, нужны некоторые планы объяснения, чтобы увидеть, что происходит, какое бы решение вы ни выбрали;)

Проще всего разбить строку в коде и затем запросить

SQL> select ID
CON>   from (select ID, count(DISTINCT LABEL) as N_LABELS
CON>           from T
CON>          where LABEL in ('Apple', 'Pear', 'Peach')
CON>          group by 1) D
CON>  where D.N_LABELS >= 3;  -- We know a priori we have 3 LABELs

          ID 
 ============ 
           10 

Если допустимо создать вспомогательную хранимую процедуру, которая будет вызываться из основного выбора, рассмотрите следующее.

Хранимая процедура Helper принимает строку с разделителями вместе с разделителем и возвращает строку для каждой строки с разделителями

CREATE OR ALTER PROCEDURE SPLIT_BY_DELIMTER (
    WHOLESTRING VARCHAR(10000),
    SEPARATOR VARCHAR(10))
RETURNS (
    ROWID INTEGER,
    DATA VARCHAR(10000))
AS
DECLARE VARIABLE I INTEGER;
BEGIN
    I = 1;   
    WHILE (POSITION(:SEPARATOR IN WHOLESTRING) > 0) DO
    BEGIN
        ROWID = I;
        DATA = TRIM(SUBSTRING(WHOLESTRING FROM 1 FOR POSITION(TRIM(SEPARATOR) IN WHOLESTRING) - 1));        
        SUSPEND;      
        I = I + 1;
        WHOLESTRING = TRIM(SUBSTRING(WHOLESTRING FROM POSITION(TRIM(SEPARATOR) IN WHOLESTRING) + 1));
    END
    IF (CHAR_LENGTH(WHOLESTRING) > 0) THEN
    BEGIN
        ROWID = I;
        DATA = WHOLESTRING;
        SUSPEND;
    END
END

Ниже приведен код для вызова, я использую блок Execute, чтобы продемонстрировать передачу строки с разделителями

EXECUTE BLOCK
RETURNS (
    LABEL_ID INTEGER)
AS
DECLARE VARIABLE PARAMETERS VARCHAR(50);
BEGIN
  PARAMETERS = 'Apple,Peach,Pear';

  FOR WITH CTE
  AS (SELECT ROWID,
             DATA
      FROM SPLIT_BY_DELIMITER(:PARAMETERS, ','))
  SELECT ID
  FROM TABLE1
  WHERE LABELS IN (SELECT DATA
                   FROM CTE)
  GROUP BY ID
  HAVING COUNT(*) = (SELECT COUNT(*)
                     FROM CTE)
  INTO :LABEL_ID
  DO
    SUSPEND;
END
Другие вопросы по тегам