sql - 当 ORDER BY 工作正常时，GROUP BY 不可用的别名

Question

这里的主要思想是从列中提取 json 值（来自电影数据库的流派标签）并创建一个包含所有流派的新表。这是在几个步骤中完成的，首先我检查了一部电影的最大类型（这里是 7 个），然后生成 7 个表以合并在一起。

此代码段旨在创建 7 个表。

DECLARE @i INT, @str_id VARCHAR(10), @str_name VARCHAR(10)

SET @i = 0
WHILE(@i < 7)
BEGIN
    SET @str_id = '$[' + CAST(@i AS CHAR(1)) + '].id' 
    SET @str_name = '$[' + CAST(@i AS CHAR(1)) + '].name' 

    SELECT JSON_VALUE(genres, @str_id) AS temp_id, 
        JSON_VALUE(genres, @str_name) AS temp_name 
--  FROM Tmdb_orig ORDER BY temp_id DESC
    FROM Tmdb_orig GROUP BY temp_id
    
    SET @i = @i + 1
END

但我收到此错误消息：

Msg 207, Level 16, State 1, Line 12
Invalid column name 'temp_id'.

虽然使用相同别名的注释行（ORDER BY）工作正常。

那是一个错误吗？对我的误解？

我可以用另一种语言轻松地做到这一点（例如 Python），但我想尽可能多地学习 sql-server。

仅供参考：具有 5 个第一个条目的数据库结构：

CREATE TABLE Tmdb_orig(
   budget               BIGINT  NOT NULL
  ,genres               VARCHAR(230) NOT NULL
  ,homepage             VARCHAR(138)
  ,id                   INTEGER  NOT NULL PRIMARY KEY 
  ,keywords             VARCHAR(3783) NOT NULL
  ,original_language    VARCHAR(2) NOT NULL
  ,original_title       VARCHAR(86) NOT NULL
  ,overview             VARCHAR(1000)
  ,popularity           NUMERIC(10,6) NOT NULL
  ,production_companies VARCHAR(1155) NOT NULL
  ,production_countries VARCHAR(517) NOT NULL
  ,release_date         DATE 
  ,revenue              BIGINT  NOT NULL
  ,runtime              NUMERIC(5,1)
  ,spoken_languages     VARCHAR(474) NOT NULL
  ,status               VARCHAR(15) NOT NULL
  ,tagline              VARCHAR(252)
  ,title                VARCHAR(86) NOT NULL
  ,vote_average         NUMERIC(4,1) NOT NULL
  ,vote_count           INTEGER  NOT NULL
);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (237000000,'[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 878, "name": "Science Fiction"}]','http://www.avatarmovie.com/',19995,'[{"id": 1463, "name": "culture clash"}, {"id": 2964, "name": "future"}, {"id": 3386, "name": "space war"}, {"id": 3388, "name": "space colony"}, {"id": 3679, "name": "society"}, {"id": 3801, "name": "space travel"}, {"id": 9685, "name": "futuristic"}, {"id": 9840, "name": "romance"}, {"id": 9882, "name": "space"}, {"id": 9951, "name": "alien"}, {"id": 10148, "name": "tribe"}, {"id": 10158, "name": "alien planet"}, {"id": 10987, "name": "cgi"}, {"id": 11399, "name": "marine"}, {"id": 13065, "name": "soldier"}, {"id": 14643, "name": "battle"}, {"id": 14720, "name": "love affair"}, {"id": 165431, "name": "anti war"}, {"id": 193554, "name": "power relations"}, {"id": 206690, "name": "mind and soul"}, {"id": 209714, "name": "3d"}]','en','Avatar','In the 22nd century, a paraplegic Marine is dispatched to the moon Pandora on a unique mission, but becomes torn between following orders and protecting an alien civilization.',150.437577,'[{"name": "Ingenious Film Partners", "id": 289}, {"name": "Twentieth Century Fox Film Corporation", "id": 306}, {"name": "Dune Entertainment", "id": 444}, {"name": "Lightstorm Entertainment", "id": 574}]','[{"iso_3166_1": "US", "name": "United States of America"}, {"iso_3166_1": "GB", "name": "United Kingdom"}]','2009-12-10',2787965087,162,'[{"iso_639_1": "en", "name": "English"}, {"iso_639_1": "es", "name": "Espa\u00f1ol"}]','Released','Enter the World of Pandora.','Avatar',7.2,11800);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (300000000,'[{"id": 12, "name": "Adventure"}, {"id": 14, "name": "Fantasy"}, {"id": 28, "name": "Action"}]','http://disney.go.com/disneypictures/pirates/',285,'[{"id": 270, "name": "ocean"}, {"id": 726, "name": "drug abuse"}, {"id": 911, "name": "exotic island"}, {"id": 1319, "name": "east india trading company"}, {"id": 2038, "name": "love of one''s life"}, {"id": 2052, "name": "traitor"}, {"id": 2580, "name": "shipwreck"}, {"id": 2660, "name": "strong woman"}, {"id": 3799, "name": "ship"}, {"id": 5740, "name": "alliance"}, {"id": 5941, "name": "calypso"}, {"id": 6155, "name": "afterlife"}, {"id": 6211, "name": "fighter"}, {"id": 12988, "name": "pirate"}, {"id": 157186, "name": "swashbuckler"}, {"id": 179430, "name": "aftercreditsstinger"}]','en','Pirates of the Caribbean: At World''s End','Captain Barbossa, long believed to be dead, has come back to life and is headed to the edge of the Earth with Will Turner and Elizabeth Swann. But nothing is quite as it seems.',139.082615,'[{"name": "Walt Disney Pictures", "id": 2}, {"name": "Jerry Bruckheimer Films", "id": 130}, {"name": "Second Mate Productions", "id": 19936}]','[{"iso_3166_1": "US", "name": "United States of America"}]','2007-05-19',961000000,169,'[{"iso_639_1": "en", "name": "English"}]','Released','At the end of the world, the adventure begins.','Pirates of the Caribbean: At World''s End',6.9,4500);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (245000000,'[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 80, "name": "Crime"}]','http://www.sonypictures.com/movies/spectre/',206647,'[{"id": 470, "name": "spy"}, {"id": 818, "name": "based on novel"}, {"id": 4289, "name": "secret agent"}, {"id": 9663, "name": "sequel"}, {"id": 14555, "name": "mi6"}, {"id": 156095, "name": "british secret service"}, {"id": 158431, "name": "united kingdom"}]','en','Spectre','A cryptic message from Bond’s past sends him on a trail to uncover a sinister organization. While M battles political forces to keep the secret service alive, Bond peels back the layers of deceit to reveal the terrible truth behind SPECTRE.',107.376788,'[{"name": "Columbia Pictures", "id": 5}, {"name": "Danjaq", "id": 10761}, {"name": "B24", "id": 69434}]','[{"iso_3166_1": "GB", "name": "United Kingdom"}, {"iso_3166_1": "US", "name": "United States of America"}]','2015-10-26',880674609,148,'[{"iso_639_1": "fr", "name": "Fran\u00e7ais"}, {"iso_639_1": "en", "name": "English"}, {"iso_639_1": "es", "name": "Espa\u00f1ol"}, {"iso_639_1": "it", "name": "Italiano"}, {"iso_639_1": "de", "name": "Deutsch"}]','Released','A Plan No One Escapes','Spectre',6.3,4466);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (250000000,'[{"id": 28, "name": "Action"}, {"id": 80, "name": "Crime"}, {"id": 18, "name": "Drama"}, {"id": 53, "name": "Thriller"}]','http://www.thedarkknightrises.com/',49026,'[{"id": 849, "name": "dc comics"}, {"id": 853, "name": "crime fighter"}, {"id": 949, "name": "terrorist"}, {"id": 1308, "name": "secret identity"}, {"id": 1437, "name": "burglar"}, {"id": 3051, "name": "hostage drama"}, {"id": 3562, "name": "time bomb"}, {"id": 6969, "name": "gotham city"}, {"id": 7002, "name": "vigilante"}, {"id": 9665, "name": "cover-up"}, {"id": 9715, "name": "superhero"}, {"id": 9990, "name": "villainess"}, {"id": 10044, "name": "tragic hero"}, {"id": 13015, "name": "terrorism"}, {"id": 14796, "name": "destruction"}, {"id": 18933, "name": "catwoman"}, {"id": 156082, "name": "cat burglar"}, {"id": 156395, "name": "imax"}, {"id": 173272, "name": "flood"}, {"id": 179093, "name": "criminal underworld"}, {"id": 230775, "name": "batman"}]','en','The Dark Knight Rises','Following the death of District Attorney Harvey Dent, Batman assumes responsibility for Dent''s crimes to protect the late attorney''s reputation and is subsequently hunted by the Gotham City Police Department. Eight years later, Batman encounters the mysterious Selina Kyle and the villainous Bane, a new terrorist leader who overwhelms Gotham''s finest. The Dark Knight resurfaces to protect a city that has branded him an enemy.',112.31295,'[{"name": "Legendary Pictures", "id": 923}, {"name": "Warner Bros.", "id": 6194}, {"name": "DC Entertainment", "id": 9993}, {"name": "Syncopy", "id": 9996}]','[{"iso_3166_1": "US", "name": "United States of America"}]','2012-07-16',1084939099,165,'[{"iso_639_1": "en", "name": "English"}]','Released','The Legend Ends','The Dark Knight Rises',7.6,9106);
INSERT INTO Tmdb_orig(budget,genres,homepage,id,keywords,original_language,original_title,overview,popularity,production_companies,production_countries,release_date,revenue,runtime,spoken_languages,status,tagline,title,vote_average,vote_count) VALUES (260000000,'[{"id": 28, "name": "Action"}, {"id": 12, "name": "Adventure"}, {"id": 878, "name": "Science Fiction"}]','http://movies.disney.com/john-carter',49529,'[{"id": 818, "name": "based on novel"}, {"id": 839, "name": "mars"}, {"id": 1456, "name": "medallion"}, {"id": 3801, "name": "space travel"}, {"id": 7376, "name": "princess"}, {"id": 9951, "name": "alien"}, {"id": 10028, "name": "steampunk"}, {"id": 10539, "name": "martian"}, {"id": 10685, "name": "escape"}, {"id": 161511, "name": "edgar rice burroughs"}, {"id": 163252, "name": "alien race"}, {"id": 179102, "name": "superhuman strength"}, {"id": 190320, "name": "mars civilization"}, {"id": 195446, "name": "sword and planet"}, {"id": 207928, "name": "19th century"}, {"id": 209714, "name": "3d"}]','en','John Carter','John Carter is a war-weary, former military captain who''s inexplicably transported to the mysterious and exotic planet of Barsoom (Mars) and reluctantly becomes embroiled in an epic conflict. It''s a world on the brink of collapse, and Carter rediscovers his humanity when he realizes the survival of Barsoom and its people rests in his hands.',43.926995,'[{"name": "Walt Disney Pictures", "id": 2}]','[{"iso_3166_1": "US", "name": "United States of America"}]','2012-03-07',284139100,132,'[{"iso_639_1": "en", "name": "English"}]','Released','Lost in our world, found in another.','John Carter',6.1,2124);

标签： sqljsonsql-servergroup-bycolumn-alias