Привет всем. Есть одна проблемма с решением формулы на SQL. Но никак не могу найти выход. Программа написана на VB.NET, а БД на SQL Server. Дело в следующем. На форме есть datagrid. В datagrid есть поля: First, second, third, four, five, six, finish, summa. В первый день в поле First записывается какое-то число (например 1000), во-второй день в поле second записывается другое число (например 30). Мне нужно чтобы поле summa равна First минус second. Это понятно. Но проблема в том, что когда в третий день в поле third записывается какое-то число (например 60), то summa была равна First минус third и при этом значение second оставалось не тронутым. И так далее до последнего дня (summa = first - finish). Суть в следующем. В поле First, значения записываются один раз, но в другие поля каждый день записывается значение и поле summa должна каждый день вычитать значения поля First от значения последнего поля. И самое главное, чтобы поля, которые содержать значения не обнулялись. Не знаю смог ли правильно задать вопрос, но если вопрос понятен, очень надеюсь на вашу помощь. И не имеет значения как можно решить задачу. На стороне sql или на стороне VB.NET. Главное как-то решить задачу. Спасибо заранее.
Технологии: Microsoft SQL Server, Visual Basic .NET
Если значения не обнулять намеренно, то само сбрасываться ничего не будет.
Поле summa тут вообще лишнее, если выводить данные, то можно его значение считать на лету:
SELECT (first - second) AS summa FROM tableName;
SELECT (first - third) AS summa FROM tableName;
SELECT (first - four) AS summa FROM tableName;
-- ..
SELECT (first - finish) AS summa FROM tableName;
Самое сложное в этой задаче - это определение, что вычитать от summa. Если бы данные хранились вертикально, было бы проще. Можно было бы номер дня записать в виде целого числа и просто брать нужно число. А с горизонтальной структурой придется писать дополнительные условия.
Если значения полей изначально равны NULL, а first, second, third, four, five, six, finish - это абсолютно любой день обращения к программе, то можно проверять по значению NULL, какой нужно считать день.
В T-SQL есть инструкция UNPIVOT, которая может горизонтальные данные превратить в вертикальные, что для решения данной задачи будет проще, если нет возможности переделывать структуру таблицы:
SELECT id, [day], value FROM
(SELECT id, [first], [second], [third], [four], [five], [six], [finish] FROM @table) AS p
UNPIVOT
(
value FOR [day] IN ([first], [second], [third], [four], [five], [six], [finish])
) AS unpvt;
Пример с временной таблицей:
DECLARE @table AS TABLE (id int primary key identity, [first] int, [second] int, [third] int, [four] int, [five] int, [six] int, [finish] int);
INSERT INTO @table ([first], [second], [third], [four], [five], [six], [finish])
VALUES (1000, 30, NULL, NULL, NULL, NULL, NULL);
SELECT id, [day], value FROM
(SELECT id, [first], [second], [third], [four], [five], [six], [finish] FROM @table) AS p
UNPIVOT
(
value FOR [day] IN ([first], [second], [third], [four], [five], [six], [finish])
) AS unpvt;
Результат:
id day value
----------- -------- -----------
1 first 1000
1 second 30
В реальных условиях, вместо @table нужно указывать существующую таблицу (только в запросе SELECT, инструкция DECLARE и INSERT не нужны, это для примера).
Можно сделать временную таблицу для вертикальных данных:
DECLARE @work AS TABLE (id int, [day] varchar(20), [value] int);
INSERT INTO @work
SELECT id, [day], value FROM
(SELECT id, [first], [second], [third], [four], [five], [six], [finish] FROM @table) AS p
UNPIVOT
(
value FOR [day] IN ([first], [second], [third], [four], [five], [six], [finish])
) AS unpvt;
И далее определить, какой день был последним и понять, для какого дня нужно считать сумму. Удобней всего, если first, second, third и т.п. будут числами, а не текстом:
DECLARE @table AS TABLE (id int primary key identity, [first] int, [second] int, [third] int, [four] int, [five] int, [six] int, [finish] int);
INSERT INTO @table ([first], [second], [third], [four], [five], [six], [finish])
VALUES (1000, 30, NULL, NULL, NULL, NULL, NULL);
DECLARE @work AS TABLE (id int, [day] int, [value] int);
INSERT INTO @work
SELECT id, [day], value FROM
(
SELECT id,
[first] AS [1], [second] AS [2], [third] AS [3],
[four] AS [4], [five] AS [5], [six] AS [6], [finish] AS [7] FROM @table
) AS p
UNPIVOT
(
value FOR [day] IN ([1], [2], [3], [4], [5], [6], [7])
) AS unpvt;
-- переменная для хранения значения первого дня
DECLARE @значениеПервогоДня int;
-- берем значение первого дня
SELECT TOP 1 @значениеПервогоДня = value FROM @work WHERE [day] = 1;
-- переменные для хранения номера последнего дня и значения
DECLARE @последнийДень int, @значениеПоследнегоДня int;
-- получаем последний день
SELECT TOP 1
@последнийДень = [day],
@значениеПоследнегоДня = value
FROM @work ORDER BY [day] DESC;
SELECT @последнийДень, @значениеПоследнегоДня
SELECT (@значениеПервогоДня - @значениеПоследнегоДня) AS summa;
Вот небольшая схема того, как это работает (надеюсь, не слишком запутано):
Результат выборки будет таким: Это два последних SELECT:
SELECT @последнийДень, @значениеПоследнегоДня
SELECT (@значениеПервогоДня - @значениеПоследнегоДня) AS summa;
Еще добавлю, в T-SQL есть функция SUM, которая суммирует значения указанных полей. Если нужно от first вычесть сумму остальных полей, то можно использовать эту функцию. Но данные должны быть вертикальными.
SELECT
(
(SELECT TOP 1 value FROM table WHERE day = 1) - (SELECT SUM(value) FROM table WHERE day <> 1)
) AS summa
![[at]](/Content/Themes/Native5x/Images/a.gif){kind=small}
kbyte.ru