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

Откуда:
Сообщений: 185
всем здравия, вопрос важный:
--есть табличка, в ней значения
declare @t as table(id int, id_subject int, property varchar(10), value varchar(5))
insert into @t(id,id_subject,property,value) 
select 1,1,'Ширина','250' union all 
select 2,1,'Длина','125' union all 
select 3,1,'Высота','100' union all 
select 4,2,'Ширина','250' union all 
select 5,2,'Длина','250' union all 
select 6,2,'Высота','125' union all 
select 7,3,'Ширина','125' union all 
select 8,3,'Длина','250' union all 
select 9,3,'Высота','225' union all 
select 10,4,'Ширина','250' union all 
select 11,4,'Длина','250' union all 
select 12,4,'Высота','125'
по сути, хранит такие данные:
1. 250x125x100
2. 250x250x125
3. 125x250x225
4. 250x250x125

Как можно из этого всего получить только уникальные размеры? Т.е. реально данные на выходе нужны без 4 сабжекта, т.к. он точно такой же как и второй по трём параметрам.
результат такой нужно получить:
1 Ширина 250
1 Длина 125
1 Высота 100
2 Ширина 250
2 Длина 250
2 Высота 125
3 Ширина 125
3 Длина 250
3 Высота 225
по какому id_subject группировать - неважно, т.к. нужен только результат уникальных габаритов.
буду премного благодарен за любую помощь.
6 окт 09, 22:29    [7750587]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
извините, не указал, БД SQL 2008
6 окт 09, 22:44    [7750618]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Попробуйте так:
declare @t as table(id int, id_subject int, property varchar(10), value varchar(5))
insert into @t(id,id_subject,property,value) 
select 1,1,'Ширина','250' union all 
select 2,1,'Длина','125' union all 
select 3,1,'Высота','100' union all 
select 4,2,'Ширина','250' union all 
select 5,2,'Длина','250' union all 
select 6,2,'Высота','125' union all 
select 7,3,'Ширина','125' union all 
select 8,3,'Длина','250' union all 
select 9,3,'Высота','225' union all 
select 10,4,'Ширина','250' union all 
select 11,4,'Длина','250' union all 
select 12,4,'Высота','125'

select id,dimenType=case dimenType when 1 then 'Длина' when 2 then 'Ширина' else 'Высота' end,dimenSize
from(
select id=min(id_subject),[1],[2],[3]
from(
select id_subject,r=case property when 'Длина' then 1 when 'Ширина' then 2 else 3 end,value from @t
)p
pivot(max(value) for r in([1],[2],[3]))pv
group by [1],[2],[3]
)t
unpivot(dimenSize for dimenType in([1],[2],[3]))up
iddimenTypedimenSize
1Длина125
1Ширина250
1Высота100
3Длина250
3Ширина125
3Высота225
2Длина250
2Ширина250
2Высота125
6 окт 09, 23:13    [7750731]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
большое спасибо,
вроде запрос сработал как надо,
по-крайней мере на первый взгляд.

тяжело отследить ибо записей очень много, тысяч 8.
эх, я б до такого и не додумался.

так, а если в дальнейшем у сабжекта будет 2 параметра или 4, то просто цифр отнять/добавить?
хочу сохранить запросик к себе в копилку.
6 окт 09, 23:39    [7750790]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
круто, даже работает тогда, если у какого-то сабжа нехватает одного из пропертиес
6 окт 09, 23:45    [7750802]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
viktor zelenin
если в дальнейшем у сабжекта будет 2 параметра или 4, то просто цифр отнять/добавить?
да, натолкайте в оба case нужные варианты.
viktor zelenin
эх, я б до такого и не додумался.... хочу сохранить запросик к себе в копилку.
заходите для разминки на один хороший сайт, будете это делать на автопилоте :-)
6 окт 09, 23:58    [7750835]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
Ozzy-Osbourne
select id,dimenType=case dimenType when 1 then 'Длина' when 2 then 'Ширина' else 'Высота' end,dimenSize
from(
select id=min(id_subject),[1],[2],[3]
from(
select id_subject,r=case property when 'Длина' then 1 when 'Ширина' then 2 else 3 end,value from @allvalue
)p
pivot(max(value) for r in([1],[2],[3]))pv
group by [1],[2],[3]
)t
unpivot(dimenSize for dimenType in([1],[2],[3]))up


такая сложная функция написана по выбору уникальных значений, я всё скинул в таблицу
@unique.
теперь такой вопрос встал: таблица @allvalue хранит все параметры по каждому сабжу из таблицы @subjects.
Мне нужно в таблицу @subjects, в поле id_group перенести те id из таблицы @group, где значения сабжа из таблицы @allvalues равны тем же значениям из таблицы @unique.

Т.е. в конечном виде таблица @subjects должна принять вид:
1  'Шкаф'      1 
3 'Тумбочка' 2
7 'Трюмо' 3
8 'Кровать' 3

Очень прошу посодействовать в решении данного вопроса.
вот все четыре таблицы:
declare @subjects as table(id int, subject_name varchar(10), id_group int)
insert into @subjects(id,subject_name,id_group) 
select 1,'Шкаф',NULL union all 
select 3,'Тумбочка',NULL union all 
select 7,'Трюмо',NULL union all 
select 8,'Кровать',NULL

declare @allvalue as table(id int, id_subject int, property varchar(10), value varchar(5))
insert into @allvalue(id,id_subject,property,value) 
select 421,1,'Ширина','250' union all 
select 422,1,'Длина','125' union all 
select 423,1,'Высота','100' union all 
select 457,7,'Ширина','250' union all 
select 458,7,'Длина','250' union all 
select 459,7,'Высота','125' union all 
select 557,3,'Ширина','125' union all 
select 558,3,'Длина','250' union all 
select 559,3,'Высота','225' union all 
select 700,8,'Ширина','250' union all 
select 701,8,'Длина','250' union all 
select 702,8,'Высота','125'

declare @unique as table(id_group int, property varchar(10), value varchar(5))
insert into @unique(id_group,property,value) 
select 1,'Ширина','250' union all 
select 1,'Длина','125' union all 
select 1,'Высота','100' union all 
select 2,'Ширина','125' union all 
select 2,'Длина','250' union all 
select 2,'Высота','225' union all 
select 3,'Ширина','250' union all 
select 3,'Длина','250' union all 
select 3,'Высота','125'

declare @group as table(id_group int)
insert into @group(id_group) 
select 1 union all 
select 2 union all 
select 3
7 окт 09, 12:49    [7752817]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
автор
нужно в таблицу @subjects, в поле id_group перенести те id из таблицы @group, где значения сабжа из таблицы @allvalues равны тем же значениям из таблицы @unique.
Соединять всё равно по условию равенства ТРЁХ значений (длины, ширины и высоты). Поскольку значения эти "размазаны" по трём строкам, делаем опять-таки pivot'ы для транспонирования их в три столбца. А затем соединяем две таблицы по условию равенства трёх столбцов (эти таблицы есть результаты транспонирования #allValue и #unique; они названы соотв-но avp и uvp).
Результат соединения обозван t0.
В итоге команда update выглядит просто: надо проставить в #subjects.id_group значения, находящиеся в t0.idg для соотв-щего измерения.
Вот что получилось в итоге:
1) тестовые данные
+
if object_id('tempdb..#subjects')>0 drop table #subjects
create table #subjects (id int, subject_name varchar(10), id_group int)
insert into #subjects(id,subject_name,id_group) 
select 1,'Шкаф',NULL union all 
select 3,'Тумбочка',NULL union all 
select 7,'Трюмо',NULL union all 
select 8,'Кровать',NULL

if object_id('tempdb..#allvalue')>0 drop table #allvalue
create table #allvalue (id int, id_subject int, property varchar(10), value varchar(5))
insert into #allvalue(id,id_subject,property,value) 
select 421,1,'Ширина','250' union all 
select 422,1,'Длина','125' union all 
select 423,1,'Высота','100' union all 
select 457,7,'Ширина','250' union all 
select 458,7,'Длина','250' union all 
select 459,7,'Высота','125' union all 
select 557,3,'Ширина','125' union all 
select 558,3,'Длина','250' union all 
select 559,3,'Высота','225' union all 
select 700,8,'Ширина','250' union all 
select 701,8,'Длина','250' union all 
select 702,8,'Высота','125'

if object_id('tempdb..#unique')>0 drop table #unique
create table #unique (id_group int, property varchar(10), value varchar(5))
insert into #unique(id_group,property,value) 
select 1,'Ширина','250' union all 
select 1,'Длина','125' union all 
select 1,'Высота','100' union all 
select 2,'Ширина','125' union all 
select 2,'Длина','250' union all 
select 2,'Высота','225' union all 
select 3,'Ширина','250' union all 
select 3,'Длина','250' union all 
select 3,'Высота','125'

if object_id('tempdb..#group')>0 drop table #group
create table #group (id_group int)
insert into #group(id_group) 
select 1 union all 
select 2 union all 
select 3

2) сам запрос:
with
avp as
(
select ids,[1]=max([1]),[2]=max([2]),[3]=max([3])
from(
select ids=id_subject,r=case property when 'Длина' then 1 when 'Ширина' then 2 else 3 end,value from #allValue
)p
pivot(max(value) for r in([1],[2],[3]))pv
group by ids
)
--select * from avp

,uvp as
(
  select idg,[1]=max([1]),[2]=max([2]),[3]=max([3])
  from(
  select idg=id_group,r=case property when 'Длина' then 1 when 'Ширина' then 2 else 3 end,value from #unique
  )p pivot(max(value)for r in([1],[2],[3]))pv 
  group by idg
)
--select * from uvp

update #subjects
set id_group=
(select t0.idg
 from
 (
    select a.ids,u.idg
    from avp a
    join uvp u on a.[1]=u.[1] and a.[2]=u.[2] and a.[3]=u.[3]
 )t0
 where id=t0.ids
)
select * from #subjects
result:
idsubject_nameid_group
1Шкаф1
3Тумбочка2
7Трюмо3
8Кровать3


ЗЫ. см в аттаче скриншот - фрагмент из .xls (там удобно смотреть промежуточные результаты)

К сообщению приложен файл. Размер - 0Kb
7 окт 09, 17:15    [7754785]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Ozzy-Osbourne
заходите для разминки на один хороший сайт, будете это делать на автопилоте :-)

цитата Ozzy-Osbourne с 'одного хорошего сайта' пару месяцев назад
...каждый раз перед необходимостью в очередной раз "сбацать пивот" мне приходилось обращаться к черновикам предыдущего применения. Сиё означает, что "окончательно разобраться" с ним мне не удалось. Согласен с ***: конструкция этого оператора почему-то не оседает в голове надолго...

Судя по всему, конструкция, наконец-то, осела.
7 окт 09, 17:37    [7754903]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Anddros
Судя по всему, конструкция, наконец-то, осела.
ну да, я ж там "честное пионерское" дал, что перестану юзать кейсы :-)
7 окт 09, 17:41    [7754935]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
Ozzy-Osbourne,
вот это запросище, тыкался-мыкался, что-то нехватает чтоли?

скюл ругается, мол:
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near 'avp'.
Msg 102, Level 15, State 1, Line 57
Incorrect syntax near ','.


вроде, запятой чтоли нехватает?
7 окт 09, 19:14    [7755406]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
vino
Member

Откуда:
Сообщений: 1191
viktor zelenin, попробуйте перед первым with поставить точку с запятой, в смысле - ;with
7 окт 09, 19:34    [7755470]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
vino, и как всегда прямо в цель!

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

Ozzy-Osbourne, мегареспектище вам,
просто жизненноважный был запрос.
моя база похудела на 20кг.
надоело каждому сабжу выставлять отельно размеры, когда известно что они как правило эталонны.
7 окт 09, 21:07    [7755769]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
viktor zelenin
Member

Откуда:
Сообщений: 185
Ozzy-Osbourne, даже в экселе рисовали схему, чтобы решить мою задачу?
мне даже неудобно както стало...

всё читаю снова, но с пивотами я вообще никогда не работал,
пытался, но понял что ещё крайне слабоват для этого.
7 окт 09, 21:19    [7755816]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
viktor zelenin
Ozzy-Osbourne, даже в экселе рисовали схему, чтобы решить мою задачу? мне даже неудобно както стало...
да ладно Вам, перестаньте... :-) здесь же принято помогать другим решать задачи, если есть на то свободное время и желание. У меня сегодня оба этих параметра были установлены в true, вот и всё. А в экселе я просто привык смотреть промежуточные результаты, так быстрее думается. Сделать оттуда копипаст части экрана было не сложнее, чем написать запрос :-)
ЗЫ. А на сайт всё-таки загляните, потренируйтесь. Не пожалеете.
7 окт 09, 21:41    [7755886]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать выборку уникальных записей по трём полям?  [new]
RFT
Member

Откуда: Москва
Сообщений: 999
Просто еще вариант. Вроде результат более читаемый.
declare @t as table(id int, id_subject int, property varchar(10), value varchar(5))
insert into @t(id,id_subject,property,value) 
select 1,1,'Ширина','250' union all 
select 2,1,'Длина','125' union all 
select 3,1,'Высота','100' union all 
select 4,2,'Ширина','250' union all 
select 5,2,'Длина','250' union all 
select 6,2,'Высота','125' union all 
select 7,3,'Ширина','125' union all 
select 8,3,'Длина','250' union all 
select 9,3,'Высота','225' union all 
select 10,4,'Ширина','250' union all 
select 11,4,'Длина','250' union all 
select 12,4,'Высота','125'

select   distinct (Ш + 'x' + Д + 'x' + В) as ШхДхВ, Ш, Д, В from (
select id_subject, max(case property when 'Ширина' then value end) as Ш, 
max(case property when 'Длина' then value end) as Д, 
max(case property when 'Высота' then value end) as В
from @t
group by id_subject
) as tt
ШхДхВ	          Ш	Д	В
125x250x225 125 250 225
250x125x100 250 125 100
250x250x125 250 250 125
8 окт 09, 11:47    [7757665]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить