Как вытащить отдельные строки на основе комбинации нескольких столбцов таблицы

Извините за заголовок вопроса, но у меня есть таблица CUSTOMER (ACCT_NUM, LRN, NAME, ADDRESS, CITY, STATE, COUNTRY)

Мне нужно написать запрос для извлечения записей, которые содержат более 1 (различной) комбинации имени, адреса, города, штата и страны для одной и той же комбинации acct_num и LRN. Это то, что я пробовал, но я не уверен, что это правильно. Я просто хочу сгруппировать по acct_num и LRN, но я знаю, что группировка по не позволит мне исключить другие столбцы.

select distinct name, address, state, country, city, 
COUNT(1) from CUSTOMER 
group by acct_num,
 LRN ,
 name,
 address,
 state, 
 country, 
 city
 having COUNT(1) > 1

Пожалуйста помоги.


sql sql-server count group-by distinct
person Reeya Oberoi    schedule 22.07.2014    source источник

Ответы (5)


arrow_upward
2
arrow_downward

Создание некоторых тестовых данных

DECLARE @Customer TABLE
(
    ACCT_NUM INT,
    LRN INT,
    name varchar(20),
    address varchar(100),
    state varchar(2),
    country varchar(100),
    city varchar(100)
)

INSERT INTO @Customer
VALUES
( 1, 1, 'Test1', 'Addr1', 'FL', 'USA', 'Tampa' ),
( 1, 1, 'Test1', 'Addr2', 'FL', 'USA', 'Tampa' ),
( 1, 1, 'Test1', 'Addr3', 'FL', 'USA', 'Tampa' ),
( 1, 1, 'Test1', 'Addr4', 'FL', 'USA', 'Tampa' ),
( 2, 1, 'Test2', 'Addr1', 'FL', 'USA', 'Tampa' ),
( 2, 1, 'Test2', 'Addr1', 'FL', 'USA', 'Tampa' ),
( 3, 1, 'Test3', 'Addr1', 'FL', 'USA', 'Tampa' )

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

 SELECT * FROM
 (
    SELECT *, 
        Rank() OVER (PARTITION BY c.ACCT_NUM, c.LRN ORDER BY c.Name, c.Address, c.State, c.Country, c.City) RK
    FROM @Customer c
) d
WHERE d.RK > 1

Выход:

ACCT_NUM    LRN name    address state   country city    RK
1   1   Test1   Addr2   FL  USA Tampa   2
1   1   Test1   Addr3   FL  USA Tampa   3
1   1   Test1   Addr4   FL  USA Tampa   4
person Kevin Cook    schedule 22.07.2014
comment
Какой замечательный ответ @Kevin! Только один вопрос: что, если в приведенный выше набор результатов я хочу включить еще одну дополнительную строку 1, 1, «Test1», «Addr1», «FL», «USA», «Tampa». Как это сделать? Если я уберу условие d.RK › 1, он вернет все строки. - person Reeya Oberoi; 22.07.2014
comment
вы можете использовать вывод и присоединиться к основной таблице, используя acct_num, lrn, а затем получить все строки для acct. - person Kevin Cook; 22.07.2014

arrow_upward
2
arrow_downward

Ответ RANK() OVER правильный, но не показывает первый адрес в результатах.

Я бы предпочел использовать дополнительный уровень вложенности для достижения этой цели.

SELECT
  *
FROM
(
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY acct_num, LRN) AS distinct_matches
  FROM
  (
    SELECT
      acct_num, LRN, name, address, state, country, city
    FROM
      CUSTOMER
    GROUP BY
      acct_num, LRN, name, address, state, country, city
  )
    AS unique_rows
)
  AS counted_unique_rows
WHERE
  distinct_matches > 1
;
person MatBailie    schedule 22.07.2014
comment
Большое спасибо @MatBailie. Вы правы, приведенный выше ответ правильный, и ваш ответ также включает эту недостающую строку. Я не знаю, какой ответ принять :( Большое спасибо за помощь. - person Reeya Oberoi; 22.07.2014
comment
@ReeyaOberoi - какой бы ответ вы не нашли наиболее полезным для себя. Словом, как вам больше нравится :) - person MatBailie; 23.07.2014

arrow_upward
0
arrow_downward 

SELECT distinct name, address, state, country, city, acct_num
FROM (
    SELECT  COUNT(*) OVER (PARTITION BY name, address, state, country, city) AS RN
    FROM CUSTOMER 
    group by acct_num,
             LRN ,
             name,
             address,
             state, 
             country, 
             city) AS n
WHERE RN > 1

Может быть, это работает !!! согласно моему предположению.

person mohan111    schedule 22.07.2014
comment
Это противоположно тому, что запросил ОП. Это возвращает acct_num, LRN комбинаций, которые совместно используют один и тот же name, address, state, country, city хотя бы с одним другим acct_num, LRN. - person MatBailie; 22.07.2014
comment
@ mohan111 - спасибо за помощь, но я чувствую, что вы упустили возможность включить имя, адрес, штат, страну, город в свой внутренний запрос, и даже если я добавлю его, я чувствую, что это может дать результаты, аналогичные тем, что я пробовал, хотя в немного более сложный способ. - person Reeya Oberoi; 22.07.2014

arrow_upward
0
arrow_downward

Это вернет комбинации acct_num и LRN с более чем одной комбинацией других полей. Если вы хотите увидеть всю другую информацию, которая отличается, это как бы противоречит цели агрегатов IMO.

select acct_num, LRN, COUNT(*)
from CUSTOMER
group by acct_num, LRN
having COUNT(*) > 1
person How 'bout a Fresca    schedule 22.07.2014
comment
Только если для всех полей установлено уникальное ограничение. Что, если одна комбинация acct_num, LRN содержит одинаковые name, address, city, state, country в нескольких строках? - person MatBailie; 22.07.2014

arrow_upward
0
arrow_downward

Я сделал это так.

Вот ссылка на SQL Fiddle, заимствовавшая пример набора данных из ответа Кевина, чтобы вы могли увидеть результаты. .

Получить список отдельных клиентов. Из этого списка сгруппируйте по acct_num и lrn. Любое количество, имеющее значение > 1, означает наличие более 1 комбинации с учетом остальных полей. Теперь извлеките его из клиента, где он существует, из предыдущего запроса, чтобы получить все данные.

select distinct * from customer c
where exists
  (
    select acct_num, lrn
    from
      (
        select distinct * from customer
       ) base
    where c.acct_num = base.acct_num and c.lrn = base.lrn
    group by acct_num, lrn
    having count(1) > 1
  )
person SQLChao    schedule 22.07.2014