SQL запрос с повторяющимся условием WHERE. Access 2010

Question

Можно ли сократить этот запрос? Условие WHERE одинаковое, и запросов очень много. в примере дал 2. Нужно чтобы в столбец были результаты запросов. SQL Access 2010

FROM Ext_GamePlayerStatsTVSport_temp temp, Players p, 
(
    SELECT  IIF(:TEAMID = TEAM,1,2) AS TEAMORDER
    FROM 
    (
        SELECT  FIRSTTEAMID AS TEAM
        FROM Matches
        WHERE ID = :MATCHID  
    ) 
)
WHERE :PLAYERID = p.ID 
AND temp.PlayerNumber = p.PlayerNumber 
AND temp.Teamorder=TEAMORDER  
UNION ALL
SELECT  Goal1 & '/' & Shot1
FROM Ext_GamePlayerStatsTVSport_temp temp, Players p, 
(
    SELECT  IIF(:TEAMID = TEAM,1,2) AS TEAMORDER
    FROM 
    (
        SELECT  FIRSTTEAMID AS TEAM
        FROM Matches
        WHERE ID = :MATCHID  
    ) 
)
WHERE :PLAYERID = p.ID 
AND temp.PlayerNumber = p.PlayerNumber 
AND temp.Teamorder=TEAMORDER  ```

Answer 1

А не устроит горизонтально?

SELECT Points AS val1, Goal1 & '/' & Shot1 AS val2, ...
FROM Ext_GamePlayerStatsTVSport_temp temp, Players p, 
(
    SELECT  IIF(:TEAMID = TEAM,1,2) AS TEAMORDER
    FROM 
    (
        SELECT  FIRSTTEAMID AS TEAM
        FROM Matches
        WHERE ID = :MATCHID  
    ) 
)
WHERE :PLAYERID = p.ID 
AND temp.PlayerNumber = p.PlayerNumber 
AND temp.Teamorder=TEAMORDER

Если нет, то придётся делать нечто типа

SELECT CASE nums WHEN 1 THEN col1
                 WHEN 2 THEN col1
                 ...
                 WHEN N THEN colN
                 END AS value
FROM (запрос выше) AS DATA,
     ( SELECT 1 AS num UNION SELECT 2 UNION ... SELECT N ) nums

Ну само собой причесать это под синтаксис Аксесса.

БЛОГ НА HUSL

SQL запрос с повторяющимся условием WHERE. Access 2010

Ответы (1 шт):