Odwołanie się do aliasu kolumny w klauzuli WHERE

166

SELECT logcount, logUserID, maxlogtm
   , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE daysdiff > 120

dostaję

„nieprawidłowa nazwa kolumny różnica dni”.

Maxlogtm to pole typu data i godzina. To małe rzeczy, które doprowadzają mnie do szaleństwa.

— user990016
źródło

nie jestem pewien dla mysql, ale może alias musi być zawarty w tickach `daysdiff`.

— Ash Burlaczenko

194

SELECT
   logcount, logUserID, maxlogtm,
   DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

Zwykle nie można odwoływać się do aliasów pól w WHEREklauzuli. (Potraktuj to jako całość, w SELECTtym aliasy, stosuje się poWHERE klauzuli.)

Ale, jak wspomniano w innych odpowiedziach, możesz zmusić SQL do SELECTobsługi traktowania przed WHEREklauzulą. Zwykle robi się to z nawiasami, aby wymusić logiczną kolejność operacji lub za pomocą wspólnego wyrażenia tabelowego (CTE):

Nawias / Podselekcja:

SELECT
   *
FROM
(
   SELECT
      logcount, logUserID, maxlogtm,
      DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary   
) as innerTable
WHERE daysdiff > 120

Lub zobacz odpowiedź Adama na wersję CTE tego samego.

— Jamie F.
źródło

16

Nie jest to możliwe bezpośrednio, ponieważ chronologicznie WHERE dzieje się przed SELECT, co zawsze jest ostatnim krokiem w łańcuchu wykonywania. REFER - stackoverflow.com/questions/356675/…

— david Blaine

afaik, jeśli alias w zaznaczeniu jest skorelowanym podzapytaniem, zadziała, podczas gdy rozwiązanie CTE nie.

— Răzvan Flavius Panda

Jak wspomniał Pascal w swojej odpowiedzi tutaj stackoverflow.com/a/38822328/282887 , możesz użyć klauzuli HAVING, która wydaje się działać szybciej niż podzapytania.

— Bakhtiyor

@Bakhtiyor HAVINGOdpowiedź nie działa w większości środowisk SQL, w tym w MS-SQL, którego dotyczy to pytanie. (W T-SQL HAVINGwymaga funkcji agregującej.)

— Jamie F

72

Jeśli chcesz użyć aliasu w swojej WHEREklauzuli, musisz zawinąć go w sub select lub CTE :

WITH LogDateDiff AS
(
   SELECT logcount, logUserID, maxlogtm
      , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary
)
SELECT logCount, logUserId, maxlogtm, daysdiff
FROM LogDateDiff
WHERE daysdiff > 120

— Adam Wenger
źródło

2

Czy wiesz, jak te targi są efektywne? Czy użycie CTE wiąże się z dodatkowymi kosztami?

— James,

5

CTE jest po prostu ładniejszą składnią dla zapytania podrzędnego, więc wydajność byłaby podobna do tej. Z mojego doświadczenia wynika, że różnica w wydajności nie była czymś, co mnie niepokoiło w przypadku takich operacji, ale powinno być dość proste przetestowanie tego w swoim środowisku, aby sprawdzić, czy ta konkretna tabela / zapytanie nie ma niekorzystnego wpływu na to, a nie wywołanie funkcji formuła w szczególności w klauzuli where. Podejrzewam, że nie zauważysz różnicy.

— Adam Wenger,

CTE są super ładne, dopóki nie spróbujesz ich użyć jako podzapytania. musiałem uciec się do tworzenia ich jako widoków, aby je zagnieździć. Uważam to za poważną wadę SQL

— symbiont

10

Najskuteczniejszym sposobem na zrobienie tego bez powtarzania kodu jest użycie HAVING zamiast WHERE

SELECT logcount, logUserID, maxlogtm
   , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
HAVING daysdiff > 120

— Pascal
źródło

1

Myślę, że używanie HAVINGaliasów nie jest standardem (działa jednak na MySQL). W szczególności myślę, że nie działa z SQL Server.

— tokland,

2

SQL Server:[S0001][207] Invalid column name 'daysdiff'

— Vadzim

3

SQL Server:

[S0001][8121] Column 'day' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

— Vadzim

9

Jeśli nie chcesz wyświetlać wszystkich swoich kolumn w CTE, możesz to zrobić w inny sposób outer apply:

select
    s.logcount, s.logUserID, s.maxlogtm,
    a.daysdiff
from statslogsummary as s
    outer apply (select datediff(day, s.maxlogtm, getdate()) as daysdiff) as a
where a.daysdiff > 120

— Roman Pekar
źródło

6

A co powiesz na użycie podzapytania (to zadziałało w MySQL)?

SELECT * from (SELECT logcount, logUserID, maxlogtm
   , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary) as 'your_alias'
WHERE daysdiff > 120

— Shekhar Joshi
źródło

4

MAJĄC działa w MySQL zgodnie z dokumentacją:

BIORĄC klauzula został dodany do SQL ponieważ GDZIE Hasło nie może być używany z funkcji agregujących.

— roier.rdz
źródło

4

Możesz odwołać się do aliasu kolumny, ale musisz go zdefiniować za pomocą CROSS/OUTER APPLY:

SELECT s.logcount, s.logUserID, s.maxlogtm, c.daysdiff
FROM statslogsummary s
CROSS APPLY (SELECT DATEDIFF(day, s.maxlogtm, GETDATE()) AS daysdiff) c
WHERE c.daysdiff > 120;

DBFiddle Demo

Plusy:

pojedyncza definicja wyrażenia (łatwiejsza w utrzymaniu / brak konieczności kopiowania-wklejania)
nie ma potrzeby pakowania całego zapytania w CTE / zapytanie zewnętrzne
możliwość odniesienia się w WHERE/GROUP BY/ORDER BY
możliwa lepsza wydajność (pojedyncze wykonanie)

— Łukasz Szozda
źródło

1

warto wspomnieć, że działa tylko w SQL Server

— Martin Zinovsky

1

@MartinZinovsky Pytanie jest oznaczone tagiem sql-serveri t-sql:)

— Lukasz Szozda

0

Przyjechałem tu szuka czegoś podobnego do tego, ale z przypadku, gdy, a zakończył przy użyciu gdzie tak: WHERE (CASE WHEN COLUMN1=COLUMN2 THEN '1' ELSE '0' END) = 0może można użyć DATEDIFFw WHEREbezpośrednio. Coś jak:

SELECT logcount, logUserID, maxlogtm
FROM statslogsummary
WHERE (DATEDIFF(day, maxlogtm, GETDATE())) > 120

— Scy
źródło