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

Откуда:
Сообщений: 142
Добрый день, прошу помощи в написании запроса. Суть такова - в таблице хранятся данные о подключении кабелей между шкафами. Каждая строка представляет подключение одного провода. Упрощенная структура таблицы: первый столбец - шкаф, второй - номер кабеля. Итак, если 3х жильный кабель N1 подключен правильно, то в таблице будет 6 строк - три строки из шкафа 1 и три строки из шкафа 2, все с тем же кабелем N1. Иногда при проектировании возникают ошибки и между шкафами подключается разное количество жил. Вот такие случаи мне и надо отфильтровать. Я создал два запроса, которые потом между собой сравниваю в третьем на предмет различного количества жил.

SELECT     TOP (100) PERCENT SHKAF, KOD_KABELU_MOD, COUNT(SHKAF) AS CountOfSHKAF
FROM         dbo.DBQTA_B1_1_2
GROUP BY SHKAF, KOD_KABELU_MOD
ORDER BY KOD_KABELU_MOD


Но мне это кажется очень неуклюжим, а вот до более компактного решения я пока не допетрил! Подскажите, пожалуйста.
23 дек 11, 18:46    [11817588]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
libru
Member

Откуда:
Сообщений: 877
прошу аффтара внести в студию скрипты создания тестовых таблиц и наполнения их тестовыми данными а также показаь желаемый результат
23 дек 11, 18:55    [11817613]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
andrej2005
Member

Откуда:
Сообщений: 142
libru
прошу аффтара внести в студию скрипты создания тестовых таблиц и наполнения их тестовыми данными а также показаь желаемый результат...


USE Projekce
GO

IF OBJECT_ID ('dbo.Table_1', 'U') IS NOT NULL
    DROP TABLE [dbo].[Table_1];
GO
IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
    DROP VIEW dbo.V1;
GO
IF OBJECT_ID ('dbo.V2', 'V') IS NOT NULL
    DROP VIEW dbo.V2;
GO
IF OBJECT_ID ('dbo.V3', 'V') IS NOT NULL
    DROP VIEW dbo.V3;
GO

CREATE TABLE [dbo].[Table_1](
	[MyShkaf] [varchar](20) NULL,
	[MyKabel] [varchar](20) NULL
);
GO

INSERT INTO [dbo].[Table_1] 
    VALUES	('shkaf1', 'kabel1'),
			('shkaf1', 'kabel1'),
			('shkaf1', 'kabel1'),
			('shkaf2', 'kabel1'),
			('shkaf2', 'kabel1'),
			('shkaf2', 'kabel1'),
			('shkaf2', 'kabel2'),
			('shkaf2', 'kabel2'),
			('shkaf2', 'kabel2'),
			('shkaf2', 'kabel2'),
			('shkaf3', 'kabel2'),
			('shkaf3', 'kabel2'),
			('shkaf3', 'kabel2'),
			('shkaf4', 'kabel3'),
			('shkaf5', 'kabel3'),
			('shkaf6', 'kabel4'),
			('shkaf6', 'kabel4'),
			('shkaf7', 'kabel4'),
			('shkaf7', 'kabel4'),
			('shkaf8', 'kabel5'),
			('shkaf8', 'kabel5'),
			('shkaf9', 'kabel5'),
			('shkaf9', 'kabel5'),
			('shkaf9', 'kabel5'),
			('shkaf9', 'kabel5'),
			('shkaf10', 'kabel5');

GO

/*moe reshenie*/
CREATE VIEW V1 AS
	SELECT [Table_1].[MyKabel], [Table_1].[MyShkaf], COUNT([MyShkaf]) AS CountOfShkaf1
	FROM [Table_1]
	GROUP BY [Table_1].[MyKabel], [Table_1].[MyShkaf]
GO

CREATE VIEW V2 AS
	SELECT [Table_1].[MyKabel], [Table_1].[MyShkaf], COUNT([MyShkaf]) AS CountOfShkaf2
	FROM [Table_1]
	GROUP BY [Table_1].[MyKabel], [Table_1].[MyShkaf]
GO

CREATE VIEW V3 AS
	SELECT [V1].[MyKabel], [V1].[MyShkaf], [V1].[CountOfShkaf1]
	FROM V1 INNER JOIN V2
		ON [V1].[MyKabel]=[V2].[MyKabel]
		AND [V1].[CountOfShkaf1]<>[V2].[CountOfShkaf2]
	GROUP BY [V1].[MyKabel], [V1].[MyShkaf], [V1].[CountOfShkaf1]
GO

SELECT *
FROM V3
ORDER BY [MyKabel]
24 дек 11, 10:14    [11818996]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
with x as
(
 select
  MyShkaf,
  MyKabel,
  case when dense_rank() over (partition by MyKabel order by MyShkaf) = 1 then 1 else -1 end as r
 from
  dbo.Table_1
)
select
 MyKabel
from
 x
group by
 MyKabel
having
 sum(r) <> 0;
24 дек 11, 11:02    [11819033]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Наврал. Вот исправленный вариант:
with x as
(
 select
  MyShkaf,
  MyKabel,
  case when dense_rank() over (partition by MyKabel order by MyShkaf) = 1 then 1 else -1 end as r
 from
  dbo.Table_1
)
select
 MyKabel
from
 x
group by
 MyKabel
having
 sum(r) <> 0 or count(distinct MyShkaf) <> 2;
24 дек 11, 11:38    [11819084]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
aleks2
Guest
Зачем такие сложности? Чем искать подключенные кабели, не проще ли искать НЕ подключенные?

ЗЫ. GROUP BY - абсолютное зло.
24 дек 11, 13:49    [11819333]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
andrej2005
Member

Откуда:
Сообщений: 142
разбираюсь...
24 дек 11, 13:55    [11819351]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
aleks2
Guest
with x as
(
 select   MyShkaf,  MyKabel,  row_number() over (partition by MyKabel, MyShkaf order by MyShkaf) N  from  dbo.Table_1
)
select *
FROM x x1 full outer join x x2 on x1.MyKabel=x2.MyKabel AND x1.MyShkaf<x2.MyShkaf AND x1.N=x2.N
WHERE x1.MyKabel is null or x2.MyKabel is null
24 дек 11, 20:38    [11820627]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение в запросе  [new]
andrej2005
Member

Откуда:
Сообщений: 142
Привет всем и спасибо за идеи! Остановился на этом варианте:
with x as
(
	SELECT DISTINCT [MyKabel], [MyShkaf], COUNT([MyShkaf]) OVER(PARTITION BY [MyKabel], [MyShkaf]) AS 'CountOf'
	FROM [Table_1]
)
SELECT x1.MyKabel, x1.MyShkaf, x1.CountOf
FROM x x1 FULL OUTER JOIN x x2 ON x1.MyKabel=x2.MyKabel AND x1.CountOf<>x2.CountOf
WHERE x1.MyKabel IS NOT NULL AND x2.MyKabel IS NOT NULL


Вариант от invm не получается. В проекте фигурируют кабели, которые подключены только на одной стороне, а другая сторона в проекте не модернизируется (это моя ошибка, я должен был это учесть в тестовой таблице), поэтому в таких случаях не возникает перекрестное соединение жил шкаф1<-->шкаф2 и это условие соблюдается sum(r) <> 0. В результате вылазит куча кабелей, которые подключены правильно, но подключены только на одной стороне.
26 дек 11, 13:12    [11826047]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить