时间戳

首页 > 解决方案 > 子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或

sql - 子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或

问题描述

我正在尝试将城市表更新为 City + #p + 'Number of player of Team'+ #g + 'Number of Goals' 但有一个解决方案，包括 IN 和 EXISTS 运算符，但不能解决我的问题。

UPDATE Team set City = City +'#p'+ CONVERT(nvarchar(10),(select COUNT(p1.PlayerID) 
                                            from  Team as t     
                                            inner join PlayerTeam as pt on pt.TeamID= t.TeamID
                                            inner join Player as p1 on p1.PlayerID= pt.PlayerID
                                            where pt.Season = '13-14' 
                                            group by t.Name) )

                                            + '#g' +

                                            CONVERT(nvarchar(10),(select Count(g.PlayerID) 
                                            from  Team as t 
                                            inner join PlayerTeam as pt on pt.TeamID = t.TeamID
                                            inner join Player as p1 on p1.PlayerID= pt.PlayerID
                                            inner join Goals as g on g.PlayerID = p1.PlayerID
                                            where pt.Season = '13-14'
                                            group by t.Name))

标签： sqlsql-server

解决方案

您的子查询正在尝试使用包含更新的每一行的每个团队计数的结果集，这是没有意义的，因此会出现错误。您需要使用正在更新的行的 ID。

UPDATE Team
SET City = T.City + '#p' + CONVERT(nvarchar(10), COUNT(DISTINCT pt.PlayerId)) + '#g' + CONVERT(nvarchar(10), COUNT(pt.PlayerId))
FROM Team as T
INNER JOIN PlayerTeam as PT ON PT.TeamID = T.TeamID AND PT.Season = '13-14' 
INNER JOIN Goals as G ON G.PlayerId = PT.PlayerId

推荐阅读