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

Откуда: Украина, Киев-Одесса
Сообщений: 182
Задача в следующем:
в двух таблицах 76-2012 и 76-2013 нужно определить банки Рогабанк и Копытабанк (на самом деле список таких банков состоит из > 40, и постоянно обновляется) по полям externalchannel или Beneficiary, подбить сумму поля [76] по договорам, у которых не изменился RegistrationNumber, но изменился ContractNumber

По большому счету, попытался совместить два различных запроса (на отпределения банка, и подсчет суммы [76] при заданных условиях). но где-то напортачил
При выполнении запроса возникает ошибка
"Сообщение 8156, уровень 16, состояние 1, строка 23
Столбец "ContractNumber" для "t3" указан более одного раза."
Может, кто посоветует, в чем проблема?..

DECLARE @as TABLE (mask NVARCHAR(100), name NVARCHAR(100));
INSERT @as (mask, name) VALUES 


  (N'%рога%',  N'Рогабанк'),
  (N'%копыт%',  N'Копытабанк');

 
    SELECT t3.lb3, t3.vid, b.name, sum (t3.[76]) as 'Платежи'
      FROM
         (SELECT *
             FROM [REPORT].[dbo].[76-2012] as t1
                     join [REPORT].[dbo].[76-2013] as t2
                     on  t1. [INSURER] = t2. [INSURER]
	          where (t2.[ContractNumber] != t1.[ContractNumber])  and (t1.RegistrationNumber =t2.RegistrationNumber) 
	  
         ) as t3
 
             right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask 
   
    GROUP BY   t3.lb3, t3.vid, b.name
12 фев 14, 16:31    [15558138]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
         (SELECT *
             FROM [REPORT].[dbo].[76-2012] as t1
                     join [REPORT].[dbo].[76-2013] as t2
                     on  t1. [INSURER] = t2. [INSURER]
	          where (t2.[ContractNumber] != t1.[ContractNumber])  and (t1.RegistrationNumber =t2.RegistrationNumber) 
	  
         ) as t3

вместо select * -> select FIELD1, FIELD2...... FIELD N
12 фев 14, 16:34    [15558159]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Maxx
         (SELECT *
             FROM [REPORT].[dbo].[76-2012] as t1
                     join [REPORT].[dbo].[76-2013] as t2
                     on  t1. [INSURER] = t2. [INSURER]
	          where (t2.[ContractNumber] != t1.[ContractNumber])  and (t1.RegistrationNumber =t2.RegistrationNumber) 
	  
         ) as t3

вместо select * -> select FIELD1, FIELD2...... FIELD N


Таки да! :) Спасибо Maxx
12 фев 14, 16:58    [15558320]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Возникла необходимость совместить этот запрос

DECLARE @as TABLE (mask NVARCHAR(100), name NVARCHAR(100));
INSERT @as (mask, name) VALUES 


  (N'%рога%',  N'Рогабанк'),
  (N'%копыт%',  N'Копытабанк');

 
    SELECT t3.lb3, t3.vid, b.name, count (distinct ContractNumber) as 'Измененные 2012'
      FROM
         (SELECT *
             FROM [REPORT].[dbo].[76-2012] as t1
                     join [REPORT].[dbo].[76-2013] as t2
                     on  t1. [INSURER] = t2. [INSURER]
	          where (t2.[ContractNumber] != t1.[ContractNumber])  and (t1.RegistrationNumber =t2.RegistrationNumber) 
	  
         ) as t3
 
             right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask 
   
    GROUP BY   t3.lb3, t3.vid, b.name


с запросом

SELECT count (distinct ContractNumber) as 'Все 2012'
             FROM [REPORT].[dbo].[76-2012]


То есть, первый запрос выдасть ограниченное количество значений 2012 года после применения правил, а второй - общее.
Удобно было бы добавить результат второго запроса в виде отдельной колонки в первом.
Попытался вложить селектом, естественно ругается. может есть какие-то варианты решения, union или join?...
12 фев 14, 20:48    [15559256]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Glory
Member

Откуда:
Сообщений: 104760
googlogmob
Возникла необходимость совместить этот запрос

