MS-SQL Server에서 별칭이 지정된 열에 대해 GROUP BY를 수행하려면 어떻게합니까?

MS-SQL Server에서 별칭이 지정된 열에 대해 GROUP BY를 수행하려면 어떻게합니까?

---

별칭이 지정된 열 (아래 예)에서 작업 별로 그룹 을 수행하려고 하는데 적절한 구문을 결정할 수 없습니다.

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY     'FullName'

올바른 구문은 무엇입니까?

---

편집하다

질문을 추가로 확장하면 (받은 답변을 예상하지 못함) 솔루션이 CASEed aliased column에 계속 적용됩니까?

SELECT       
    CASE
        WHEN LastName IS NULL THEN FirstName
        WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
    END AS 'FullName'
FROM         customers
GROUP BY     
    LastName, FirstName

그리고 대답은 그렇습니다. 여전히 적용됩니다.

---

별칭이 아닌 그룹화하려는 표현식을 전달합니다.

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY      LastName + ', ' + FirstName

---

이것이 제가하는 것입니다.

SELECT FullName
FROM
(
  SELECT LastName + ', ' + FirstName AS FullName
  FROM customers
) as sub
GROUP BY FullName

---

이 기술은 "편집"시나리오에 간단한 방식으로 적용됩니다.

SELECT FullName
FROM
(
  SELECT
     CASE
       WHEN LastName IS NULL THEN FirstName
       WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
     END AS FullName
  FROM customers
) as sub
GROUP BY FullName

---

불행히도 GROUP BY 문에서 별칭을 참조 할 수 없습니다. 논리를 다시 작성해야합니다. 놀랍습니다.

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY     LastName + ', ' + FirstName

또는 선택 항목을 하위 선택 또는 공통 테이블 표현식에 넣은 다음 열 이름 (더 이상 별칭이 아님)을 기준으로 그룹화 할 수 있습니다.

---

죄송합니다. MS SQL Server에서는 불가능합니다 (PostgreSQL에서는 가능함).

select lastname + ', ' + firstname as fullname
from person
group by fullname

그렇지 않으면 다음을 사용하십시오.

select x.fullname
from 
(
    select lastname + ', ' + firstname as fullname
    from person
) as x
group by x.fullname

아니면 이거:

select lastname + ', ' + firstname as fullname
from person
group by lastname, firstname  -- no need to put the ', '

위 쿼리는 더 빠르며 필드를 먼저 그룹화 한 다음 해당 필드 를 계산 합니다.

다음 쿼리는 더 느립니다 ( 먼저 선택 표현식 을 계산 한 다음 해당 계산을 기반으로 레코드를 그룹화합니다).

select lastname + ', ' + firstname as fullname
from person
group by lastname + ', ' + firstname

---

편집 한 문제 설명을 감안할 때 COALESCE()다루기 힘든 CASE표현 대신 사용 하는 것이 좋습니다 .

SELECT FullName
FROM (
  SELECT COALESCE(LastName+', '+FirstName, FirstName) AS FullName
  FROM customers
) c
GROUP BY FullName;

---

내 추측은 :

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY     LastName + ', ' + FirstName

오라클에는 유사한 제한이 있으며 이는 성가신 일입니다. 더 나은 해결책이 있는지 궁금합니다.

To answer the second half of the question, this limitation applies to more complex expressions such as your case statement as well. The best suggestion I've seen it to use a sub-select to name the complex expression.

---

You can use CROSS APPLY to create an alias and use it in the GROUP BY clause, like so:

SELECT       FullName
FROM         Customers
CROSS APPLY  (SELECT LastName + ', ' + FirstName AS FullName) Alias
GROUP BY     FullName

---

SELECT       
    CASE
        WHEN LastName IS NULL THEN FirstName
        WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
    END AS 'FullName'
FROM
    customers
GROUP BY     
    LastName,
    FirstName

This works because the formula you use (the CASE statement) can never give the same answer for two different inputs.

This is not the case if you used something like:

LEFT(FirstName, 1) + ' ' + LastName

In such a case "James Taylor" and "John Taylor" would both result in "J Taylor".

If you wanted your output to have "J Taylor" twice (one for each person):

GROUP BY LastName, FirstName

If, however, you wanted just one row of "J Taylor" you'd want:

GROUP BY LastName, LEFT(FirstName, 1)

---

If you want to avoid the mess of the case statement being in your query twice, you may want to place it in a User-Defined-Function.

Sorry, but SQL Server would not render the dataset before the Group By clause so the column alias is not available. You could use it in the Order By.

---

In the old FoxPro (I haven't used it since version 2.5), you could write something like this:

SELECT       LastName + ', ' + FirstName AS 'FullName', Birthday, Title
FROM         customers
GROUP BY     1,3,2

I really liked that syntax. Why isn't it implemented anywhere else? It's a nice shortcut, but I assume it causes other problems?

---

SELECT 
CASE WHEN LastName IS NULL THEN FirstName         
     WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName     
END AS 'FullName' 
FROM  customers GROUP BY 1`

---

For anyone who finds themselves with the following problem (grouping by ensuring zero and null values are treated as equals)...

SELECT AccountNumber, Amount AS MyAlias
FROM Transactions
GROUP BY AccountNumber, ISNULL(Amount, 0)

(I.e. SQL Server complains that you haven't included the field Amount in your Group By or aggregate function)

...remember to place the exact same function in your SELECT...

SELECT AccountNumber, ISNULL(Amount, 0) AS MyAlias
FROM Transactions
GROUP BY AccountNumber, ISNULL(Amount, 0)

참고URL : https://stackoverflow.com/questions/497241/how-do-i-perform-a-group-by-on-an-aliased-column-in-ms-sql-server