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

Откуда:
Сообщений: 269
 		Добрый день. Прошу помочь с выборкой.

Таблица

create table gas_client (ClientID int)
insert into gas_client values (12287)
insert into gas_client values (12900)
create table gas_address (ClientID int,Address varchar(50),AddressType smallint)
insert into gas_address values (12287,'Почтовый',3)	
insert into gas_address values (12287,'МестоРегистр',5)
insert into gas_address values (12287,'МестоПребывания',6)
insert into gas_address values (12900,'МестоРегистрСтарое',1)	
insert into gas_address values (12900,'МестоРегистр',5)
insert into gas_address values (12900,'МестоПребывания',6)

select c.ClientID,a1.Address,a2.Address,a3.Address from gas_client c
	left outer join gas_address a1 on c.ClientID=a1.ClientID
	left outer join gas_address a2 on c.ClientID=a2.ClientID
	left outer join gas_address a3 on c.ClientID=a3.ClientID
where (a1.AddressType=1 and a2.AddressType=5 and a3.AddressType=6)


У одного клиента нет адреса с типом = 1 поэтому результат:

12900	МестоРегистрСтарое	МестоРегистр	МестоПребывания


Хочется так:

12287   Null 			МестоРегистр	МестоПребывания
12900	МестоРегистрСтарое	МестоРегистр	МестоПребывания


Заранее благодарю!
28 мар 13, 09:36    [14105428]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
 		Убирите из WHERE уловия и пристыкуйте к соответствующим выражениям ON
28 мар 13, 09:39    [14105440]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Евгенич
Member

Откуда:
Сообщений: 269
ambarka_max
Убирите из WHERE уловия и пристыкуйте к соответствующим выражениям ON


не очень понял, извините?
28 мар 13, 12:20    [14106423]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
 
select c.ClientID,a1.Address,a2.Address,a3.Address from gas_client c
	left outer join gas_address a1 on c.ClientID=a1.ClientID and a1.AddressType=1
	left outer join gas_address a2 on c.ClientID=a2.ClientID and a2.AddressType=5
	left outer join gas_address a3 on c.ClientID=a3.ClientID and a3.AddressType=6
28 мар 13, 12:21    [14106427]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Евгенич
Member

Откуда:
Сообщений: 269
 		Теперь понял, все сделал! СПАСИБО!
28 мар 13, 12:23    [14106439]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
zozozozozo
Guest 
select *
from
	dbo.gas_address
		pivot(min([Address]) for [AddressType] in ([1], [5], [6])) as d;
28 мар 13, 15:04    [14107712]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Евгенич
Member

Откуда:
Сообщений: 269
 		Извиняюсь, но еще вопрос - ситуация усложнилась.

Если в таблице gas_address несколько аналогичных записей

insert into gas_address values (12900,'МестоПребывания',6)


то получаем

12287   Null                    МестоРегистр	МестоПребывания
12900	МестоРегистрСтарое	МестоРегистр	МестоПребывания
12900	МестоРегистрСтарое	МестоРегистр	МестоПребывания


А хочется взять max или min от строки 12900 чтобы было

12287   Null 			МестоРегистр	МестоПребывания
12900	МестоРегистрСтарое	МестоРегистр	МестоПребывания


Что-то типа того
left outer join gas_address a1 on c.ClientID=a1.ClientID and max(a1.AddressType)=1
29 мар 13, 15:06    [14113342]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
 
select c.ClientID, max(a1.Address), max(a2.Address), max(a3.Address) from gas_client c
	left outer join gas_address a1 on c.ClientID=a1.ClientID and a1.AddressType=1
	left outer join gas_address a2 on c.ClientID=a2.ClientID and a2.AddressType=5
	left outer join gas_address a3 on c.ClientID=a3.ClientID and a3.AddressType=6
group by c.ClientID
29 мар 13, 15:54    [14113610]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Гость333
select c.ClientID, max(a1.Address), max(a2.Address), max(a3.Address) from gas_client c
	left outer join gas_address a1 on c.ClientID=a1.ClientID and a1.AddressType=1
	left outer join gas_address a2 on c.ClientID=a2.ClientID and a2.AddressType=5
	left outer join gas_address a3 on c.ClientID=a3.ClientID and a3.AddressType=6
group by c.ClientID

Плохой
29 мар 13, 16:05    [14113674]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Евгенич
Member

Откуда:
Сообщений: 269
ambarka_max
Гость333
select c.ClientID, max(a1.Address), max(a2.Address), max(a3.Address) from gas_client c
	left outer join gas_address a1 on c.ClientID=a1.ClientID and a1.AddressType=1
	left outer join gas_address a2 on c.ClientID=a2.ClientID and a2.AddressType=5
	left outer join gas_address a3 on c.ClientID=a3.ClientID and a3.AddressType=6
group by c.ClientID

Плохой


Как получить одну строку (первую попавшуюся) для каждого типа адреса по клиенту, не используя при этом группировку, иными словами:

из таблицы gas_address

ClientID        Address              AddressType      Value
12287          Почтовый                 3              Москва
12287          Почтовый                 3              Питер
12287          МестоРегистр             5              Воронеж
12287          МестоРегистр             5              Пенза
12287          МестоРегистр             5              Самара


получить 

ClientID        Address              AddressType      Value
12287          Почтовый                 3              Москва
12287          МестоРегистр             5              Воронеж
5 апр 13, 09:13    [14138607]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
мимо
Guest 
; with a as (
select *
,row_number() over(partition by AddressType order by Value) as rt
from (values 
(12287,          'Почтовый',                 3,              'Москва')
,(12287,          'Почтовый',                 3,              'Питер')
,(12287,          'МестоРегистр',             5,              'Воронеж')
,(12287,          'МестоРегистр',             5,              'Пенза')
,(12287,          'МестоРегистр',             5,              'Самара')
) as z (ClientID,        Address,              AddressType,      Value)
)

select  *
from a
where rt = 1
5 апр 13, 09:34    [14138646]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Евгенич
Member

Откуда:
Сообщений: 269
мимо
; with a as (
select *
,row_number() over(partition by AddressType order by Value) as rt
from (values 
(12287,          'Почтовый',                 3,              'Москва')
,(12287,          'Почтовый',                 3,              'Питер')
,(12287,          'МестоРегистр',             5,              'Воронеж')
,(12287,          'МестоРегистр',             5,              'Пенза')
,(12287,          'МестоРегистр',             5,              'Самара')
) as z (ClientID,        Address,              AddressType,      Value)
)

select  *
from a
where rt = 1


Виноват, но у меня SQL 2000 там нет row_number()
5 апр 13, 10:18    [14138769]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Crimzic
Member

Откуда: Sydney
Сообщений: 59
Евгенич
Виноват, но у меня SQL 2000 там нет row_number()

select c.ClientID,
  a1Address = (SELECT TOP 1 a1.Address FROM gas_address a1 WHERE c.ClientID=a1.ClientID and a1.AddressType=1 ORDER BY a1.Address),
  a2Address = (SELECT TOP 1 a2.Address FROM gas_address a2 WHERE c.ClientID=a2.ClientID and a2.AddressType=5 ORDER BY a2.Address),
  a3Address = (SELECT TOP 1 a3.Address FROM gas_address a3 WHERE c.ClientID=a3.ClientID and a3.AddressType=6 ORDER BY a3.Address)
from gas_client c

Может быть не очень быстро при большом количестве строк.
5 апр 13, 10:24    [14138792]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
мимо
Guest
Евгенич
мимо
; with a as (
select *
,row_number() over(partition by AddressType order by Value) as rt
from (values 
(12287,          'Почтовый',                 3,              'Москва')
,(12287,          'Почтовый',                 3,              'Питер')
,(12287,          'МестоРегистр',             5,              'Воронеж')
,(12287,          'МестоРегистр',             5,              'Пенза')
,(12287,          'МестоРегистр',             5,              'Самара')
) as z (ClientID,        Address,              AddressType,      Value)
)

select  *
from a
where rt = 1


Виноват, но у меня SQL 2000 там нет row_number()

Проапгрейдить сервер.
5 апр 13, 10:47    [14138915]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Евгенич
Member

Откуда:
Сообщений: 269
мимо
Евгенич
пропущено...


Виноват, но у меня SQL 2000 там нет row_number()

Проапгрейдить сервер.


Нарыл в инете identity(int,1,1) вместо row_number() но не могу использовать его по группе (partition by)
5 апр 13, 10:49    [14138928]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Glory
Member

Откуда:
Сообщений: 104760
Евгенич
Нарыл в инете identity(int,1,1) вместо row_number()

identity(int,1,1) не является заменой row_number()
5 апр 13, 10:55    [14138966]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Евгенич
Нарыл в инете identity(int,1,1) вместо row_number() но не могу использовать его по группе (partition by)

Используете нарытый в инете identity(int,1,1).
Получаете некую таблицу, пусть #tmp со столбцом id=identity(int,1,1).
К этой таблице делаете запрос:
select t.*
from #tmp t
where t.id in (select min(t1.id) from #tmp t1 group by t1.ClientID, t1.AddressType)
5 апр 13, 10:57    [14138979]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Евгенич
Как получить одну строку (первую попавшуюся) для каждого типа адреса по клиенту, не используя при этом группировку

Вот оно как. А почему нельзя использовать группировку?
5 апр 13, 10:58    [14138983]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Евгенич
Member

Откуда:
Сообщений: 269
Гость333
Евгенич
Как получить одну строку (первую попавшуюся) для каждого типа адреса по клиенту, не используя при этом группировку

Вот оно как. А почему нельзя использовать группировку?


В реалности в таблице gas_address очень много полей, по которым также придется делать группировку, чего делать не хотелось бы
5 апр 13, 11:21    [14139138]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
 		Евгенич,

а ч то мешает гаписать нужное количество полей в запросе и сделать групировку,а не шерситть всю простыню ?
5 апр 13, 11:22    [14139148]     Ответить | Цитировать Сообщить модератору
 Re: Один ко многим в строку  [new]
trayal
Member

Откуда: Пенза
Сообщений: 471
 		distinct ?
5 апр 13, 16:16    [14141338]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить