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

Откуда: Уфа
Сообщений: 185
Есть таблица вида

BRID,FAMILY, ADRESS, DISTANCE
119 Иванов Проспект 10,2
119 Иванов Улица 9,8
120 Сидоров Переулок 7,7

т.е. таблица составленная из нескольких где указаны расстояния от машин до адресов.Из них надо выбрать ближайшие точки для каждой машины.

Как выбрать из неё так чтобы показало таблицу такого вида:
BRID,FAMILY, ADRESS, DISTANCE
119 Иванов Улица 9,8 - 9,8 меньше чем 10,2 поэтому попадает в выборку.
120 Сидоров Переулок 7,7
28 авг 09, 12:41    [7590212]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Для всех версий MS SQL Server надо?
28 авг 09, 12:45    [7590229]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
так наверна
Guest
SELECT a.BRID, a.FAMILY, a.ADRESS, a.DISTANCE
FROM [Есть таблица вида] a
WHERE a.DISTANCE = (SELECT MIN(b.DISTANCE) FROM [Есть таблица вида] b WHERE a.FAMILY=b.FAMILY)
28 авг 09, 12:47    [7590243]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
ну сейчас это крутится под SQL 2000
28 авг 09, 12:47    [7590245]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
так наверна
SELECT a.BRID, a.FAMILY, a.ADRESS, a.DISTANCE
FROM [Есть таблица вида] a
WHERE a.DISTANCE = (SELECT MIN(b.DISTANCE) FROM [Есть таблица вида] b WHERE a.FAMILY=b.FAMILY)


там эта - у каждой записи адрес разный!!!
28 авг 09, 12:48    [7590251]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
`
Guest
Гений сыска
...

там эта - у каждой записи адрес разный!!!


и??
28 авг 09, 12:49    [7590257]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
-- test data
declare @t table(BRID int, FAMILY varchar(16), ADRESS varchar(16), DISTANCE decimal(7, 1))
insert into @t(BRID, FAMILY, ADRESS, DISTANCE)
select 119, 'Иванов',  'Проспект', 10.2 union all
select 119, 'Иванов',  'Улица',     9.8 union all
select 120, 'Сидоров', 'Переулок',  7.7
-- end of test data

select *
  from @t as t1
 where not exists (select 'не важно что'
                     from @t as t2
                    where t2.FAMILY = t1.FAMILY
                      and t2.DISTANCE < t1.DISTANCE)

BRID        FAMILY           ADRESS           DISTANCE
----------- ---------------- ---------------- ---------------------------------------
119         Иванов           Улица            9.8
120         Сидоров          Переулок         7.7

(2 row(s) affected)
28 авг 09, 12:50    [7590263]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
`
Гений сыска
...

там эта - у каждой записи адрес разный!!!


и??


ну и должно быть для каждой фамилии только один адрес сопоставлен который с минимальным расстоянием
28 авг 09, 12:51    [7590269]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
`
Guest
Гений сыска
`
Гений сыска
...

там эта - у каждой записи адрес разный!!!


и??


ну и должно быть для каждой фамилии только один адрес сопоставлен который с минимальным расстоянием


... а кто ж спорит - канешна адин
28 авг 09, 12:52    [7590275]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Гений сыска
ну и должно быть для каждой фамилии только один адрес сопоставлен который с минимальным расстоянием
так оно так и есть
28 авг 09, 12:52    [7590276]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А если так?
Паганель
-- test data
declare @t table(BRID int, FAMILY varchar(16), ADRESS varchar(16), DISTANCE decimal(7, 1))
insert into @t(BRID, FAMILY, ADRESS, DISTANCE)
select 119, 'Иванов',  'Проспект', 10.2 union all
select 119, 'Иванов',  'Улица',     9.8 union all
select 119, 'Иванов',  'Тупик',     9.8 union all
select 120, 'Сидоров', 'Переулок',  7.7
-- end of test data

select *
  from @t as t1
 where not exists (select 'не важно что'
                     from @t as t2
                    where t2.FAMILY = t1.FAMILY
                      and t2.DISTANCE < t1.DISTANCE)

BRID        FAMILY           ADRESS           DISTANCE
----------- ---------------- ---------------- ---------------------------------------
119         Иванов           Улица            9.8
119         Иванов           Тупик            9.8
120         Сидоров          Переулок         7.7

(2 row(s) affected)
28 авг 09, 12:53    [7590281]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
select
	a.BRID
	, a.FAMILY
	, a.ADRESS
	, a.DISTANCE
from [Есть таблица вида] a
inner join
		(
		select
			BRID
			, FAMILY
			, min(DISTANCE) as DISTANCE
		from [Есть таблица вида]
		group by BRID
			, FAMILY
		) b on a.BRID = b.BRID
				and a.FAMILY = b.FAMILY
				and a.DISTANCE = b.DISTANCE
28 авг 09, 12:53    [7590285]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
iap
А если так?
А если так, то как всегда ждем от автора уточнений -
кто не должен попасть в результат - улица или тупик
и почему именно улица или именно тупик
28 авг 09, 12:55    [7590296]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
тада так
Guest
iap
А если так?

SELECT a.BRID, a.FAMILY, MIN(a.ADRESS), a.DISTANCE
FROM [Есть таблица вида] a
WHERE a.DISTANCE = (SELECT MIN(b.DISTANCE) FROM [Есть таблица вида] b WHERE a.FAMILY=b.FAMILY)
GROUP BY a.BRID, a.FAMILY, a.DISTANCE
так например
хотя ТС - виднее
28 авг 09, 12:56    [7590305]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
Паганель
iap
А если так?
А если так, то как всегда ждем от автора уточнений -
кто не должен попасть в результат - улица или тупик
и почему именно улица или именно тупик


должно выбраться только одно значения для каждой фамилии. неважно улица или тупик.главное с минимальным расстоянием
28 авг 09, 12:57    [7590315]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Гений сыска
неважно улица или тупик
-- test data
declare @t table(BRID int, FAMILY varchar(16), ADRESS varchar(16), DISTANCE decimal(7, 1))
insert into @t(BRID, FAMILY, ADRESS, DISTANCE)
select 119, 'Иванов',  'Проспект', 10.2 union all
select 119, 'Иванов',  'Улица',     9.8 union all
select 119, 'Иванов',  'Тупик',     9.8 union all
select 120, 'Сидоров', 'Переулок',  7.7
-- end of test data

select *
  from @t as t1
 where not exists (select 'не важно что'
                     from @t as t2
                    where t2.FAMILY = t1.FAMILY
                      and (    t2.DISTANCE < t1.DISTANCE 
                            or t2.DISTANCE = t1.DISTANCE and t2.ADRESS < t1.ADRESS
                           )
                   )

BRID        FAMILY           ADRESS           DISTANCE
----------- ---------------- ---------------- ---------------------------------------
119         Иванов           Тупик            9.8
120         Сидоров          Переулок         7.7

(2 row(s) affected)
28 авг 09, 12:59    [7590331]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
Паганель
Гений сыска
неважно улица или тупик
-- test data
declare @t table(BRID int, FAMILY varchar(16), ADRESS varchar(16), DISTANCE decimal(7, 1))
insert into @t(BRID, FAMILY, ADRESS, DISTANCE)
select 119, 'Иванов',  'Проспект', 10.2 union all
select 119, 'Иванов',  'Улица',     9.8 union all
select 119, 'Иванов',  'Тупик',     9.8 union all
select 120, 'Сидоров', 'Переулок',  7.7
-- end of test data

select *
  from @t as t1
 where not exists (select 'не важно что'
                     from @t as t2
                    where t2.FAMILY = t1.FAMILY
                      and (    t2.DISTANCE < t1.DISTANCE 
                            or t2.DISTANCE = t1.DISTANCE and t2.ADRESS < t1.ADRESS
                           )
                   )

BRID        FAMILY           ADRESS           DISTANCE
----------- ---------------- ---------------- ---------------------------------------
119         Иванов           Тупик            9.8
120         Сидоров          Переулок         7.7

(2 row(s) affected)


Задумался...
28 авг 09, 13:03    [7590364]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
Гений сыска
Паганель
Гений сыска
неважно улица или тупик
-- test data
declare @t table(BRID int, FAMILY varchar(16), ADRESS varchar(16), DISTANCE decimal(7, 1))
insert into @t(BRID, FAMILY, ADRESS, DISTANCE)
select 119, 'Иванов',  'Проспект', 10.2 union all
select 119, 'Иванов',  'Улица',     9.8 union all
select 119, 'Иванов',  'Тупик',     9.8 union all
select 120, 'Сидоров', 'Переулок',  7.7
-- end of test data

select *
  from @t as t1
 where not exists (select 'не важно что'
                     from @t as t2
                    where t2.FAMILY = t1.FAMILY
                      and (    t2.DISTANCE < t1.DISTANCE 
                            or t2.DISTANCE = t1.DISTANCE and t2.ADRESS < t1.ADRESS
                           )
                   )

BRID        FAMILY           ADRESS           DISTANCE
----------- ---------------- ---------------- ---------------------------------------
119         Иванов           Тупик            9.8
120         Сидоров          Переулок         7.7

(2 row(s) affected)


Задумался...


а если в первоначальной таблице не будет адреса а только фамилии и расстояния, то тогда как выбрать записи с минимальными расстояниями и туда добавить адреса?
28 авг 09, 13:11    [7590429]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Гений сыска
в первоначальной таблице не будет адреса а только фамилии и расстояния
Пример данных покажите, пожалуйста
28 авг 09, 13:12    [7590437]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
Паганель
Гений сыска
в первоначальной таблице не будет адреса а только фамилии и расстояния
Пример данных покажите, пожалуйста


119 Иванов 10.2
119 Иванов 9.8
120 Сидоров 7.7

но в таком случае эта таблица берет значения уже на основании расчетов по адресам

т.е. есть таблица адресов там Адрес, координаты (поля lat1,lon1)
и таблица машин там номер, фамилия, координаты(поля lat2,lon2)


расстояние между координатами вычисляется по формуле
CAST(6372795*2*ATN2(Sqrt(Power(Sin(ABS(cast(Cast(Lat as numeric(20,12))*(3.14159265358979/180) as numeric(20,12))-CAST(Cast(Lat1 as numeric(20,12))*(3.14159265358979/180) as numeric(20,12)))/2),2)+(Cos(cast(Cast(Lat as numeric(20,12))*(3.14159265358979/180) as numeric(20,12))*cast(Cast(Lat1 as numeric(20,12))*(3.14159265358979/180) as numeric(20,12)))*Power(Sin(ABS(cast(Cast(Lon as numeric(20,12))*(3.14159265358979/180) as numeric(20,12))-CAST(Cast(Lon1 as numeric(20,12))*(3.14159265358979/180) as numeric(20,12)))/2),2))),SQRT(1-Power(Sin(ABS(cast(Cast(Lat as numeric(20,12))*(3.14159265358979/180) as numeric(20,12))-CAST(Cast(Lat1 as numeric(20,12))*(3.14159265358979/180) as numeric(20,12)))/2),2)+(Cos(cast(Cast(Lat as numeric(20,12))*(3.14159265358979/180) as numeric(20,12))*cast(Cast(Lat1 as numeric(20,12))*(3.14159265358979/180) as numeric(20,12)))*Power(Sin(ABS(cast(Cast(Lon as numeric(20,12))*(3.14159265358979/180) as numeric(20,12))-CAST(Cast(Lon1 as numeric(20,12))*(3.14159265358979/180) as numeric(20,12)))/2),2)))) as NUMERIC(10,2)) as DISTANCE

где lat1,lon1 -координаты машины, lat2,lon2 -координаты адреса

по прежнему нужен результирующий набор типа машина, фамилия, адрес, расстояние
28 авг 09, 13:19    [7590491]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
`
Guest
Гений сыска,

порадовали))

numeric(20,12))*(3.14159265358979/180) as numeric

PI()
28 авг 09, 13:24    [7590528]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
`
Гений сыска,

порадовали))

numeric(20,12))*(3.14159265358979/180) as numeric

PI()



да косяк. надо будет увеличить до 20,14 ))
28 авг 09, 13:26    [7590540]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
делаете выборку такого вида
insert into @[специально созданная для этого табличная переменная]
select М.номер, М.фамилия, A.Адрес, {расстояние между координатами вычисляется по формуле}
  from [есть таблица адресов] as A 
  cross join [таблица машин] as M
а что потом делать с табличной переменной - см. выше
28 авг 09, 13:28    [7590561]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Гений сыска
Member

Откуда: Уфа
Сообщений: 185
попробовал ваши варианты - теперь такая тема - в результирующий запрос не попадают те машины для которых не найдено соответствие в адресной базе, а мне нужен результат как будто left outer join - те для кого соответствие найдено должны показать единственное минимальное расстояние, те для кого нет - пустое поле расстояния или 0.
30 авг 09, 21:50    [7595363]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать по минимальным значениям  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
я ничего не понял, какое еще соответствие

cross join, который я предложил выше, дает комбинации всех машин со всеми адресами
на то он и cross join

при такоем подходе ну просто физически не может "не найтись соответствие адреса"
31 авг 09, 09:48    [7595992]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить