Помогите составить SQL-запрос!

[FleshDro]

Есть таблица Table1
fam - фамилия
im - имя
ot - отчество
dr - день рождения
ad - адрес

Нужно найти в таблице работающих людей у которых одинаковые ФИО.
В итоговой таблице структура должна быть такой же.

[HandKot]

вариант 1

Код:

select
  fam as [фамилия],
  im as [имя],
  ot as [отчество],
  dr as [день рождения],
  ad as [адрес]
from
  Table1 t
where
  (select count(*) from Mytable t1 where t1.fam = t.fam and t1.im = t.im and t1.ot = t.ot) > 1

вариант2

Код:

select
  fam as [фамилия],
  im as [имя],
  ot as [отчество],
  dr as [день рождения],
  ad as [адрес]
from
  Table1 t
inner join (select fam, im, ot from Table1 group by fam, im, ot having count(*) > 1) t1 on t1.fam = t.fam and t1.im = t.im and t1.ot = t.ot

скорость выполнения зависит от наличия ключей
я обычно использую второй вариант

[FleshDro]

таблица dbf. соединяюсь через BDE
да и найти нужно всех однофамильцев

[McZim]

HandKot, count(*) - не нужно так делать никогда (при условии что есть другой вариант поиска). Привыкнешь, а потом будет это у тебя висеть в коде.

[Dimka]

count(*) - не нужно так делать никогда.

Обоснуй.

[x77]

Dimka, на некоторых СУБД count (*) и count (123) дадут разные планы выполнения.

[Sla]

я никак не мог mczimу возразить, но сейчас вот только-только

ну думаю, вот посоветовал "правильный" пацан, а ну-ка

select count(account) from table
-------
12

select count(*) from table
----
13

Null не посчитало.

[Sla]

И... не помню где, но проскакивало такое
что count(*) быстрее, но это видимо связано с тем, что не нужно делать никаких анализов

[x77]

Null не посчитало.

и не должно было.

[Dimka]

x77, дело не только в планах выполнения, дело гораздо глубже.

Код: (Text)

CREATE DATABASE Test
GO

USE Test
GO

CREATE TABLE Test
(
        X INT NULL
)
GO

INSERT Test (X) VALUES (NULL)
INSERT Test (X) VALUES (0)
INSERT Test (X) VALUES (0)
GO

SELECT
        COUNT(X) AS CountAll,
        COUNT(DISTINCT X) AS CountDistinct,
        COUNT(*) AS CountAsterisk
FROM Test

USE Master
GO

DROP DATABASE Test
GO

Результат:

Код:

CountAll CountDistinct CountAsterisk
-------------------------------------
2        1             3

Выводы:
1) COUNT(X) по полю X выдаёт количество непустых значений поля.
2) COUNT(DISTINCT X) по полю X выдаёт количество непустых разных значений поля.
3) COUNT(*) выдаёт количество записей в выборке.

Так что утверждение:

не нужно так делать никогда

считаю неправильным. COUNT(*) - это самостоятельная по смыслу функция, а не разновидность COUNT(X).

[McZim]

Dimka, я только с точки зрения Oracle. COUNT(поле), количество записей для этого поля, COUNT(*), количество записей для все таблицы, не взирая на пустые поля. Для того что бы понять всю глубину проблемы count(*) на продакшн базе, предлагаю проанализировать расширенные файлы трассировок, и посмотреть время отклика системы.

[x77]

COUNT(*) - это самостоятельная по смыслу функция, а не разновидность COUNT(X).

далась вам это звёздочка. count (X) ищет кол-во непустых значений в поле X. если поля Х нет - он вернёт количество записей. вместо count (*) можно писать count ('Солны идут на север!').

просто стандарт де-факто - использовать звёздочку, потому что в подавляющем большинстве СУБД нельзя завести поле с именем "*". но нигде, ни в одном регламентирующем документе, не сказано, что для подсчёта кол-ва записей можно использовать ТОЛЬКО здёздочку. да всё что угодно можно, если вы уверены, что это не совпадает с именем поля.

просто в некоторых случаях подсчёт кол-ва записей по count (ключевое_поле) даёт иной план выполнения запроса, чем count (*), т.е. общий подсчёт полей.

[Dimka]

что для подсчёта кол-ва записей можно использовать ТОЛЬКО здёздочку. да всё что угодно можно, если вы уверены, что это не совпадает с именем поля.

А я не про это говорю. Я говорю о том, что утверждение "не используй никогда count(*)" - неправильное утверждение, поэтому что count(<не поле>) - самостоятельная функция.

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

[McZim]

Но так же не стоит забывать о партицировании, индексировании, таблиц.

[McZim]

Dimka, посмотри, я поправил свое утверждение. В ответе №3

[x77]

Dimka, я тоже стараюсь не использовать count (*).

две таблицы, customers & orders, связь один ко многим по customerid. требуется посчитать договора с ненулевыми кастомерами.

Код:

select count (*)
 from orders JOIN customers
      on (orders.customerid = customers.customerid)

даёт правильное значение, 7398. меняем условие запроса:

Код:

select count (*)
 from orders LEFT JOIN customers
      on (orders.customerid = customers.customerid)

получаем 7430 - неправильный ответ, потому что посчитались пустые кастомеры.

а можно (и нужно) использовать count именно по тому полю, которое мы считаем, в данном случае - нас интересует customerid:

Код:

select count (orders.customerid)
 from orders LEFT JOIN customers
      on (orders.customerid = customers.customerid)

внутренний там будет запрос, или внешний - результат будет правильным всегда. поэтому я стараюсь избегать "всеобщей переписи". для каждой задачи почти всегда можно формализовать, что конкретно мы считаем, и считать count-ом надо именно это. имхо.

"никогда" - это, конечно, слишком сильно сказано. never say never. но в большинстве случаев лучше обходиться без обобщений (здвёздочек)

[x77]

З.Ы.

 пример примитивный, конечно. но. именно на этом примере прекрасно видно, почему выгоднее использовать count (поле), чем count (*). чтобы звёздочкой посчитать кол-во договоров, заключённых с ненулевыми покупателями, мне действительно придётся делать жёсткий джойн по id полкупателя и считать, что получится. а когда мы используем конкретное поле - join вообще становится не нужен, правильный результат можно получить просто через

Код:

select count (orders.customerid)
  from orders

.

[x77]

З.З.Ы. для интереса, проверил то, что понаписал на FB 1.5.3. count (поле) работает медленнее, чем count (*) .. where поле is not null.

[McZim]

сделал сейчас расширенный трассировачный файл, вот краткие результаты:

count(*)
общее процессорное время, потраченное процессом Oracle в хоже вызова, микросекунды: 18997
фактическая продолжительность вызова, микросекунды: 43947

count(id)
общее процессорное время, потраченное процессом Oracle в хоже вызова, микросекунды: 18997
фактическая продолжительность вызова, микросекунды: 22202

[x77]

а это не кэш?

[McZim]

x77, чтение из кэша или с диска я не приводил, это общее время проца и фактическая продолжительность вызова.

[Dimka]

меняем условие запроса

Что значит меняем? outer join и inner join - разные типы связывания, употреблять надо правильный для задачи. outer join нужен, чтобы не исключать из запроса те записи, для которых не нашлось соответствия. По условию твоей задачи в выборке не должно быть таких записей - применение outer join необосновано.

За уши притянутый пример

пример примитивный, конечно. но. именно на этом примере прекрасно видно, почему выгоднее использовать count (поле), чем count (*). чтобы звёздочкой посчитать кол-во договоров, заключённых с ненулевыми покупателями, мне действительно придётся делать жёсткий джойн по id полкупателя и считать, что получится. а когда мы используем конкретное поле - join вообще становится не нужен, правильный результат можно получить просто через

По большому счёту твоя рекомендация не исползовать count(*) весьма похожа на рекомендацию писать

Код: (C)

if(1 == x) ...

вместо

Код: (C)

if(x == 1) ...

Защита от дурака, который пишет код наспех или не знает языка, на котором пишет. По этому поводу давно ещё была где-то тема, ещё Alf был на форуме. Если язык слишком "мощен" для его использующего программиста - программисту стоит расти над собой

[x77]

McZim, я с ораклом почти не работал, поэтому не в курсе тонкостей. имелось в виду, не может ли второй запрос выполняться быстрее только за счёт того, что до этого выполнялся аналогичный первый запрос, возвращающий такой же результат?

[x77]

Dimka, пример примитивный, никто не спорит. а насчёт рекомендации... она сводилась к тому, чтобы при подсчёте количества там, где возможно считать по конкретному полю. при подсчёте общего кол-ва записей по таблице - по ключевому полю. это не совсем х=1 <-> 1 = х, тут, скорее, твои сравнения притянуты за уши

[McZim]

x77, сейчас выполнил наоборот, процессорное время по нулям у обоих, а фактическое выполнение запроса примерно так же разница в два раза: 0 против 21296

да возможно не удачные показатели для того что бы более полно ответить нужно время на рагребание тарссировки, времени нет ((

[x77]

McZim, странно, что такой явный разрыв. на FB count (*) и count (id) выполненные раз по 10 дадут одинаковое среднее время выполнения.

[HandKot]

в MSSQL все три запроса вернули один и тотже план

Код:

select count(*) from Table with (nolock)
select count(1) from Table with (nolock)
select count(field1) from Table with (nolock)

и насколько я пониаю, что если планы одинаковые, то и выполняется тоже одинаково
к тоу же план для запросов первого и второго идентичны
count(1) был заеннен на count(*) (самим серверов при компиляции запроса)

[FleshDro]

Спасибо всем за помощь(