Блог

Главная - Вопросы по программированию - Tsql, возвращающий строки с одинаковыми значениями столбцов

Tsql, возвращающий строки с одинаковыми значениями столбцов

#tsql

#tsql

Вопрос:

Учитывая пример таблицы ‘Users’, в которой есть столбец int с именем ‘userId’ (и некоторое произвольное количество других столбцов), каков наилучший способ выбрать все строки, в которых userId появляется более одного раза?

До сих пор я придумывал 

 select * from Users where UserID in 
(select UserID from Users group by UserID having COUNT(UserID) > 1)

Это кажется довольно неэффективным способом сделать это, хотя, есть ли способ получше?

Комментарии:

1. В каком смысле неэффективно? У вас миллионы пользователей или что? Я думаю, что ваш запрос просто прекрасен; если у вас миллионы строк, то, конечно, идентификатор пользователя должен быть проиндексирован для этого типа запроса.

2. Я интуитивно чувствую, что должен быть какой-то способ сделать это без использования подзапроса. Конечно, я могу быть совершенно неправ.

3. Ближе к делу: почему идентификатор пользователя «ever» должен появляться более одного раза в таблице Users?

4. хотя вы можете переписать запрос, используя EXISTS() или JOIN , результирующий план запроса, скорее всего, будет точно таким же.

Ответ №1:

В SQL Server 2005 вы могли бы использовать этот подход:

 ;WITH UsersNumbered AS (
  SELECT
    UserID,
    rownum = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UserID)
  FROM Users
)
SELECT u.*
FROM Users u
  INNER JOIN UsersNumbered n ON u.UserID = n.UserID AND n.rownum = 2

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

 ;WITH UsersNumbered AS (
  SELECT
    UserID,
    rownum = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UserID)
  FROM Users
)
SELECT u.*
FROM Users u
WHERE EXISTS (
  SELECT *
  FROM UsersNumbered n
  WHERE u.UserID = n.UserID AND n.rownum = 2
);

В случае кластеризованного индекса на UserID все три решения выдают один и тот же план.

Ответ №2:

Это сделало бы то же самое, но оценило бы производительность, и это, вероятно, было бы быстрее / эффективнее. Конечно, в этом столбце userId должен быть индекс.

 select u.*
from Users u
join (select UserID,count(UserID) as CUserID from Users group by UserID) u1 on u1.UserID = u.UserID
where CUserID > 1