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

Откуда:
Сообщений: 469
Добрый день!

Помогите с написанием запроса:
Запрос должен вернуть ноль записей, так как свойство 'Активного в-ва' для выбранных товаров различаются.

begin tran

select 1 ID, 'Гепатромбин 30000 ЕД Мазь 40г' Name 
into #TMC
union all 
select 2, 'Гепатромбин 50000 ед мазь 40г'

select 1 TMC_ID, 'Действующее в-во' Class, convert(varchar(80), null) Unit, 'Гепатромбин' Value
into #Property
union all
select 2, 'Действующее в-во', null Unit, 'Гепатромбин' 
union all 
select 1, 'Активного в-ва', 'ЕД', '30000'
union all 
select 1, 'Активного в-ва', 'г', '40'
union all 
select 2, 'Активного в-ва', 'ЕД', '50000'
union all 
select 2, 'Активного в-ва', 'г', '40'

-- Изменить запрос
select * 
from #TMC t
inner join #TMC t2 on t.ID<>t2.ID
inner join #Property pd on pd.Class='Действующее в-во'
  and pd.TMC_ID=t.ID
inner join #Property pd2 on pd2.Class='Действующее в-во'
  and pd2.TMC_ID=t2.ID
inner join  #Property pa on pa.Class='Активного в-ва'
  and pa.TMC_ID=t.ID
inner join  #Property pa2 on pa2.Class='Активного в-ва'
  and pa2.TMC_ID=t2.ID
where
 	  pd.Value=pd2.Value
  and pa.Value=pa2.Value
  
rollback
3 мар 16, 11:53    [18891219]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Lisichkin
Member

Откуда:
Сообщений: 469
Ой, забыл в запросе дописать
and pa.Unit=pa2.Unit
3 мар 16, 11:55    [18891240]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Можно вопрос... а в чем сакральный смысл того что Вы хотите получить?
3 мар 16, 11:58    [18891258]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Lisichkin
from #TMC t
inner join #TMC t2 on t.ID<>t2.ID

А что это за шедевр ? Что он должен делать ?

Вы хотите сравнивать кортежи на их соответствие/различие ?
3 мар 16, 12:02    [18891289]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Lisichkin
Member

Откуда:
Сообщений: 469
Я хочу найти одинаковые/разные препараты.
В приведенном мной примере препараты разные. И поэтому результирующий набор должен быть пустой.
3 мар 16, 12:02    [18891290]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Lisichkin
Member

Откуда:
Сообщений: 469
Glory
Во первых, это пример, а во вторых у Вас что ни когда в справочнике ТМЦ не было товаров с одинаковыми характеристиками, но разными идентификаторами??
3 мар 16, 12:04    [18891303]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Lisichkin
Во первых, это пример, а во вторых у Вас что ни когда в справочнике ТМЦ не было товаров с одинаковыми характеристиками, но разными идентификаторами??

Во-первых, отвечайте на заданные вопросы
Во-вторых, дальнейшие ответы будут зависеть от во-первых

Для сравнения кортежей свойств товара не нужна таблица с наименования товаров

Сообщение было отредактировано: 3 мар 16, 12:07
3 мар 16, 12:07    [18891321]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Lisichkin
Member

Откуда:
Сообщений: 469
Название товара я привел для примера, для понимания того что нужно сравнивать
3 мар 16, 12:10    [18891348]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Lisichkin
Название товара я привел для примера, для понимания того что нужно сравнивать

Именно для примера вы использовали эту таблицу 2 раза да еще по условию t.ID<>t2.ID ?
3 мар 16, 12:13    [18891368]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Lisichkin,

намек...
select 
	* 
from 
	(select 
		*, count(*) over (partition by txt) cnt
	from
		(select 
			*,
			(select Class +';' + value from #Property p where p.TMC_ID = t.ID order by Class for xml path('')) txt
		from 
			#TMC t) a) a1
where 
	cnt > 1
3 мар 16, 12:28    [18891450]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Lisichkin,

больной с утра мозг родил сие :)

автор

SELECT DISTINCT
q.Name,w.Name
FROM
#Property x
JOIN
#TMC q
ON
q.Id = x.TMC_Id
CROSS APPLY
(
SELECT a.Tmc_Id,COUNT(*) as Cnt
FROM
#Property a
JOIN
#Property b
ON
b.Tmc_Id = x.Tmc_Id
WHERE
a.TMC_Id != x.TMC_Id AND
a.Class = b.Class AND
a.Value = b.Value AND
ISNULL(a.Unit,'') = ISNULL(b.Unit,'')
GROUP BY
a.Tmc_Id
) y
JOIN
#TMC w
ON
w.Id = y.TMC_Id
WHERE
(
SELECT Count(*)
FROM #Property
WHERE
Tmc_Id = x.Tmc_Id
) = y.Cnt
3 мар 16, 12:28    [18891454]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
o-o
Guest
сделайте пошагово,
у меня в cte много лишнего, чисто чтобы показать логику.
1. сджойнили неодинаковые ид (берем ид1 < ид2, чтоб не 2 раза) по одинаковым классам и юнитам (сделайте select * from cte)
2. посчитали, сколько несовпадающих по значению
3. в ответе те, где нет несовпадений, ну т.е. дубли
with cte as
(
select *
from #TMC t join #Property p
       on t.id = p.TMC_ID 
)    

,cte1 as
(
select c1.id as id1, c2.id as id2, c1.class, c1.unit, c1.value as val1, c2.value as val2, 
       case 
           when c1.value = c2.value then 1
           else 0
       end as is_equal    
from cte c1 join cte c2 
      on c1.id < c2.id and  
      ((c1.class = c2.class and c1.unit = c2.unit) OR
      (c1.class = c2.class and c1.unit is null and c2.unit is null))
      
)

select id1, id2, count(*) as cnt, sum(is_equal) as cnt_equal
from cte1
group by id1, id2
having count(*) =  sum(is_equal);
3 мар 16, 12:44    [18891549]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Lisichkin
Member

Откуда:
Сообщений: 469
Спасибо o-o
Я если честно копал не cte а exists/not exist

Несколько усложню задачу:
Единицы измерения для одного и того же товара может не совпадать: Бромгексин есть в таблетках, а есть в сиропе:

begin tran

select 1 ID, 'Гепатромбин 30000 ЕД Мазь 40г' Name 
into #TMC
union all 
select 2, 'Гепатромбин 50000 ед мазь 40г'
union all 
select 3, 'Бромгексин  8мг'
union all 
select 4, 'Бромгексин сироп 100мл'

select 1 TMC_ID, 'Действующее в-во' Class, convert(varchar(80), null) Unit, 'Гепатромбин' Value
into #Property
union all
select 2, 'Действующее в-во', null Unit, 'Гепатромбин' 
union all 
select 1, 'Активного в-ва', 'ЕД', '30000'
union all 
select 1, 'Активного в-ва', 'г', '40'
union all 
select 2, 'Активного в-ва', 'ЕД', '50000'
union all 
select 2, 'Активного в-ва', 'г', '40'
union all
select 3, 'Действующее в-во', null Unit, 'Бромгексин' 
union all
select 4, 'Действующее в-во', null Unit, 'Бромгексин' 
union all 
select 3, 'Действующее в-во', 'г', '8'
union all 
select 4, 'Действующее в-во', 'мл', '100'

;with cte as
(
select *
from #TMC t join #Property p
       on t.id = p.TMC_ID 
)    
,cte1 as
(
select c1.id as id1, c2.id as id2, c1.class, c1.unit, c1.value as val1, c2.value as val2, 
       case 
           when c1.value = c2.value then 1
           else 0
       end as is_equal    
from cte c1 join cte c2 
      on c1.id < c2.id and  
      ((c1.class = c2.class and c1.unit = c2.unit) OR
      (c1.class = c2.class and c1.unit is null and c2.unit is null))
      
)
-- Не верный результат :(
select id1, id2, count(*) as cnt, sum(is_equal) as cnt_equal
from cte1
group by id1, id2
having count(*) =  sum(is_equal);
	
rollback
3 мар 16, 12:54    [18891615]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Lisichkin,

т.е. теперь должно "почти" совпадать что-ли?
3 мар 16, 13:00    [18891650]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Lisichkin
Единицы измерения для одного и того же товара может не совпадать: Бромгексин есть в таблетках, а есть в сиропе:

Разве в этом случае именование единицы измерения не должно быть тоже свойством товара ?
3 мар 16, 13:22    [18891780]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Lisichkin,

Мой вариант выдает правильно?
3 мар 16, 13:54    [18892034]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Lisichkin
Member

Откуда:
Сообщений: 469
Извините описался.

С Вашей помощью я написал следующий запрос:

begin tran

select 1 ID, 'Гепатромбин 30000 ЕД Мазь 40г' Name 
into #TMC
union all 
select 2, 'Гепатромбин 50000 ед мазь 40г'
union all 
select 3, 'Бромгексин  8мг'
union all 
select 4, 'Бромгексин сироп 100мл'

select 1 TMC_ID, 'Действующее в-во' Class, convert(varchar(80), null) Unit, 'Гепатромбин' Value
into #Property
union all
select 2, 'Действующее в-во', null Unit, 'Гепатромбин' 
union all 
select 1, 'Активного в-ва', 'ЕД', '30000'
union all 
select 1, 'Активного в-ва', 'г', '40'
union all 
select 2, 'Активного в-ва', 'ЕД', '50000'
union all 
select 2, 'Активного в-ва', 'г', '40'
union all
select 3, 'Действующее в-во', null Unit, 'Бромгексин' 
union all
select 4, 'Действующее в-во', null Unit, 'Бромгексин' 
union all 
select 3, 'Активного в-ва', 'г', '8'
union all 
select 4, 'Активного в-ва', 'мл', '100'

;with cte as
(
select *
from #TMC t join #Property p
       on t.id = p.TMC_ID 
)    

,cte1 as
(
select c1.id as id1, c2.id as id2, c1.class, c1.unit, c1.value as val1, c2.value as val2, 
       case 
           when c1.value = c2.value then 1
           else 0
       end as is_equal    
from cte c1 join cte c2 
      on c1.id < c2.id and  
      ((c1.class = c2.class and c1.unit = c2.unit) OR
      (c1.class = c2.class and c1.unit is null and c2.unit is null))
      
)
-- Результат id(3)=Id(4), Что не верно
select id1, id2, count(*) as cnt, sum(is_equal) as cnt_equal
from cte1
group by id1, id2
having count(*) =  sum(is_equal);

-- Результат пустое множествр, Что верно
select t.ID, t2.ID, t.Name, t2.Name, ps.SameCount, pc.PropCount, pc2.PropCount
from #TMC t
inner join #TMC t2 on t.ID>t2.ID
inner join #Property pd on pd.Class='Действующее в-во'
  and pd.TMC_ID=t.ID
inner join #Property pd2 on pd2.Class='Действующее в-во'
  and pd2.TMC_ID=t2.ID
outer apply (
		select pa.TMC_ID, pa2.TMC_ID ID2, count(*) SameCount
		from #Property pa
		inner join #Property pa2 on pa2.Class='Активного в-ва'
		  and pa2.TMC_ID=t2.ID
		where pa.Class='Активного в-ва'
		  and pa.TMC_ID=t.ID
		  and pa.Unit=pa2.Unit 
		  and pa.Value=pa2.Value
		group by
		  pa.TMC_ID, pa2.TMC_ID) ps 
outer apply (		  
		select pa.TMC_ID, count(*) PropCount
		from #Property pa
		where pa.Class='Активного в-ва'
		  and pa.TMC_ID=t.ID
		group by
		  pa.TMC_ID) pc 
outer apply (		  
		select pa2.TMC_ID, count(*) PropCount
		from #Property pa2
		where pa2.Class='Активного в-ва'
		  and pa2.TMC_ID=t2.ID
		group by
		  pa2.TMC_ID) pc2
where
 	  pd.Value=pd2.Value and
 	  ( 
 	   -- Совпадают все свойства
 	   (ps.SameCount=pc.PropCount and ps.SameCount=pc2.PropCount) or
 	   -- Все свойства отсутствуют
 	    (pc.PropCount is null and pc.PropCount is null) 
 	  )
   	
rollback
3 мар 16, 14:17    [18892169]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Lisichkin,

автор
Извините описался.

бывает, может это могут вылечить.

автор
С Вашей помощью я написал следующий запрос:

ниправда, такой ереси тебе никто не советовал
3 мар 16, 14:26    [18892242]     Ответить | Цитировать Сообщить модератору
 Re: Не выходит каменный цветок :)  [new]
Lisichkin
Member

Откуда:
Сообщений: 469
WarAnt

Ваш запрос тоже дает верный результат, и намного короче чем мой :)
3 мар 16, 14:27    [18892264]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить