Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите сформулировать запрос!  [new]
uaggster
Member

Откуда:
Сообщений: 946
Имеется некая таблица create table t (a int identity(1,1) primary key, b int, c int check (c between 0 and N)), где N - некое произвольное (не большое) число (ну, 5, например).
Столбец b заполняется произвольными повторяющимися числами (на самом деле, это значения некоего внешнего ключа, но это - не важно).

"Ключом N-ки чисел" будем называть такое число из столбца b, для которого в таблице t имеется в точности одна запись со значением в столбце с = 0, + в точности одна запись со значением = 1 + ... + в точности одна запись со значением = N, т.е. если для числа из столбца b представлен весь набор значений из столбца с, без изъянов и дублей.

Вывести все записи из таблицы t, если b НЕ является "ключом N-ки".

... В принципе, если не удается решить всё это одним запросом, то согласен на конструкцию с временными таблицами.
+ хотелось бы иметь некое общее решение, для произвольного N, а не наворачивать N подзапросов с exists.
7 фев 13, 20:06    [13893736]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
qwerty112
Guest
uaggster
Вывести все записи из таблицы t, если b НЕ является "ключом N-ки".

имхо, обратная этой задаче - это то, что называется реляц.делением,
поищите по форуму - бывало ...

select b
from t
group by b
having count (*) = count(distinct a)
   and count (*) = N+1


и "прямая" задача, соотв. :
select * from t
where b not in 
(select b
from t
group by b
having count (*) = count(distinct a)
   and count (*) = N+1)
7 фев 13, 21:15    [13893899]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
declare @n int = 4;

declare @t table (a int identity(1,1) primary key, b int, c int check (c between 0 and 4));

insert into @t
 (b, c)
values
 (4, 0), (4, 1), (4, 2), (4, 3), (4, 4),
 (1, 0), (1, 1), (1, 2), (1, 3),
 (2, 0), (2, 1), (2, 2),
 (3, 0), (3, 1), (3, 1), (3, 3), (3, 3);

with x as
( 
 select
  *,
  @n - (dense_rank() over (partition by b order by c) - 1) as r1,
  dense_rank() over (partition by b order by c desc) - 1 as r2
 from
  @t
)
select
 a, b, c
from
 x
where
 r1 <> r2;
7 фев 13, 23:38    [13894315]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
qwerty112
Guest
fix
qwerty112
и "прямая" задача, соотв. :
select * from t
where b not in 
(select b
from t
group by b
having count (*) = count(distinct c)
   and count (*) = N+1)
8 фев 13, 01:26    [13894606]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
invm, при всем уважении, но RANK для повторов одинаков - после добавления, например, (4,1), по-прежнему 4 будет считаться ключем.
8 фев 13, 01:40    [13894630]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
aleks2
Guest
declare @n int = 4;

declare @t table (a int identity(1,1) primary key, b int, c int check (c between 0 and 4));

insert into @t
 (b, c)
values
 (4, 0), (4, 1), (4, 2), (4, 3), (4, 4),
 (1, 0), (1, 1), (1, 2), (1, 3),
 (2, 0), (2, 1), (2, 2),
 (3, 0), (3, 1), (3, 1), (3, 3), (3, 3);

-- ну эта просто стационарной таблицей можно сделать
declare @c table(c int primary key clustered);
while @n>=0 begin
  insert @c values(@n);
  set @n = @n-1;
end;

-- и фсего то два exists
select * from @t t
where exists( select * from @t tt where tt.b=t.b and tt.c=t.c and tt.a<>t.a )
      or
      exists( select * 
                from @c c 
                     left outer join 
                     (select * from @t tt where tt.b=t.b) ttt 
                     on ttt.c=c.c 
                where ttt.c is null
            )
            
select * from @t t
where exists( select * from @t tt where tt.b=t.b and tt.c=t.c and tt.a<>t.a )
      or
      exists( select c from @c
              except
              (select c from @t tt where tt.b=t.b)
             )            
8 фев 13, 09:57    [13895306]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
1) то же возражение
(4, 0), (4, 1), (4, 2), (4, 3), (4, 4), (4, 4),

2) самый первый вариант куда как лаконичнее
8 фев 13, 10:11    [13895407]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Поправки, с учетом замечания Cygapb-007:
declare @n int = 4;

declare @t table (a int identity(1,1) primary key, b int, c int check (c between 0 and 4));

insert into @t
 (b, c)
values
 (4, 0), (4, 1), (4, 2), (4, 3), (4, 4),
 (1, 0), (1, 1), (1, 2), (1, 3),
 (2, 0), (2, 1), (2, 2),
 (3, 0), (3, 1), (3, 1), (3, 3), (3, 3);

with x as
( 
 select
  *,
  @n - (dense_rank() over (partition by b order by c) - 1) as r1,
  dense_rank() over (partition by b order by c desc) - 1 as r2,
  row_number() over (partition by b order by a, c) +
  row_number() over (partition by b order by a desc, c desc) as n
 from
  @t
)
select
 a, b, c
from
 x
where
 r1 <> r2 or n > @n + 2;
8 фев 13, 10:18    [13895468]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Добрый Э - Эх
Guest
uaggster,

если правильно понял задачу, то примерно что-то такое должно получиться:

select top 1 with ties *
  from t
 order by case 
            when count(1) over(partition by b) != N + 1
              or sum(c) over(partition by b) != ((N + 1)*(N))/2 
            then 1 
          end desc

Нигде не проверял, за работоспособность не ручаюсь :)
8 фев 13, 10:53    [13895743]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

Хотя, к чему эти отрицания и обратная сортировка?

Можно и так:
select top 1 with ties *
  from t
 order by case 
            when count(1) over(partition by b) = 4 + 1
              and sum(c) over(partition by b) != ((4 + 1)*(4))/2 
            then 1 
          end
8 фев 13, 10:54    [13895762]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
Добрый Э - Эх,

Хотя, к чему эти отрицания и обратная сортировка?

Можно и так:
select top 1 with ties *
  from t
 order by case 
            when count(1) over(partition by b) = N + 1
              and sum(c) over(partition by b) = ((N + 1)*(N))/2 
            then 1 
          end

Таки надо потестировать, а то в пятницу как-то плохо соображается
8 фев 13, 10:56    [13895780]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Добрый Э - Эх,

Суммировать нельзя.
8 фев 13, 11:10    [13895883]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Добрый Э - Эх
Guest
invm,

кто сказал?
8 фев 13, 11:12    [13895894]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Добрый Э - Эх,

declare @n int = 4;

declare @t table (a int identity(1,1) primary key, b int, c int check (c between 0 and 4));

insert into @t
 (b, c)
values
 (1, 2), (1, 2), (1, 2), (1, 2), (1, 2),
 (2, 0), (2, 1), (2, 2), (2, 3), (2, 4),
 (3, 0), (3, 1), (3, 2), (3, 4);

-- решение от qwerty112
select * from @t
where b not in 
(select b
from @t
group by b
having count (*) = count(distinct c)
   and count (*) = @n+1)

-- решение от Добрый Э - Эх
select top 1 with ties *
  from @t
 order by case 
            when count(1) over(partition by b) = @n + 1
              and sum(c) over(partition by b) = ((@n + 1)*(@n))/2 
            then 1 
          end;


Кроме того, если в таблице есть только "ключи N-ки чисел", ваш запрос выведет все записи таблицы. Хотя по условию не должен вывести ни одной.
8 фев 13, 11:22    [13895967]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Добрый Э - Эх,

Данные для проверки: (4, 0), (4, 0), (4, 3), (4, 3), (4, 4) и (4, 0), (4, 1), (4, 2), (4, 3), (4, 4)
8 фев 13, 11:25    [13895980]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Добрый Э - Эх
Guest
Гость333,

тогда избавляемся от красоты одного уровня, условие из order by переносим в селект-лист и на следующем уровне фильтруем по этому полю... :)
8 фев 13, 11:27    [13895992]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

да, без проверки запросы писать, конечно, неблагодарное дело...
всех ситуаций в голове не обсчитаешь...
8 фев 13, 11:34    [13896034]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
Добрый Э - Эх
Guest
может так попробовать?
select *
  from (
         select *
              , c - row_number()over(partition by b order by c) as x_rn1
              , c + row_number()over(partition by b order by c desc) as x_rn2
           from t
       ) v
 where x_rn1 != -1
    or x_rn2 != @N + 1
 order by b,c
8 фев 13, 11:44    [13896098]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
aleks2
Guest
Cygapb-007
1) то же возражение
(4, 0), (4, 1), (4, 2), (4, 3), (4, 4), (4, 4),

2) самый первый вариант куда как лаконичнее


Чо, рыба не чищеная? Хе-хе.
Дык надо проявить сообразительность.
8 фев 13, 12:04    [13896249]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
aleks2
Guest
select * from @t t
where exists( select * from @t tt inner join @t ttt on ttt.b=tt.b and ttt.c=tt.c where tt.b=t.b and tt.a<>t.a )
      or
      exists( select * 
                from @c c 
                     left outer join 
                     (select * from @t tt where tt.b=t.b) ttt 
                     on ttt.c=c.c 
                where ttt.c is null
            )
8 фев 13, 12:09    [13896284]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
aleks2
Guest
Опять ведь скажут: "чешуя налипла"

select * from @t t
where exists( select * from @t tt inner join @t ttt on ttt.b=tt.b and ttt.c=tt.c  and ttt.a<>tt.a where tt.b=t.b )
      or
      exists( select * 
                from @c c 
                     left outer join 
                     (select * from @t tt where tt.b=t.b) ttt 
                     on ttt.c=c.c 
                where ttt.c is null
            )
8 фев 13, 12:12    [13896301]     Ответить | Цитировать Сообщить модератору
 Re: Помогите сформулировать запрос!  [new]
sti
Member

Откуда:
Сообщений: 769
declare @n int = 4;

declare @t table (a int identity(1,1) primary key, b int, c int check (c between 0 and 4));

insert into @t
 (b, c)
values
 (4, 0), (4, 1), (4, 2), (4, 3), (4, 4),
 (1, 0), (1, 1), (1, 2), (1, 3),
 (2, 0), (2, 1), (2, 2),
 (3, 0), (3, 1), (3, 1), (3, 3), (3, 3),
 (5, 0), (5, 1), (5, 2), (5, 3), (5, 4), (5, 4);
 
select * from @t where b in
(
select b from @t            
group by b
having COUNT(*)!=@n+1

UNION

select b from @t
group by b,c
having COUNT(*)>1
)
8 фев 13, 12:22    [13896394]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить