我正在尝试从我创建的临时表在Azure Data Studio中运行查询。但是,在我的SELECT
语句中,它不识别列名。SELECT
语句(即FirstPlayer, SecondPlayer, Score
(中的列名带红色下划线,查询返回为无效。
你知道如何更改语法来运行这个程序吗?
IF OBJECT_ID('tempdb.dbo.#GameScores','U') IS NOT NULL
DROP TABLE #GameScores;
GO
CREATE TABLE #GameScores
(
FirstPlayer VARCHAR(10),
SecondPlayer VARCHAR(10),
Score INTEGER
);
GO
INSERT INTO #GameScores
VALUES ('Joe','Ryan', 120),
('Sue', 'Jackie', 200),
('Ryan', 'Sue', 50),
('Ryan', 'Joe', 100);
GO
SELECT DISTINCT
FirstPlayer,
SecondPlayer,
IF(Score = MAX(Score), MAX(Score) + 20, Score) AS Score
FROM
#GameScores
WHERE
SecondPlayer NOT LIKE "JO%"
OR Points <> 100
如果选择查询中有其他列,则需要GROUP BY.
为了使查询工作,您必须首先计算max(分数(,然后将其应用于SELECT查询中,如下所示。此外,表中没有积分列。我想你们指的是分数栏。
DECLARE @max_Score INT = (SELECT max(score) from #GameScores)
SELECT
DISTINCT FirstPlayer,
SecondPlayer,
CASE WHEN Score = @max_Score THEN @max_Score+20 ELSE Score END AS Score
FROM #GameScores
WHERE SecondPlayer NOT LIKE 'JO%'
OR Score <> 100
FirstPlayer | SecondPlayer | 得分|
---|---|---|
Joe | Ryan | 120 |
Ryan | Sue | 50 |
Sue | Jackie | 220 |