Как я могу написать этот код без использования переменных
У меня есть следующий код, который проверяет площадь поверхности продукта и выдает число. Однако это очень эффективно. Как я могу написать этот код с использованием Case When, then вместо переменных и if else?
DECLARE @test DECIMAL(10,2)
SELECT
@test = ([WIDTH] / 1000) * ([HEIGHT] / 1000)
FROM
MAIN.SYSADM.[TABLE]
WHERE
ID = 854037
AND POS_NR = 1
IF (@test > 1)
BEGIN
(
SELECT
TOP(1)
360 / SUM(CAST([QTY] AS INT))
FROM
MAIN.SYSADM.[TABLE]
WHERE
ID = 854037
AND POS_NR = 1
AND BOM_PRODUKT NOT IN (52101,52102,52006,52007,52003,52005,52008,53201,53102)
AND (
STL_PRODGRP IN (26,27,28,30,33,35,412,413,415,425,426,427)
OR BOM_PRODUKT = 50002
)
GROUP BY
BOM_NODE
)
END
ELSE
BEGIN
(
SELECT
TOP(1)
180 / SUM(CAST([QTY] AS INT))
FROM
MAIN.SYSADM.[TABLE]
WHERE
ID = 854037
AND POS_NR = 1
AND BOM_PRODUKT NOT IN (52101,52102,52006,52007,52003,52005,52008,53201,53102)
AND (
STL_PRODGRP IN (26,27,28,30,33,35,412,413,415,425,426,427)
OR BOM_PRODUKT = 50002
)
GROUP BY
BOM_NODE
)
END
Я пробовал это, но top(1) и GROUP by не работают.
SELECT
(
SELECT
TOP(1)
(
CASE
WHEN CAST(([WIDTH] / 1000) * ([HEIGHT] / 1000) AS DECIMAL(10,2)) > 1
THEN 360 / SUM(CAST([QTY] AS INT))
ELSE 180 / SUM(CAST([QTY] AS INT))
END
)
)
FROM
MAIN.SYSADM.[TABLE]
WHERE
[ID] = 854037
AND POS_NR = 1
AND BOM_PRODUKT NOT IN (52101,52102,52006,52007,52003,52005,52008,53201,53102)
AND (
STL_PRODGRP IN (26,27,28,30,33,35,412,413,415,425,426,427)
OR BOM_PRODUKT = 50002
)
GROUP BY
BOM_NODE
Здесь есть много вещей, которые вызывают у меня вопросы. Почему вы храните Qty как нечто иное, чем int? Вы понимаете целочисленную математику? Вы делаете какие-то странные вещи с делением и приведением десятичных дробей, чтобы определить, больше ли результат целого числа. По сути, ваши типы данных явно разбросаны повсюду, и работать с ними неинтересно.
Возможно, я это упускаю, но разве между вашими двумя запросами нет единственной разницы: дивиденд 180 или 360?
However, it's very efficient. это хорошо, нет
С таким же успехом вы можете использовать width * height > 1000000, если только вы на самом деле каким-то образом не полагаетесь на целочисленное деление, что кажется сомнительным, учитывая приведение к decimal.
В каком смысле это не сработало? Я предполагаю, что он начал создавать несколько строк, а не одну.
Я понимаю это. Сообщение 8120, уровень 16, состояние 1, строка 3. Столбец «MAIN.SYSADM.TABLE.WIDTH» недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY. Сообщение 8120, уровень 16, состояние 1, строка 3. Столбец «MAIN.SYSADM.TABLE.STL_HEIGHT» недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.
Мне нужно выяснить, куда поместить Top(1), чтобы он работал с Group by.
Какова цель расчета всей ширины/высоты? У вас явно есть несколько строк, возвращающихся в результате этого поиска, но выбор переменной маскирует это, молча сохраняя только «последнюю» (которая является произвольной без order by). Вам нужно выяснить, что вам нужно запросить, прежде чем пытаться закрепите его top.
У вас синтаксическая ошибка в первом запросе. Он даже не запустится: выберите @test = ([WIDTH]/1000)*(HEIGHT]/1000)
Пробелы и форматирование имеют значение. Отредактированный код. Добавлен недостающий [ в [HEIGHT].
Ответы 1
Если ваши данные содержат несколько строк с ID = 854037 и POS_NR = 1, ваш расчет @test фактически вычисляет несколько результатов, последний из которых сохраняется в операторе select @test = .... Если каждая такая строка имеет одинаковые значения WIDTH и HEIGHT, все результаты будут одинаковыми, и не имеет значения, какой из них выбран.
Как только вы поместите это вычисление в предложение WHEN выражения CASE, будет разрешен только один результат. Если все результаты одинаковы, вы можете применить MIN(), MAX() или TOP 1, чтобы ограничить результаты только этим одним значением.
Обновленный запрос будет выглядеть примерно так:
select --top(1)
BOM_NODE,
SUM(QTY) as SumQty,
MAX((WIDTH/1000)*(HEIGHT/1000)) as MaxArea,
case when (
select MAX((WIDTH/1000)*(HEIGHT/1000))
from [TABLE]
where ID=854037 and POS_NR=1
) > 1
then 360/SUM(CAST(QTY as int))
else 180/SUM(CAST(QTY as int))
end as Result
from [TABLE]
where ID = 854037
and POS_NR = 1
and BOM_PRODUKT not in (52101,52102,52006,52007,52003,52005,52008,53201,53102)
and (
STL_PRODGRP in (26,27,28,30,33,35,412,413,415,425,426,427)
or BOM_PRODUKT = 50002
)
group by BOM_NODE
Я закомментировал top 1 и добавил несколько дополнительных столбцов, чтобы показать некоторые промежуточные результаты. Я также подчистил часть форматирования для улучшения читабельности.
Используя некоторые сгенерированные мной тестовые данные, я получил следующие результаты:
См. эту db<>fiddle для демонстрации.
Я также считаю, что у вас возникнут серьезные проблемы с проектированием базы данных, если вы храните атрибуты продукта (например, ширину и высоту) в той же таблице, что и количества. Я предлагаю вам прочитать о «нормализации базы данных» и применить некоторые из этих методов для улучшения вашей модели данных.
Выражения типа CAST(QTY as int) также вызывают подозрения. Вы храните числовые значения в виде текста? Не.
Обратите внимание, что
TOPбезORDER BY— верный признак недостатка. Это означает, что механизм данных может возвращать любые произвольные строки, которые он хочет, и строки могут быть разными каждый раз, когда вы запускаете указанный запрос. Если вы используетеTOP, вам необходимо убедиться, что запрос имеет детерминированностьORDER BY, чтобы вы получали последовательные и надежные результаты.