Мождет вы про задачу расскажите, а не про ваш способ решения ?
13 фев 14, 10:37    [15561040]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
googlogmob
первый запрос выдасть ограниченное количество значений 2012 года после применения правил, а второй - общее.
Удобно было бы добавить результат второго запроса в виде отдельной колонки в первом.
Даже интересно, как может выглядеть такой совмещённый результат???
13 фев 14, 10:43    [15561078]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Glory
googlogmob
Возникла необходимость совместить этот запрос

Мождет вы про задачу расскажите, а не про ваш способ решения ?


нужно в одной таблице вывести общее количество договоров, и количество договоров с применением правил отбора, с разбивкой по банкам (которых много)
Как-то так
Банк Колтво с фильтром Всего
Рога 5 10
Копыта 8 22
13 фев 14, 10:44    [15561085]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Glory
Member

Откуда:
Сообщений: 104760
googlogmob
нужно в одной таблице вывести общее количество договоров, и количество договоров с применением правил отбора, с разбивкой по банкам (которых много)


select count(*), sum(case when "применение праил отбора" then 1 else 0 end)
...
13 фев 14, 10:47    [15561114]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Glory
googlogmob
нужно в одной таблице вывести общее количество договоров, и количество договоров с применением правил отбора, с разбивкой по банкам (которых много)


select count(*), sum(case when "применение праил отбора" then 1 else 0 end)
...

применил
в итоге получился след.код:
DECLARE @as TABLE (mask NVARCHAR(100), name NVARCHAR(100));
INSERT @as (mask, name) VALUES 


  (N'%рога%',  N'Рогабанк'),
  (N'%копыта%',  N'Копытабанк'),


  SELECT t1.lb1, b.name,  count( distinct t1.contractnumber) as 'Количество 2012',( sum(case  when 
            (t1.RegistrationNumber =t2.RegistrationNumber and
	            (t2.[ContractNumber] != t1.[ContractNumber]) and  (t1. [Vid] = t2. [Vid] )  and (t1.Object = t2.Object)
	             and (t1. [INSURER] = t2. [INSURER])  )
		       
		    then 1 else 0 end ))
       FROM [REPORT].[dbo].[76-2013] as t2,
	   [REPORT].[dbo].[76-2012] as t1 
	    right JOIN @as b ON  t1.externalchannel LIKE b.mask  OR t1.Beneficiary LIKE b.mask
		 where (t1. [Vid] like N'%транспорту, кр%') 
    GROUP BY  t1.lb1, b.name


Время выполнения перевалило за 18 минут. не ждал окончания сверить результат
Запросы, из которых он состоит, выполняются, первый - 1,45 мин (поиск общего количества):

 SELECT t3.lb1, b.name, count(distinct t3.contractnumber) as 'Количество 2012'
       FROM [REPORT].[dbo].[76-2012] as t3
         right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask
		 where (t3. [Vid] like N'%транспорту, кр%') 
    GROUP BY  t3.lb1, b.name
,
Второй запрос выполняется 5,58 мин (поиск количества после применения правил)

 SELECT t3.lb1,b.name, count(distinct t3.Дог2012) as 'Количество лонг с 2012' 
	     
       FROM 
	      (SELECT t1.lb1,  t1.ContractNumber as 'Дог2012' ,   t1.externalchannel, t1.Beneficiary 
             FROM [REPORT].[dbo].[76-2012] as t1
             join [REPORT].[dbo].[76-2013] as t2
              on t1.RegistrationNumber =t2.RegistrationNumber 
	             where (t2.[ContractNumber] != t1.[ContractNumber]) and  (t1. [Vid] = t2. [Vid] ) and  (t1. [Vid] like N'%транспорту, кр%' ) and (t1.Object = t2.Object)
	             and (t1. [INSURER] = t2. [INSURER]) 
		        group by  t1.lb1, t1.ContractNumber, t1.externalchannel, t1.Beneficiary 
		  ) as t3
      right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask 
	    where t3.lb1 is not null
  GROUP BY   t3.lb1, b.name


И как быть с условием distinct?.. в сведенном запросе с применением case использовать distinct не получается..

Может не правильно составил сведенный запрос с применением case? Или если все верно, тогда как можно сократить время выполнения запроса, чтобы в обоих его частях выполнялось условие distinct..

