Подсчет столбцов с данными
У меня есть запрос на получение месячных данных из SQL Server. Мне нужно добавить дополнительный столбец «Количество», который фиксирует количество всех столбцов со значением больше «0».
Вот SQL-запрос, который я попробовал, однако вскоре понял, что это выражение case может быть не оптимальным способом для захвата всех возможных комбинаций.
Может ли кто-нибудь предложить лучшее решение?
SELECT
, P.January as 'Jan'
, P.February as 'Feb'
, P.March as 'Mar',
CASE
WHEN P.January > 0 AND P.February > 0 AND P.March > 0 THEN '3'
WHEN P.January < 0 AND P.February > 0 AND P.March > 0 THEN '2'
....
ELSE ''
END as 'Count'
знак (январь) + знак (февраль) + знак (март) Если отрицательные значения, вы можете обернуть каждый столбец в abs()
SELECT COUNT(Col1), COUNT(Col2), COUNT(Col3) и т.д...
ИЗ какойтаблицы
Ответы 4
Если нет отрицательных значений, мы можем воспользоваться тем фактом, что CONVERT(bit, 13) дает 1, и взять SUM из CROSS APPLY:
SELECT s.Jan, s.Feb, s.Mar,
[Count] = SUM(CONVERT(int, CONVERT(bit, x.v)))
FROM dbo.YourTableName AS s
CROSS APPLY (VALUES(s.Jan),(s.Feb),(s.Mar)) AS x(v)
GROUP BY s.Jan, s.Feb, s.Mar;
- Пример db<>рабочий пример
Если у вас есть отрицательные значения, которые не должны учитываться, вы можете просто добавить col + ABS(col), чтобы изменить отрицательные значения на 0, удвоить положительные значения и оставить 0 нетронутым.
SELECT s.Jan, s.Feb, s.Mar,
[Count] = SUM(CONVERT(int, CONVERT(bit, x.v + ABS(x.v))))
-------------------------------------------^^^^^^^^^^^
FROM dbo.YourTableName AS s
CROSS APPLY (VALUES(s.Jan),(s.Feb),(s.Mar)) AS x(v)
GROUP BY s.Jan, s.Feb, s.Mar;
Как сказал @JohnCappelletti в комментариях, вы можете использовать красивую функцию Sign, подобную этой (при условии, что значения >=0):
SELECT
, P.January as 'Jan'
, P.February as 'Feb'
, P.March as 'Mar'
, Sign(P.January) + Sign(P.February) + Sign(P.March) /*other months*/ as Count
FROM P
Вот гибкое решение, которое работает независимо от количества столбцов.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (Jan int, Feb int, Mar INT, Apr INT, May INT);
INSERT @tbl (Jan,Feb,Mar, Apr, May) VALUES
(13, 0, 25, 0, 2),
(11, 10, 4, 1, 18),
(0, 0, 7, 0, 7);
-- DDL and sample data population, end
SELECT t.*
, [Count] = x.value('count(/root/*[not(text() = "0")]/text())', 'INT')
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x);
Выход
Я думаю, что это хороший вариант использования IIF, см. документацию .
Запрос будет таким:
SELECT
Jan, Feb, Mar,
IIF(Jan > 0, 1, 0) + IIF(Feb > 0, 1, 0) + IIF(Mar > 0, 1, 0) AS [Count]
FROM p;
Используйте выражение case для каждого столбца, 1 или 0, и сложите их вместе.