Проблема с оператором SQL
У меня есть такой код:
SELECT idcallhistory3, callid, starttime, answertime, endtime, duration,
is_answ, is_fail, is_compl, is_fromoutside, mediatype, from_no,
to_no, callerid, dialednumber, lastcallerid, lastdialednumber,
group_no, line_no
FROM "public".callhistory3
WHERE (starttime >= ?) AND (endtime <= ?) AND (is_fromoutside = ?)
AND (from_no = ?) AND (to_no = ?)
Проблема в том, что мне нужно передать одно значение? и получить весь результат без фильтра, что-то вроде *
Любая помощь?
Ответы 6
Типичный способ сделать это выглядит примерно так:
WHERE (? IS NULL OR starttime >= ?)
а затем передать DBNull.Value. Очевидно, вам нужно сделать это для каждого параметра, для которого вы хотите иметь возможность использовать такой «подстановочный знак».
Леппи: Правда? Я ожидал, что любая серьезная база данных сможет так хорошо оптимизировать. Это не совсем сложная оптимизация, учитывая, что значение параметра не будет меняться на каждой итерации. Левая часть OR либо гарантирует совпадение, либо может быть проигнорирована в рамках всего запроса.
По отдельности нормально, но в сочетании (5 в OP) я видел, как это ведет себя очень плохо - по-видимому, не удается найти соответствующий индекс.
Я использовал это для больших наборов данных со сложными запросами с разумной производительностью. В MSSQL это
Из-за сниффинга параметров MS-SQL может изначально выбрать план выполнения, соответствующий начальным значениям, но это невероятно плохо для последующих запусков. Я не совсем уверен, что обнюхивание параметров является проблемой со специальным SQL, но, поскольку планы специальных запросов все еще кешируются, я ожидал, что так и будет.
WHERE
(@start is null OR starttime >= @start) AND
(@end is null OR endtime <= @end) AND
(@fromOutside is null OR is_fromoutside = @fromOutside) AND
(@fromNo is null OR from_no = @fromNo) AND
(@toNo is null OR to_no = @toNo)
Передайте нули для всех параметров (dang sql nulls; спасибо GC).
Использование «is null» - «= null» всегда ложно, потому что null! = Null.
Фактически, "= null" должно возвращать null. Приравнивается ли неизвестное к другому неизвестному, неизвестно.
Верно, но конечный эффект тот же - эта половина оператора фильтра никогда не вступает в силу, в результате чего значения NULL не совпадают, когда они должны совпадать.
Добавьте оператор "Где" условно - только если вам нужно отфильтровать результаты.
Для сложных запросов с несколькими необязательными разделами вы можете обнаружить, что лучше создать соответствующий SQL. Вы можете сделать это либо в вызывающей стороне (например, в C#), либо в базе данных (по крайней мере, с SQL Server), но в любом случае вы должен убедитесь, что он остается параметризованным. Когда вызывающий абонент выполняет работу, это всего лишь случай добавления подходящих параметров к команде. Если база данных генерирует TSQL, то подход зависит от СУБД. С SQL-сервером sp_ExecuteSql - ваш друг - т.е. вы можете создать переменную @cmd на основе запроса, а затем:
EXEC sp_ExecuteSQL @cmd, N'@someArg int', @actualArg
Где @someArg - это объявление внутри @cmd, а @actualArg - это значение, передаваемое во время выполнения.
Мне нравится COALESCE.
Вам просто нужно быть осторожным с нулевыми значениями с левой стороны, если могут быть нули с левой стороны, вы можете сделать что-то вроде последней строки, чтобы нули совпадали. Обычно с чем-то подобным, хотя вы захотите убедиться, что ваш запрос по-прежнему работает нормально.
SELECT idcallhistory3, callid, starttime, answertime, endtime, duration,
is_answ, is_fail, is_compl, is_fromoutside, mediatype, from_no,
to_no, callerid, dialednumber, lastcallerid, lastdialednumber,
group_no, line_no
FROM "public".callhistory3
WHERE (starttime >= COALESCE(@starttime, starttime ))
AND (endtime <= COALESCE(@endtime, endtime))
AND (is_fromoutside = COALESCE(@is_fromoutside, is_fromoutside))
AND (from_no = COALESCE(@from_no, from_no))
AND (COALESCE(to_no, -1) = COALESCE(@to_no, to_no, -1)) -- make nulls match
Я согласен с Джамалом Хансеном. COALESCE - безусловно, лучший способ, по крайней мере, на SQL Server.
Это неплохо, но «ИЛИ» может помешать использованию индексов в столбце. Конечно, это зависит от конкретной СУБД.