Буду очень признателен за подсказку
13 фев 14, 13:39    [15562598]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Glory
Member

Откуда:
Сообщений: 104760
googlogmob
Может не правильно составил сведенный запрос с применением case? Или если все верно, тогда как можно сократить время выполнения запроса, чтобы в обоих его частях выполнялось условие distinct..

Буду очень признателен за подсказку

Начните с подробного оформления задачи.
Гораздо более подробного, чем в 15561085
13 фев 14, 13:41    [15562626]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Glory
googlogmob
Может не правильно составил сведенный запрос с применением case? Или если все верно, тогда как можно сократить время выполнения запроса, чтобы в обоих его частях выполнялось условие distinct..

Буду очень признателен за подсказку

Начните с подробного оформления задачи.
Гораздо более подробного, чем в 15561085


да, верно. Имеются массивы 2012 и 2013 со списком договоров (которые могут дублироваться в связи с возможность нескольких платежей - важно применять distinct)
Нужно сверить количество лонгированных с 2012 года в 2013. Это выполняется запросом

SELECT t3.lb1,b.name, count(distinct t3.Дог2012) as 'Количество лонг с 2012' 
	     
       FROM 
	      (SELECT t1.lb1,  t1.ContractNumber as 'Дог2012' ,   t1.externalchannel, t1.Beneficiary 
             FROM [REPORT].[dbo].[76-2012] as t1
             join [REPORT].[dbo].[76-2013] as t2
              on t1.RegistrationNumber =t2.RegistrationNumber 
	             where (t2.[ContractNumber] != t1.[ContractNumber]) and  (t1. [Vid] = t2. [Vid] ) and  (t1. [Vid] like N'%транспорту, кр%' ) and (t1.Object = t2.Object)
	             and (t1. [INSURER] = t2. [INSURER]) 
		        group by  t1.lb1, t1.ContractNumber, t1.externalchannel, t1.Beneficiary 
		  ) as t3
      right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask 
	    where t3.lb1 is not null
  GROUP BY   t3.lb1, b.name


Также, для расчета удельного веса лонгированных договоров нужно вытяную общее количество договоров
Запрос:

 SELECT t3.lb1, b.name, count(distinct t3.contractnumber) as 'Количество 2012'
       FROM [REPORT].[dbo].[76-2012] as t3
         right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask
		 where (t3. [Vid] like N'%транспорту, кр%') 
    GROUP BY  t3.lb1, b.name


И все это - в разрезе банков (список не фиксированных, постоянно обновляется)


Отдельно выполняется, но потом приходиться вручную сводить в таблицу по типу
Банк Колтво с фильтром Всего
Рога 5 10
Копыта 8 22


Было бы отлично формировать запросом такую таблицу сразу, с допустимым временем выполнения
13 фев 14, 13:53    [15562710]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Список актуальных банков распределяется через "шапку"

DECLARE @as TABLE (mask NVARCHAR(100), name NVARCHAR(100));
    INSERT @as (mask, name) VALUES 


  (N'%рога%',  N'Рогабанк'),
  (N'%копыта%',  N'Копытабанк'),
13 фев 14, 13:54    [15562724]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Glory
Member

Откуда:
Сообщений: 104760
googlogmob
да, верно. Имеются массивы 2012 и 2013 со списком договоров

Вот здесь должне быть скрипт создания и заполнения этих "массивов"

googlogmob
Нужно сверить количество лонгированных с 2012 года в 2013.

Вот здесь должен быть приведен желаемый конечный результат для данных из предыдущего скрипта
Плюс какие то пояснения.
13 фев 14, 13:56    [15562733]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Glory
googlogmob
да, верно. Имеются массивы 2012 и 2013 со списком договоров

Вот здесь должне быть скрипт создания и заполнения этих "массивов"

я не верно выразился. сорри. имеются таблицы 2012 и 2013. Создаются отдельным скриптом

googlogmob
Нужно сверить количество лонгированных с 2012 года в 2013.

Вот здесь должен быть приведен желаемый конечный результат для данных из предыдущего скрипта
Плюс какие то пояснения.


желаемый конечный результат - количество уникальных договоров, для которых выполняются условия при сверке значений полей двух таблиц: 2012 и 2013 :
-RegistrationNumber =RegistrationNumber,
-ContractNumber] != t1.[ContractNumber],
-[Vid] = [Vid] )
-[Vid] like N'%транспорту, кр%' ,
-Object = t2.Object,
-[INSURER]= [INSURER])
13 фев 14, 14:10    [15562832]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
googlogmob,

может вы таки задачу целиком обрисутете,а то ето счастье совместить одно с другим уже 3ю неделю на форуме....
13 фев 14, 14:13    [15562856]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Glory
Member

Откуда:
Сообщений: 104760
googlogmob
желаемый конечный результат - количество уникальных договоров, для которых выполняются условия при сверке значений полей двух таблиц: 2012 и 2013 :
-RegistrationNumber =RegistrationNumber,
-ContractNumber] != t1.[ContractNumber],
-[Vid] = [Vid] )
-[Vid] like N'%транспорту, кр%' ,
-Object = t2.Object,
-[INSURER]= [INSURER])

Мда.
Вот скажите, у кого есть время
- придумать и создать таблицы(такие, как у вас)
- заполнить их данными(такими как у вас )
- придумать к этим данным конечный результат (такой как у вас)

и только после всего этого начать писать запрос ???
13 фев 14, 14:14    [15562866]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Maxx
googlogmob,

может вы таки задачу целиком обрисутете,а то ето счастье совместить одно с другим уже 3ю неделю на форуме....


идея совместить вчера возникла.
Оба запроса и делают то что нужно, но поскольку запросов два, то и результат получается не в одной таблице.





Этот запроос:
DECLARE @as TABLE (mask NVARCHAR(100), name NVARCHAR(100));
INSERT @as (mask, name) VALUES 


  (N'%рога%',  N'Рогабанк'),
  (N'%копыта%',  N'Копытабанк'),


  SELECT t1.lb1, b.name,  count( distinct t1.contractnumber) as 'Количество 2012',( sum(case  when 
            (t1.RegistrationNumber =t2.RegistrationNumber and
	            (t2.[ContractNumber] != t1.[ContractNumber]) and  (t1. [Vid] = t2. [Vid] )  and (t1.Object = t2.Object)
	             and (t1. [INSURER] = t2. [INSURER])  )
		       
		    then 1 else 0 end ))
       FROM [REPORT].[dbo].[76-2013] as t2,
	   [REPORT].[dbo].[76-2012] as t1 
	    right JOIN @as b ON  t1.externalchannel LIKE b.mask  OR t1.Beneficiary LIKE b.mask
		 where (t1. [Vid] like N'%транспорту, кр%') 
    GROUP BY  t1.lb1, b.name


Выводит результат:
Банк Колтво с фильтром
Рога 5
Копыта 8


А запрос:
 SELECT t3.lb1, b.name, count(distinct t3.contractnumber) as 'Количество 2012'
       FROM [REPORT].[dbo].[76-2012] as t3
         right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask
		 where (t3. [Vid] like N'%транспорту, кр%') 
    GROUP BY  t3.lb1, b.name


Выводит результат:

Банк Общее количество
Рога 8
Копыта 22


Правильно, но нужно совместить оба запроса чтобы в результате получить таблицу:

Банк Колтво с фильтром Всего
Рога 5 10
Копыта 8 22
13 фев 14, 15:01    [15563169]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
googlogmob
Maxx
googlogmob,

может вы таки задачу целиком обрисутете,а то ето счастье совместить одно с другим уже 3ю неделю на форуме....


идея совместить вчера возникла.
Оба запроса и делают то что нужно, но поскольку запросов два, то и результат получается не в одной таблице.





Этот запроос:
SELECT t3.lb1,b.name, count(distinct t3.Дог2012) as 'Количество лонг с 2012' 
	     
       FROM 
	      (SELECT t1.lb1,  t1.ContractNumber as 'Дог2012' ,   t1.externalchannel, t1.Beneficiary 
             FROM [REPORT].[dbo].[76-2012] as t1
             join [REPORT].[dbo].[76-2013] as t2
              on t1.RegistrationNumber =t2.RegistrationNumber 
	             where (t2.[ContractNumber] != t1.[ContractNumber]) and  (t1. [Vid] = t2. [Vid] ) and  (t1. [Vid] like N'%транспорту, кр%' ) and (t1.Object = t2.Object)
	             and (t1. [INSURER] = t2. [INSURER]) 
		        group by  t1.lb1, t1.ContractNumber, t1.externalchannel, t1.Beneficiary 
		  ) as t3
      right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask 
	    where t3.lb1 is not null
  GROUP BY   t3.lb1, b.name


Выводит результат:
Банк Колтво с фильтром
Рога 5
Копыта 8


А запрос:
 SELECT t3.lb1, b.name, count(distinct t3.contractnumber) as 'Количество 2012'
       FROM [REPORT].[dbo].[76-2012] as t3
         right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask
		 where (t3. [Vid] like N'%транспорту, кр%') 
    GROUP BY  t3.lb1, b.name


Выводит результат:

Банк Общее количество
Рога 8
Копыта 22


Правильно, но нужно совместить оба запроса чтобы в результате получить таблицу:

Банк Колтво с фильтром Всего
Рога 5 10
Копыта 8 22



Подправил первый запрос
Буду очень благодарен, если удастся помочь. Это сэкономило бы море времени многим людям..
13 фев 14, 16:13    [15563693]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
а вы уверены ,что количество строк в обоих запросах совпадут то ?
Что делать с теми кто будет во втором и не будет в первом ?
13 фев 14, 17:11    [15563986]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Да и как интерпритировать ваши таблички результатов то ?

В певом варинате у Рогов Общее количество =8
Во втором Количество с фильтром 5 и общее 10.....

Они ваще как -то кореллируються между собой ?
13 фев 14, 17:13    [15563995]     Ответить | Цитировать Сообщить модератору
 Re: Совмещение двух запросов  [new]
googlogmob
Member

Откуда: Украина, Киев-Одесса
Сообщений: 182
Maxx
а вы уверены ,что количество строк в обоих запросах совпадут то ?
Что делать с теми кто будет во втором и не будет в первом ?


резонное замечание Maxx. применил left join. В первор выбираются все значения, поэтому во втором точно будет меньше значений

решил таким способом

DECLARE @as TABLE (mask NVARCHAR(100), name NVARCHAR(100));
INSERT @as (mask, name) VALUES 


  (N'%рога%',  N'Рогабанк'),
  (N'%копыта%',  N'Копытабанк');

 SELECT t20.lb1, t20.name, t20.s2012, t10.s2013 FROM
    (SELECT t3.lb1 as lb1, b.name as name, count(distinct t3.contractnumber) as 's2012'
       FROM [REPORT].[dbo].[76-2012] as t3
         right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask
		 where (t3. [Vid] like N'%транспорту, кр%') 
    GROUP BY  t3.lb1, b.name) as t20
	left join    ( SELECT t3.lb1,b.name as name, count(distinct t3.Дог2012) as 's2013' 
	     
       FROM 
	      (SELECT t1.lb1,  t1.ContractNumber as 'Дог2012' ,   t1.externalchannel, t1.Beneficiary 
             FROM [REPORT].[dbo].[76-2012] as t1
             join [REPORT].[dbo].[76-2013] as t2
              on t1.RegistrationNumber =t2.RegistrationNumber 
	             where (t2.[ContractNumber] != t1.[ContractNumber]) and  (t1. [Vid] = t2. [Vid] ) and  (t1. [Vid] like N'%транспорту, кр%' ) and (t1.Object = t2.Object)
	             and (t1. [INSURER] = t2. [INSURER]) 
		        group by  t1.lb1, t1.ContractNumber, t1.externalchannel, t1.Beneficiary 
		  ) as t3
      right JOIN @as b ON  t3.externalchannel LIKE b.mask  OR t3.Beneficiary LIKE b.mask 
	    where t3.lb1 is not null
  GROUP BY   t3.lb1, b.name) as t10
	on t10.lb1 = t20.lb1 and t10.name = t20.name


Выполняется 10 мин. в принципе, подходит
13 фев 14, 17:34    [15564124]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить