SamForum.org  
SamLab.ws
Заблокированные пользователи

Вернуться   SamForum.org > Программирование > Базы данных
Важная информация

Ответ
 
Опции темы Опции просмотра
выборка в SQL Server
Старый Добавлено: 31.08.2010, 15:44
  (#1)
virtuOS
Модератор
Модератор
 
Аватар для virtuOS

По умолчанию выборка в SQL Server

Имеется база данных кодов городов: название города, область, регион и сам код (без восьмерки), коды от 3 до 5 символов. например, код Москвы 495, Костромы 4942.
Есть база данных с телефонными номерами (10-ти значный формат, с восьмеркой). Требуется в эту базу добавить местоположение: город, область, регион.

Делаю такой запрос:
Код:
use Stroika;
SELECT [Номер телефона]
     
             ,[locality]
	  ,[region]
	  ,[okrug]
             ,[code1] 

  FROM [приволжский], [Codes]
 
  WHERE [code1] = substring([Номер телефона], 2, len([code1]))
Запрос отрабатывается некорректно, т.к. в результат один номер может попасть несколько раз.
Пример:
Код:
locality	        region	      okrug	                    code
УЛАН-УДЕ	Бурятия 	Сибирский федеральный округ	   301
Улан-Удэ	Бурятия	           Сибирский федеральный округ	   3012
Номер 8301255555 (например) выбирается два раза, т.к. соответствует двум кодам. Кроме того, номер может быть несколько раз в базе, т.к. другие поля в этой базе разные (например, время звонка).

Как исключить повторы? Через Distinct не получается.


"Умом Россию не понять, аршином общим не измерить: у ней особенная стать - в Россию можно только верить."©
Волхвы не боятся могучих владык, а княжеский дар им не нужен; правдив и свободен их вещий язык и с волей небесною дружен. Грядущие годы таятся во мгле; но вижу твой жребий на светлом челе. ©
virtuOS вне форума Отправить личное сообщение для virtuOS
Вверх
Ответить с цитированием
Старый Добавлено: 09.09.2010, 18:01
  (#2)
L.E.O.
Пользователь
Пользователь
 
Аватар для L.E.O.

По умолчанию

Чтобы избавиться от дубликатов, можно использовать "Group By". Был еще какой-то способ (этот способ автоматически избавляется от дубликатов), но давно не притрагивался к SQL, поэтому забыл
Код:
use Stroika;
SELECT [Номер телефона], [locality], [region], [okrug], [code1]
FROM [приволжский], [Codes]
WHERE [code1] = substring([Номер телефона], 2, len([code1]))
GROUP BY [Номер телефона], [locality], [region], [okrug], [code1]
Если в базе города хранятся с разным регистром, то можно использовать функцию "UPPER"
Код:
use Stroika;
SELECT [Номер телефона], UPPER([locality]), UPPER([region]), UPPER([okrug]), [code1]
FROM [приволжский], [Codes]
WHERE [code1] = substring([Номер телефона], 2, len([code1]))
GROUP BY [Номер телефона], [locality], [region], [okrug], [code1]
В твоем примере я что-то не разобрался. Во первых, нет поля "Номер телефона". Во-вторых, почему-то у одного и того же города разный код города. А это значит, что база испорчена (по понятным причинам).

PS: я не проверял запросы на практике. Если возникнут проблемы, сообщи.


Помог, скажи спасибо
L.E.O. вне форума Отправить личное сообщение для L.E.O.
Вверх
Ответить с цитированием
Старый Добавлено: 09.09.2010, 18:53
  (#3)
virtuOS
Модератор
Модератор
 
Аватар для virtuOS

По умолчанию

L.E.O.,
Попробую написать пример более подробно.
База №1 [уральский]:
Код:
SELECT [Номер телефона]
      ,[Дата последней попытки]
      ,[Время последней попытки]
      ,[Кол-во попыток]
      ,[Кол-во контактов]
      ,[Кол-во успешных контактов]
      ,[Макс длительность контакта]
  FROM [уральский]
База №2 [Codes]:
Код:
SELECT [id]
      ,[locality]
      ,[region]
      ,[okrug]
      ,[code1]
      ,[utc]
  FROM [Codes]
При этом во второй базе есть такие записи:
Код:
locality    region               okrug                             code1        utc
УЛАН-УДЕ	Бурятия 	Сибирский федеральный округ	   301          8
Улан-Удэ	Бурятия	           Сибирский федеральный округ	   3012        8
Обрати внимание на последнюю букву. Это не ошибка базы, это разные города.

Я выполнил такой запрос:

Код:
use Stroika;
SELECT [Номер телефона]
      ,[Дата последней попытки]
      ,[Время последней попытки]
      ,[Кол-во попыток]
      ,[Кол-во контактов]
      ,[Кол-во успешных контактов]
      ,[Макс длительность контакта]
      ,[locality]
      ,[region]
      ,[okrug]
      ,[code1]

  FROM [уральский], [Codes]
 
  WHERE substring([Номер телефона],2,len([code1])) = [code1]
  GROUP BY [Номер телефона], [Дата последней попытки], [Время последней попытки], 
[Кол-во попыток], [Кол-во контактов], [Кол-во успешных контактов], [Макс длительность контакта], 
[locality], [region], [okrug], [code1]
В базе [уральский] 72437 записей. Результат выполнения запроса 129567 строк. А должно быть столько же, т.е. 72437. Причина, как мне кажется - дублирование записей, пример которых привел выше: телефон города Улан-Удэ (83012******) попадает под выборку с кодом 301 и 3012. Таких телефонов много - результат почти двухкратное увеличение числа записей.


"Умом Россию не понять, аршином общим не измерить: у ней особенная стать - в Россию можно только верить."©
Волхвы не боятся могучих владык, а княжеский дар им не нужен; правдив и свободен их вещий язык и с волей небесною дружен. Грядущие годы таятся во мгле; но вижу твой жребий на светлом челе. ©

Последний раз редактировалось virtuOS; 09.09.2010 в 20:53..
virtuOS вне форума Отправить личное сообщение для virtuOS
Вверх
Ответить с цитированием
Старый Добавлено: 10.09.2010, 10:17
  (#4)
L.E.O.
Пользователь
Пользователь
 
Аватар для L.E.O.

По умолчанию

virtuOS, да, не заметил, что это два разных города. Как-то ты пример подобрал ни такой. На глаз плохо воспринимается Ну да ладно.

Вот решение задачи. Сразу же отмечаю работоспособность. Он работает только под твое условие, а именно:
Цитата:
Сообщение от virtuOS Посмотреть сообщение
Имеется база данных кодов городов: название города, область, регион и сам код (без восьмерки), коды от 3 до 5 символов

Цитата:
Сообщение от virtuOS Посмотреть сообщение
Есть база данных с телефонными номерами (10-ти значный формат, с восьмеркой)

ВАЖНО!!!: всю дополнительную информацию о номере телефона (такие как [Дата последней попытки], [Кол-во попыток] и т.д. и т.п.) я запихал в [info]. Просто так меньше писанины. И этот код я тестил на своей базе со своими названиями полей, поэтому могут быть опечатки в коде, где названия. А названия были след.:
[Номер телефона] = [number]
[уральский] = [Ural]
[Дата последней попытки], [Кол-во попыток], ... = [info]
Все остальное оставил также

Чисто на SQL Server 2008::

Код:
use Stroika;
SELECT [Номер телефона], [info], [locality], [region], [okrug], [code1]
FROM [уральский] u, [Codes] c
WHERE (SUBSTRING([Номер телефона], 2, LEN([code1])) = [code1]) AND NOT EXISTS
   (SELECT 1
    FROM [Codes] cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 1) = cc.[code1]) AND NOT EXISTS
    (SELECT 1
    FROM [Codes] cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 2) = cc.[code1])
В этом случае могут не оказаться те номера телефонов, у которых нет записи в базе [Codes]. То есть, если в базе [уральский] 72437 записей, то данный запрос вернет не более 72437 записей. Для предотвращения такого, используем LEFT JOIN
Код:
use Stroika;
SELECT [Номер телефона], [info], [locality], [region], [okrug], [code1]
FROM [уральский] u LEFT JOIN [Codes] c
ON (SUBSTRING([Номер телефона], 2, LEN([code1])) = [code1]) AND NOT EXISTS
   (SELECT 1
    FROM [Codes] cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 1) = cc.[code1]) AND NOT EXISTS
    (SELECT 1
    FROM [Codes] cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 2) = cc.[code1])
В этом случае, если в базе [уральский] 72437 записей, то данный запрос вернет ровно 72437 записей. Если соответствующая запись в базе [Codes] не была найдена, но эти поля заполняются NULL.


Для любителей Oracle и если не сработал код для SQL Server 2008::

Код:
use Stroika;
SELECT u.[Номер телефона], u.[info], c.[locality], c.[region], c.[okrug], c.[code1]
FROM [уральский] as u, [Codes] as c
WHERE (SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1])) = c.[code1]) AND NOT EXISTS
   (SELECT 1
    FROM [Codes] as cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 1) = cc.[code1]) AND NOT EXISTS
    (SELECT 1
    FROM [Codes] as cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 2) = cc.[code1])
В этом случае могут не оказаться те номера телефонов, у которых нет записи в базе [Codes]. То есть, если в базе [уральский] 72437 записей, то данный запрос вернет не более 72437 записей. Для предотвращения такого, используем LEFT JOIN
Код:
use Stroika;
SELECT u.[Номер телефона], u.[info], c.[locality], c.[region], c.[okrug], c.[code1]
FROM [уральский] as u LEFT OUTER JOIN [Codes] as c
ON (SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1])) = c.[code1]) AND NOT EXISTS
   (SELECT 1
    FROM [Codes] as cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 1) = cc.[code1]) AND NOT EXISTS
    (SELECT 1
    FROM [Codes] as cc
    WHERE SUBSTRING(u.[Номер телефона], 2, LEN(c.[code1]) + 2) = cc.[code1])
В этом случае, если в базе [уральский] 72437 записей, то данный запрос вернет ровно 72437 записей. Если соответствующая запись в базе [Codes] не была найдена, но эти поля заполняются NULL.


Вот и все. Если идея/что-то в коде не понятно, то обращайся.
C Уважением, L.E.O.


Помог, скажи спасибо

Последний раз редактировалось L.E.O.; 10.09.2010 в 10:21..
L.E.O. вне форума Отправить личное сообщение для L.E.O.
Вверх
Ответить с цитированием
Этот пользователь сказал cпасибо за это полезное сообщение:
virtuOS (10.09.2010)
Старый Добавлено: 10.09.2010, 14:38
  (#5)
virtuOS
Модератор
Модератор
 
Аватар для virtuOS

По умолчанию

Спасибо, всё получилось.

Идея понятна, реализация тоже. Я пытался сделать подобные вложенные запросы, но не получалось четко сформулировать логически задачу и воплотить это в коде. Не мог разобраться с представлениями и с тем, какой Join использовать.
Есть и один новый для меня прием - использование конструкции "SELECT 1"

Первый и второй код дали разный результат: во втором ряд телефонов не опознались по базе. Это немного неожиданно для меня, т.к. база телефонных кодов взята у провайдера и должна быть (по логике) полной и включать все коды городов. Как оказалось, это не так и база неполная.


"Умом Россию не понять, аршином общим не измерить: у ней особенная стать - в Россию можно только верить."©
Волхвы не боятся могучих владык, а княжеский дар им не нужен; правдив и свободен их вещий язык и с волей небесною дружен. Грядущие годы таятся во мгле; но вижу твой жребий на светлом челе. ©
virtuOS вне форума Отправить личное сообщение для virtuOS
Вверх
Ответить с цитированием
Старый Добавлено: 10.09.2010, 15:25
  (#6)
L.E.O.
Пользователь
Пользователь
 
Аватар для L.E.O.

По умолчанию

Цитата:
Сообщение от virtuOS Посмотреть сообщение
Спасибо, всё получилось.

Не за что.

Цитата:
Сообщение от virtuOS Посмотреть сообщение
Есть и один новый для меня прием - использование конструкции "SELECT 1"

Это я сообщаю на всякий случай. "SELECT 1" - такого приема и "конструкции" нет. Там конструкция "EXISTS (запрос)", который возвращает TRUE, если "запрос" вернул хотя бы одну строку таблицы и "NOT EXISTS (запрос)", который возвращает TRUE, если "запрос" вернул ни одной строки. Т.к. там учитывается только строки (и то только их наличие), а не поля, то можно указать все что угодно. Т.е. заместо "SELECT 1", можно было написать "SELECT *", "SELECT 0", "SELECT cc.[codes1]" и т.д. и т.п.

Цитата:
Сообщение от virtuOS Посмотреть сообщение
Как оказалось, это не так и база неполная.

А может быть, что там есть и сотовые номера? Из-за этого возможно их в базе провайдера и нет. Хотя скорее всего она просто не полная

PS: я указал "конструкция" в кавычках, потому что она удовлетворяет конструкции общего типа "SELECT [поле_1, поле_2, ..., поле_n] FROM [таблица_1, таблица_2, ..., таблица_n]" и поэтому является как бы "конструкцией", но как представитель "отдельного вида конструкции" (такие как INNNER JOIN, OUTER JOIN, INSERT) не является, поэтому и указал в кавычках. Это чтобы не было путаницы с терминологией.

С Уважением, L.E.O.


Помог, скажи спасибо

Последний раз редактировалось L.E.O.; 10.09.2010 в 18:23.. Причина: "телефоные" -> "сотовые"
L.E.O. вне форума Отправить личное сообщение для L.E.O.
Вверх
Ответить с цитированием
Этот пользователь сказал cпасибо за это полезное сообщение:
virtuOS (10.09.2010)
Старый Добавлено: 18.02.2012, 14:32
  (#7)
virtuOS
Модератор
Модератор
 
Аватар для virtuOS

По умолчанию

Снова столкнулся с проблемой, точнее с нехваткой знаний.

Имеется таблица, которая содержит информацию о работе пользователей. Среди прочего есть строки, отражающие время входа в систему и время выхода. Например:

user1 logon 2012-02-18 09:01:03
user2 logon 2012-02-18 09:15:12
user1 logoff 2012-02-18 10:10:01
user1 logon 2012-02-18 10:20:20
user2 logoff 2012-02-18 18:00:40
user1 logoff 2012-02-18 18:01:50

Мне надо посчитать время, проведенное каждым пользователем в системе в указанный день. Простой инструкцией SELECT не получится, т.к. надо считать разницу времени между строками, попадающих под условие LOGON/LOGOFF. Вроде бы курсоры должны помочь, но не понимаю как с ними правильно работать.


"Умом Россию не понять, аршином общим не измерить: у ней особенная стать - в Россию можно только верить."©
Волхвы не боятся могучих владык, а княжеский дар им не нужен; правдив и свободен их вещий язык и с волей небесною дружен. Грядущие годы таятся во мгле; но вижу твой жребий на светлом челе. ©
virtuOS вне форума Отправить личное сообщение для virtuOS
Вверх
Ответить с цитированием
Старый Добавлено: 18.02.2012, 14:55
  (#8)
Exodus
Заблокирован
Заблокирован
 
Аватар для Exodus

По умолчанию

virtuOS, time.h
Exodus вне форума
Вверх
Ответить с цитированием
Старый Добавлено: 18.02.2012, 15:05
  (#9)
virtuOS
Модератор
Модератор
 
Аватар для virtuOS

По умолчанию

Exodus, Microsoft SQL Server 2005 и чужая БД. Что есть, с тем и работаю.


"Умом Россию не понять, аршином общим не измерить: у ней особенная стать - в Россию можно только верить."©
Волхвы не боятся могучих владык, а княжеский дар им не нужен; правдив и свободен их вещий язык и с волей небесною дружен. Грядущие годы таятся во мгле; но вижу твой жребий на светлом челе. ©
virtuOS вне форума Отправить личное сообщение для virtuOS
Вверх
Ответить с цитированием
Ответ

Опции темы
Опции просмотра

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

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход



Powered by vBulletin® Version 3.8.10
Copyright ©2000 - 2019, vBulletin Solutions, Inc. Перевод: zCarot

Время генерации страницы 0.12167 секунды с 16 запросами