Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Сравнения чисел, без участия нулей.  [new]
new_man_new
Member

Откуда:
Сообщений: 5
Здравствуйте.

Задача такая:
В таблице, нужно сравнить числа, но так, чтобы в сравнении не участвовали нули.
В результате сравнения нужно заполнить столбец 'compare' следующим образом:
если одни нули - no compare
если все цифры одинаковы, исключая нули - yes
если цифры разные, исключая нули - no

Должно получиться что то вроде этого:

a b c d e compare

1 0 1 0 0 yes
1 0 1 2 0 no
0 0 0 0 0 no compare
1 1 1 1 0 yes

Использовать циклы нельзя.
Дальше проверки первых двух символов, не могу ничего придумать:

case
when a!=0
then
case
when a=b
then 'yes'
else 'no'
end
else 'no compare'
1 мар 14, 17:39    [15652572]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
SERG1257
Member

Откуда:
Сообщений: 2751
я так понимаю что количестов элементов фиксированое то есть все можно засунуть в один большой case
я бы первым долгом проверил на "все нули", а затем сравнивал бы первый элемент со всеми - совпало идем дальше, нет значит no.
1 мар 14, 18:06    [15652737]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
new_man_new
Member

Откуда:
Сообщений: 5
Да, количество элементов фиксированное.
Интуитивно понятно, что все нужно запихнуть в один case.
Пока думаю как.
1 мар 14, 18:14    [15652792]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
new_man_new
В таблице, нужно сравнить числа, но так, чтобы в сравнении не участвовали нули.

select 
	case 
		when ( 5*i - isnull(a, i) - isnull(a, i) - isnull(a, i) - isnull(a, i) - isnull(a, i)) = 0 
		then 'compare' 
		else 'no compare' 
	end as compare
from (
select
	case 
		when a > 0 then a
		when b > 0 then b
		when c > 0 then c
		when d > 0 then d
		when e > 0 then e
		else null 
	end as i,
	nullif(a, 0) as a,
	nullif(b, 0) as b,
	nullif(c, 0) as c,
	nullif(d, 0) as d,
	nullif(e, 0) as e
) t
1 мар 14, 18:19    [15652826]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
new_man_new
В таблице, нужно сравнить числа, но так, чтобы в сравнении не участвовали нули.
Поправил первый CASE
select 
	case 
		when ( 5*i - isnull(a, i) - isnull(b, i) - isnull(c, i) - isnull(d, i) - isnull(e, i)) = 0 
		then 'compare' 
		else 'no compare' 
	end as compare
from (
select
	case 
		when a > 0 then a
		when b > 0 then b
		when c > 0 then c
		when d > 0 then d
		when e > 0 then e
		else null 
	end as i,
	nullif(a, 0) as a,
	nullif(b, 0) as b,
	nullif(c, 0) as c,
	nullif(d, 0) as d,
	nullif(e, 0) as e
) t
1 мар 14, 18:20    [15652837]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Если заменить 0 на null, то можно решить так:

case
when
      a=b and a=c and a=d and a=e
    and 
      b=c and b=d and b=e
    and
      c=d and c=e
    and
     d=e
    then 1
else 0
end


где каждое правое поле завернуть в isnull(правое, левое). Сравнение null = null , т.е. 0 0 0 0 0 как даст 0.
1 мар 14, 18:20    [15652838]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
select
 t.*, case isnull(y.max_v - y.min_v, -1) when -1 then 'no compare' when 0 then 'yes' else 'no' end
from
 Таблица t cross apply
 (select min(nullif(x.v, 0)), max(nullif(x.v, 0)) from (values (t.a), (t.b), (t.c), (t.d), (t.e)) x(v)) y(min_v, max_v);
1 мар 14, 18:27    [15652883]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
Владислав Колосов
где каждое правое поле завернуть в isnull(правое, левое)
Так в левом поле останется NULL, там где поле было 0, а так как сравнение с NULL всегда ложно, то получится ложь
1 мар 14, 18:32    [15652908]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
alexeyvg, действительно :(
case от invm можно, упростить:

select 0 a, 1 b, 2 c, 0 d, 1 e into таблица
insert into таблица values (0,1,1,1,0)

select
 t.*, 
 case isnull(y.max_v - y.min_v, -1) when -1 then 'no compare' when 0 then 'yes' else 'no' end cmpr,
 case when y.max_v = y.min_v then 'yes' else 'no' end cmpr_alter
from
 Таблица t cross apply
 (select min(nullif(x.v, 0)), max(nullif(x.v, 0)) from (values (t.a), (t.b), (t.c), (t.d), (t.e)) x(v)) y(min_v, max_v);
1 мар 14, 19:10    [15653180]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
О, опять невнимателен :( Прошу прощения.
1 мар 14, 19:12    [15653194]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
alexeyvg
Владислав Колосов
где каждое правое поле завернуть в isnull(правое, левое)
Так в левом поле останется NULL, там где поле было 0, а так как сравнение с NULL всегда ложно, то получится ложь
alexeyvg
сравнение с NULL всегда ложно
Cравнение с NULL не бывает ложным.
Оно всегда UNKNOWN
1 мар 14, 19:22    [15653262]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
new_man_new
Member

Откуда:
Сообщений: 5
Не совсем верно описал цель.
Допустим есть таблица T:

a b c d e
1 0 1 1 1
1 0 3 1 4
1 3 0 2 8

Нужно в результате вывести только те строки, где будут присутствовать одинаковые цифры, исключая нули (то есть в моем случаи 1-ая строка)

Был бы крайне признателен за готовое решение.
1 мар 14, 21:25    [15654082]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
create table #t
(
  a tinyint,
  b tinyint,
  c tinyint,
  d tinyint,
  e tinyint
)

insert into #t(a, b, c, d, e)
  select 1, 0, 1, 0, 0 union all
  select 1, 0, 1, 2, 0 union all
  select 0, 0, 0, 0, 0 union all
  select 1, 1, 1, 1, 0

select
  *
from #t
where a | b | c | d | e = case when a != 0 then a when b != 0 then b when c != 0 then c when d != 0 then d when e != 0 then e else -1 end

drop table #t
1 мар 14, 21:31    [15654113]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
А нет, мое решение не корректно.
1 мар 14, 21:34    [15654130]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
ROLpogo,

Не, так не получится:
create table #t
(
  a tinyint,
  b tinyint,
  c tinyint,
  d tinyint,
  e tinyint
)

insert into #t(a, b, c, d, e)
  select 1, 0, 1, 0, 0 union all
  select 1, 0, 1, 2, 0 union all
  select 0, 0, 0, 0, 0 union all
  select 3, 3, 1, 1, 0

select
  *
from #t
where a | b | c | d | e = case when a != 0 then a when b != 0 then b when c != 0 then c when d != 0 then d when e != 0 then e else -1 end

drop table #t
, хотя делать через битовые операции - хорошая идея
1 мар 14, 21:39    [15654158]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
new_man_new
Нужно в результате вывести только те строки, где будут присутствовать одинаковые цифры, исключая нули (то есть в моем случаи 1-ая строка)

Был бы крайне признателен за готовое решение.
Вроде уже написали несколько готовых решений.

Вы на примерах тех скриптов, которые вам уже сделали, покажите, чем не устраивает результат, и какой нужен.
1 мар 14, 21:41    [15654170]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
select
  *
from #t
where cast((IsNull(nullif(a, 0), 0) + IsNull(nullif(b, 0), 0) + IsNull(nullif(c, 0), 0) + IsNull(nullif(d, 0), 0) + IsNull(nullif(e, 0), 0)) as float) /
      IsNull(nullif(case when a != 0 then 1 else 0 end + case when b != 0 then 1 else 0 end + case when c != 0 then 1 else 0 end +
                    case when d != 0 then 1 else 0 end + case when e != 0 then 1 else 0 end, 0), 1) = 1
1 мар 14, 21:59    [15654292]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
new_man_new
Member

Откуда:
Сообщений: 5
ROLpogo,
при моих текущих данных:

a b c d e
1 0 1 0 0
1 0 1 2 0
1 1 1 1 0

возвращает пустую таблицу:

a b c d e
1 мар 14, 23:21    [15654840]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
new_man_new
при моих текущих данных:

a b c d e
1 0 1 0 0
1 0 1 2 0
1 1 1 1 0

возвращает пустую таблицу:

А у меня
a	b	c	d	e
1 0 1 0 0
ИМХО вы скопировали неправильно.
1 мар 14, 23:26    [15654890]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
alexeyvg
А у меня
a	b	c	d	e
1 0 1 0 0
ИМХО вы скопировали неправильно.

А у меня так вообще:
a	b	c	d	e
1 0 1 0 0
1 1 1 1 0
2 мар 14, 00:19    [15655345]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31439
ROLpogo
alexeyvg
А у меня
a	b	c	d	e
1 0 1 0 0
ИМХО вы скопировали неправильно.

А у меня так вообще:
a	b	c	d	e
1 0 1 0 0
1 1 1 1 0
Всё правильно.

Я просто последнюю строчку исходных данных менял для проверки.

Вот полный скрипт с данными, там ещё и мой запрос...
create table #t
(
  a tinyint,
  b tinyint,
  c tinyint,
  d tinyint,
  e tinyint
)

insert into #t(a, b, c, d, e)
  select 1, 0, 1, 0, 0 union all
  select 1, 0, 1, 2, 0 union all
  select 0, 0, 0, 0, 0 union all
  select 3, 3, 1, 1, 0

select 
	case 
		when ( 5*i - isnull(a, i) - isnull(b, i) - isnull(c, i) - isnull(d, i) - isnull(e, i)) = 0 
		then 'yes' 
		when i is null
		then 'no compare' 
		else 'no' 
	end as compare,
	*
from (
select
	case 
		when a > 0 then a
		when b > 0 then b
		when c > 0 then c
		when d > 0 then d
		when e > 0 then e
		else null 
	end as i,
	nullif(a, 0) as a,
	nullif(b, 0) as b,
	nullif(c, 0) as c,
	nullif(d, 0) as d,
	nullif(e, 0) as e
	from #t
) t


select
  *
from #t
where cast((IsNull(nullif(a, 0), 0) + IsNull(nullif(b, 0), 0) + IsNull(nullif(c, 0), 0) + IsNull(nullif(d, 0), 0) + IsNull(nullif(e, 0), 0)) as float) /
      IsNull(nullif(case when a != 0 then 1 else 0 end + case when b != 0 then 1 else 0 end + case when c != 0 then 1 else 0 end +
                    case when d != 0 then 1 else 0 end + case when e != 0 then 1 else 0 end, 0), 1) = 1

drop table #t
2 мар 14, 00:29    [15655430]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
Ну и до кучи вариант с битовыми операциями:

select
  *
from #t
where a | b | c | d | e = case when a != 0 and a <= IsNull(nullif(b, 0), a + 1) and a <= IsNull(nullif(c, 0), a + 1) and a <= IsNull(nullif(d, 0), a + 1) and a <= IsNull(nullif(e, 0), a + 1) then a
       when b != 0 and b <= IsNull(nullif(a, 0), b + 1) and b <= IsNull(nullif(c, 0), b + 1) and b <= IsNull(nullif(d, 0), b + 1) and b <= IsNull(nullif(e, 0), b + 1) then b
       when c != 0 and c <= IsNull(nullif(b, 0), c + 1) and c <= IsNull(nullif(a, 0), c + 1) and c <= IsNull(nullif(d, 0), c + 1) and c <= IsNull(nullif(e, 0), c + 1) then c
       when d != 0 and d <= IsNull(nullif(b, 0), d + 1) and d <= IsNull(nullif(c, 0), d + 1) and d <= IsNull(nullif(a, 0), d + 1) and d <= IsNull(nullif(e, 0), d + 1) then d
       when e != 0 then e end
2 мар 14, 00:35    [15655479]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
Убрал лишние "+1"
select
  *
from #t
where a | b | c | d | e = case when a != 0 and a <= IsNull(nullif(b, 0), a) and a <= IsNull(nullif(c, 0), a) and a <= IsNull(nullif(d, 0), a) and a <= IsNull(nullif(e, 0), a) then a
       when b != 0 and b <= IsNull(nullif(a, 0), b) and b <= IsNull(nullif(c, 0), b) and b <= IsNull(nullif(d, 0), b) and b <= IsNull(nullif(e, 0), b) then b
       when c != 0 and c <= IsNull(nullif(b, 0), c) and c <= IsNull(nullif(a, 0), c) and c <= IsNull(nullif(d, 0), c) and c <= IsNull(nullif(e, 0), c) then c
       when d != 0 and d <= IsNull(nullif(b, 0), d) and d <= IsNull(nullif(c, 0), d) and d <= IsNull(nullif(a, 0), d) and d <= IsNull(nullif(e, 0), d) then d
       when e != 0 then e end
2 мар 14, 00:46    [15655593]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
new_man_new
Member

Откуда:
Сообщений: 5
Спасибо всем. Все получилось!
2 мар 14, 01:15    [15655789]     Ответить | Цитировать Сообщить модератору
 Re: Сравнения чисел, без участия нулей.  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 216
ROLpogo
select
  *
from #t
where cast((IsNull(nullif(a, 0), 0) + IsNull(nullif(b, 0), 0) + IsNull(nullif(c, 0), 0) + IsNull(nullif(d, 0), 0) + IsNull(nullif(e, 0), 0)) as float) /
      IsNull(nullif(case when a != 0 then 1 else 0 end + case when b != 0 then 1 else 0 end + case when c != 0 then 1 else 0 end +
                    case when d != 0 then 1 else 0 end + case when e != 0 then 1 else 0 end, 0), 1) = 1

Кстати, этот запрос тоже некорректен, ибо работает только на единицах .
2 мар 14, 10:30    [15656591]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить