Почему CROSS APPLY со столбцами и агрегатными функциями нуждается в группировке

ВОПРОС

Почему запрос CROSS APPLY со столбцами и агрегатными функциями, смешанными в SELECT, не работает? для этого требуется предложение GROUP BY или все столбцы с агрегатной функцией.

КОД

CREATE TABLE A
(
A_ID int identity(1,1), 
A_NAME Varchar(20)
)

INSERT INTO A (A_NAME)
VALUES
('A'), ('AA'), ('AAA')

CREATE TABLE B
(
B_ID int identity(10,10), 
B_NAME Varchar(20),
A_ID int
)

INSERT INTO B (B_NAME, A_ID)
VALUES
('B', 1), ('BB', 3), ('BBB', 2)

--НЕ РАБОТАЕТ, ЕСЛИ SELECT ИМЕЕТ СТОЛБЦЫ, СМЕШАННЫЕ С ФУНКЦИЕЙ АГРЕГАТА

    SELECT A_NAME, MAX(B_NAME)
    FROM A
    CROSS APPLY(SELECT * FROM B WHERE B.A_ID = A.A_ID) as AxB

--РАБОТАЕТ С АГРЕГАТИВНЫМИ ФУНКЦИЯМИ

    SELECT MAX(A_NAME), MAX(B_NAME)
    FROM A
    CROSS APPLY(SELECT * FROM B WHERE B.A_ID = A.A_ID) as AxB

--РАБОТАЕТ С ГРУППОЙ

    SELECT A_NAME, MAX(B_NAME)
    FROM A
    CROSS APPLY(SELECT * FROM B WHERE B.A_ID = A.A_ID) as AxB
    GROUP BY A_NAME

--ПЕРЕЗАГРУЗИТЬ

DROP TABLE A
DROP TABLE B

ОБНОВЛЕНИЕ

ЗАКЛЮЧЕНИЕ (спасибо @Richardissimo's)

У меня есть фон Sybase 15.7, и там вы можете смешивать столбцы с агрегатными функциями и не иметь группы по пункту. в таких ситуациях группой по умолчанию будет вся таблица. поэтому в моем коде я ожидал максимального B_NAME для всех A_NAMES в таблице.

Однако в SQL Server, когда я тестировал код без перекрестного применения, я получил ту же ошибку. Похоже, SQL Server запрещает такое поведение. Что довольно круто, этим они говорят, что вы не получите больше строк, чем количество групп в агрегированном запросе.

ЭТО ПОСЛЕДОВАТЕЛЬНОСТЬ


sql sql-server cross-apply
person Nelssen    schedule 04.11.2018    source источник
comment
Чтобы ответить на этот вопрос, нам нужно увидеть, каких результатов вы ожидаете. Также из вашего кода вы можете использовать внутреннее соединение вместо перекрестного применения. Также max(max(b_name) не работает - только один max().   -  person Dale K    schedule 04.11.2018
comment
Согласен с Дейлом, я предполагаю, что вы пытались сравнить подобное с подобным, но во втором и третьем примерах есть дополнительный MAX(.   -  person Richardissimo    schedule 04.11.2018
comment
Я @DaleBurrell Вы правы, я должен использовать Inner Join, но я просто хотел применить Test Cross, и я придумал этот пример. Двойное значение Max(Max(x)) было опечаткой, которую я отредактировал в разделе кода. спасибо за ваш вклад :)   -  person Nelssen    schedule 05.11.2018

Ответы (1)


arrow_upward
3
arrow_downward

Проблема не связана с cross apply, она связана с тем, как работает агрегация. Давайте рассмотрим ваши 3 сценария в обратном порядке:

SELECT A_NAME, MAX(B_NAME) с Group By A_NAME означает, что вы возвращаете строку для каждого отдельного A_NAME, и каждое из них будет сопровождаться «самым большим» значением B_NAME, где A_NAME имеет это значение.

SELECT MAX(A_NAME), MAX(B_NAME) не имеет группы по, что означает, что группировка выполняется по всем строкам, а это означает, что запрос всегда будет возвращать только одну строку.

SELECT A_NAME, MAX(B_NAME) без Group By не имеет смысла. Если бы это было просто SELECT A_NAME, он вернул бы значение в A_NAME для каждой из строк. Таким образом, добавление MAX(B_NAME) в тот же запрос не имеет смысла, так как без Group By невозможно узнать, по чему агрегировать.

Надеюсь это поможет.

person Richardissimo    schedule 04.11.2018
comment
Привет @Richardissimo, спасибо за помощь. Я делал много кода в Sybase 15.7, и вы можете сделать это. По сути, если вы не поместите группу по умолчанию, группой будет вся таблица. поэтому в моем коде я ожидал, что MAX(B_NAME) вернет Max B_Name в таблице B. На самом деле я протестировал код без перекрестного применения и получил ту же ошибку. Похоже, SQL Server запрещает такое поведение. Что довольно круто, при этом они говорят, что вы не получите больше строк, чем фактическое количество созданных групп. ЭТО ПОСЛЕДОВАТЕЛЬНОСТЬ - person Nelssen; 05.11.2018