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

Откуда:
Сообщений: 554
Таблица conts состоит из двух колонок cont1, cont2.
Картинка с другого сайта.
Необходимо по ним сделать группировку, в результате которой получится одна строка, т.е. 251-250 и 250-251 это одно и тоже!
Как это сделать?
9 фев 12, 13:08    [12061428]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
aleks2
Guest
select c1, c2
FROM
(
select 
  (select MIN(x) FROM (select cont1 x UNION ALL select cont2 x) Y) c1
  ,(select MAX(x) FROM (select cont1 x UNION ALL select cont2 x) Y) c2
FROM conts
) Z
GROUP BY c1, c2
9 фев 12, 13:15    [12061515]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
Добрый Э - Эх
Guest
с завидной регулярностью подобные вопросы всплывают...
да, не хватает функций GREATEST, LEAST. :(
9 фев 12, 13:20    [12061604]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
blest
Member

Откуда:
Сообщений: 554
Спасибо
aleks2, а что это за запрос, перекрестный какой-то? Как в гугле теорию найти, чтобы просветиться?
9 фев 12, 13:42    [12061871]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Короче:
aleks2
select c1, c2
FROM
(
select 
  (select MIN(x) FROM (values(cont1),(cont2)) Y(x)) c1
 ,(select MAX(x) FROM (values(cont1),(cont2)) Y(x)) c2
FROM conts
) Z
GROUP BY c1, c2
9 фев 12, 13:47    [12061947]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Ну и еще короче:
select
 c1, c2
from
 conts c cross apply
 (select min(v), max(v) from (values (c.cont1), (c.cont2)) t(v)) x(c1, c2)
group by
 c1, c2;
9 фев 12, 14:10    [12062213]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
fff_fff
Guest
blest
Спасибо
aleks2, а что это за запрос, перекрестный какой-то? Как в гугле теорию найти, чтобы просветиться?


http://beyondrelational.com/blogs/madhivanan/archive/2010/08/02/values-clause-in-sql-server-2008.aspx
9 фев 12, 14:26    [12062375]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
step_ks
Member

Откуда:
Сообщений: 936
fff_fff, да он не про values
9 фев 12, 14:28    [12062394]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
fff_fff
Guest
step_ks,
про что тогда, про UNION?
9 фев 12, 14:32    [12062469]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
step_ks
Member

Откуда:
Сообщений: 936
fff_fff, слово автору. По крайней мере, поста с values на тот момент еще не было.
9 фев 12, 14:35    [12062515]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
fff_fff
step_ks,
про что тогда, про UNION?

А вы в этом UNION ничего необычного не видите? Я, например, тоже такой синтаксис впервые вижу))). Но прикольно.
9 фев 12, 14:36    [12062547]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
blest
Member

Откуда:
Сообщений: 554
Вообще изначально про UNION спрашивал :)
Про values тоже синтаксис не знал
9 фев 12, 14:40    [12062593]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
kDnZP
fff_fff
step_ks,
про что тогда, про UNION?

А вы в этом UNION ничего необычного не видите? Я, например, тоже такой синтаксис впервые вижу))). Но прикольно.

гг))), проверил на 2000 серваке, работает наура.
aleks2, признавайтесь, откель раскопали? Интересно поглядеть и почитать.
9 фев 12, 14:41    [12062599]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
step_ks
Member

Откуда:
Сообщений: 936
kDnZP, да вроде не особо секретная вещь же
9 фев 12, 14:45    [12062675]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
step_ks
kDnZP, да вроде не особо секретная вещь же

Дык я щаслив, что вы сто лет как знали про такой синтаксис. Я нет. Где почитать?
9 фев 12, 14:48    [12062706]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
_nobody_
Guest
blest,

зависит от того, чего вы хотите добится группировкой.
Если просто найти уникальные пары.
можно например так:
SELECT DISTINCT
 ContId1,
 ContId2
FROM T
UNION
SELECT DISTINCT
 ContId2,
 ContId1
FROM T

Но если например есть еще поле Amount, по которому нужно суммировать, то лучше так:
SELECT
 ContId1 = ISNULL(T1.ContId1,T2.ContId1),
 ContId2 = ISNULL(T1.ContId2,T2.ContId2)
 Amount = SUM(T1.Amount) + SUM(T2.Amount)
FROM T T1
FULL JOIN T T2 ON T2.ContId1 = T1.ContId1 AND T2.ContId2 = T1.ContId2
9 фев 12, 14:52    [12062770]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
fff_fff
Guest
kDnZP,
ничего не понимаю.
по моей ссылке вроде и с values пример,
и с union,
и синтаксис тот же.
наверное я чего-то все же не вижу

К сообщению приложен файл. Размер - 39Kb
9 фев 12, 14:53    [12062776]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
iljy
Member

Откуда:
Сообщений: 8711
kDnZP
step_ks
kDnZP, да вроде не особо секретная вещь же

Дык я щаслив, что вы сто лет как знали про такой синтаксис. Я нет. Где почитать?

Что почитать-то? Кореллированные подзапросы, агрегатные функции мин-макс, ключевое слово UNION ALL?
9 фев 12, 14:54    [12062794]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
_nobody_
Guest
_nobody_
Но если например есть еще поле Amount, по которому нужно суммировать, то лучше так:
...


Извиняюсь, забыл добавить выражение группировки во втором примере
SELECT
 ContId1 = ISNULL(T1.ContId1,T2.ContId1),
 ContId2 = ISNULL(T1.ContId2,T2.ContId2)
 Amount = SUM(T1.Amount) + SUM(T2.Amount)
FROM T T1
FULL JOIN T T2 ON T2.ContId1 = T1.ContId1 AND T2.ContId2 = T1.ContId2
GROUP BY T1.ContId1, T1.ContId2, T2.ContId1, T2.ContId2
9 фев 12, 14:57    [12062826]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
iljy
Что почитать-то? Кореллированные подзапросы, агрегатные функции мин-макс, ключевое слово UNION ALL?

Неужель это давно известный синтаксис, и тока я про него не знаю? Где про такой вариант почитать можно? Как можно еще извернуться в кореллированных подзапросах? Имеется в виду неявное обращение к той же таблице.
-- select @@version
-- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05
-- Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) 
declare @conts table (cont1 int, cont2 int)
insert into @conts(cont1, cont2)
SELECT 251 cont1, 250 cont2 UNION ALL SELECT 250, 251 UNION ALL SELECT 250, 252 UNION ALL SELECT 251, 252


SELECT  c1,
        c2
FROM    ( SELECT    ( SELECT    MIN(x)
                      FROM      ( SELECT    cont1 x
                                  UNION ALL
                                  SELECT    cont2 x
                                ) Y
                    ) c1,
                    ( SELECT    MAX(x)
                      FROM      ( SELECT    cont1 x
                                  UNION ALL
                                  SELECT    cont2 x
                                ) Y
                    ) c2
          FROM      @conts
        ) Z
GROUP BY c1,c2
9 фев 12, 14:59    [12062864]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
iljy
Member

Откуда:
Сообщений: 8711
kDnZP
Имеется в виду неявное обращение к той же таблице.

Не понял... Вы не знали, что в кореллированных подзапросах можно обращаться к таблице из внешнего запроса? И почему неявно - там явно указаны имена полей.
9 фев 12, 15:04    [12062905]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Плохо только, что для c.cont1 IS NULL
MIN(v) = MAX(v) = c.cont2

Из-за того, что агрегатные функции игнорируют NULL

Вместо MIN(v) можно (SELECT TOP(1) v FROM ... ORDER BY v),
а вместо MAX(v) - (SELECT TOP(1) v FROM ... ORDER BY v DESC)
9 фев 12, 15:04    [12062906]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
iljy
Не понял... Вы не знали, что в кореллированных подзапросах можно обращаться к таблице из внешнего запроса? И почему неявно - там явно указаны имена полей.

Вы серьезно или прикалываетесь? Можете расписать эквивалентный полный запрос? И еще раз прошу пример такого же запроса на этом форуме или другом ресурсе, чтобы я успокоился.

* И да, я впервые вижу синтаксис неявного обращения к внешней таблице одним сканом, с юнионом столбцев этой же таблицы. Но рад бы увидеть другие случаи, а также прикинуть какие это открывает дополнительные возможности.
+
--select @@version -- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) 
declare @conts table (cont1 int, cont2 int)
insert into @conts(cont1, cont2)
SELECT 251 cont1, 250 cont2 UNION ALL SELECT 250, 251 UNION ALL SELECT 250, 252 UNION ALL SELECT 251, 252

-- Оригинальный
SELECT  c1,
        c2
FROM    ( SELECT    ( SELECT    MIN(x)
                      FROM      ( SELECT    cont1 x
                                  UNION ALL
                                  SELECT    cont2 x
                                ) Y
                    ) c1,
                    ( SELECT    MAX(x)
                      FROM      ( SELECT    cont1 x
                                  UNION ALL
                                  SELECT    cont2 x
                                ) Y
                    ) c2
          FROM      @conts
        ) Z
GROUP BY c1,c2

-- Частичный (нифига не работает), т.к. ссылается на самого себя
SELECT  c1,
        c2
FROM    ( SELECT    ( SELECT    MIN(x)
                      FROM      ( SELECT    t21.cont1 x FROM @conts t21 where t21.cont1=cont1 and t21.cont2=cont2
                                  UNION ALL
                                  SELECT    t22.cont2 FROM @conts t22 where t22.cont2=cont2 and t22.cont1=cont1
                                ) Y
                    ) c1,
                    ( SELECT    MAX(x)
                      FROM      ( SELECT    t21.cont1 x FROM @conts t21 where t21.cont1=cont1 and t21.cont2=cont2
                                  UNION ALL
                                  SELECT    t22.cont2 FROM @conts t22 where t22.cont2=cont2 and t22.cont1=cont1
                                ) Y
                    ) c2
          FROM      @conts
        ) Z
GROUP BY c1,c2

-- Развернутый эквивалентный, но план другой, несколько сканов.
SELECT  c1,
        c2
FROM    ( SELECT    ( SELECT    MIN(x)
                      FROM      ( SELECT    t21.cont1 x FROM @conts t21 where t21.cont1=t.cont1 and t21.cont2=t.cont2
                                  UNION ALL
                                  SELECT    t22.cont2 FROM @conts t22 where t22.cont2=t.cont2 and t22.cont1=t.cont1
                                ) Y
                    ) c1,
                    ( SELECT    MAX(x)
                      FROM      ( SELECT    t21.cont1 x FROM @conts t21 where t21.cont1=t.cont1 and t21.cont2=t.cont2
                                  UNION ALL
                                  SELECT    t22.cont2 FROM @conts t22 where t22.cont2=t.cont2 and t22.cont1=t.cont1
                                ) Y
                    ) c2
          FROM      @conts t
        ) Z
GROUP BY c1,c2
9 фев 12, 15:24    [12063150]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
iljy
Member

Откуда:
Сообщений: 8711
kDnZP,

это я не могу понять - вы серьезно или прикалываетесь. При разрешении имен в кореллированном подзапросе, если явно не указано имя таблицы или псевдоним, имя поля сначала ищется в контексте внутреннего запроса, затем внешнего. В вашем примере, который не работет, имя просто берется из ближайшей по контексту подходящей таблицы, т.е. самой внутренней. Поставьте таблице из внешнего запроса псевдоним t и пишите его в условии явно, все будет работать.
9 фев 12, 15:32    [12063231]     Ответить | Цитировать Сообщить модератору
 Re: Группировка по двум колонкам  [new]
iljy
Member

Откуда:
Сообщений: 8711
kDnZP,

пример:
10414953
9 фев 12, 15:38    [12063305]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить