如何向 SELECT 语句 SQL Server 添加声明值-解网

问：

以下代码没有错误，但按照我的要求给出了错误的值，并且只是传回并且在设置后没有应用任何条件。所以我不需要..attackPercentagesafetyPercentageconsistencyPercentage

WHERE @raterAccountID = raterAccountID

for 和attackPercentagesafetyPercentageconsistencyPercentage

语句设置的值是我所需要的。我只是不知道如何把，并在语句中将它们传递给 VB 中的读者。SETattackPercentagesafetyPercentageconsistencyPercentageSELECT

attackPercentage，并且是根据用户投票计算出的玩家实际总体评分。safetyPercentageconsistencyPercentage

attack，并且是评分者投票的内容。safetyconsistency

ALTER PROCEDURE [dbo].[SelectPlayersRating]
    @playerAccountID uniqueidentifier,
    @raterAccountID uniqueidentifier
AS
BEGIN
    DECLARE @attackPercentage INT, @safetyPercentage INT,  @consistencyPercentage INT

    SET @attackPercentage = (SELECT SUM(attack) 
                             FROM tblRatings 
                             WHERE @playerAccountID = playerAccountID) /
                            (SELECT COUNT(attack) 
                             FROM tblRatings 
                             WHERE @playerAccountID = playerAccountID)

    SET @safetyPercentage = (SELECT SUM(safety) 
                             FROM tblRatings 
                             WHERE @playerAccountID = playerAccountID) / 
                            (SELECT COUNT(safety) 
                              FROM tblRatings 
                              WHERE @playerAccountID = playerAccountID)

    SET @consistencyPercentage = (SELECT SUM(consistency) 
                                  FROM tblRatings 
                                  WHERE @playerAccountID = playerAccountID) /
                                 (SELECT COUNT(consistency) 
                                  FROM tblRatings 
                                  WHERE @playerAccountID = playerAccountID)

    SELECT 
        attack, safety, consistency, 
        @attackPercentage AS attackPercentage, 
        @safetyPercentage AS safetyPercentage, 
        @consistencyPercentage AS consistencyPercentage 
    FROM
        tblRatings 
    WHERE 
        @playerAccountID = playerAccountID 
        AND @raterAccountID = raterAccountID
END

    Dim DBConnect4 As New DBConn
    Using db As DbConnection = DBConnect4.Conn("DBConnectionString")
        Dim cmd As SqlCommand = DBConnect4.Command(db, "SelectPlayersRating")
        cmd.Parameters.Add(New SqlParameter("playerAccountID", SqlDbType.UniqueIdentifier, ParameterDirection.Input)).Value = Guid.Parse(Request.QueryString("aID"))
        cmd.Parameters.Add(New SqlParameter("raterAccountID", SqlDbType.Uniqueidentifier, ParameterDirection.Input)).Value = acc.accountID
        db.Open()
        Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While DR.Read
            ddlAttack.SelectedValue=DR("attack")
            ddlSafety.SelectedValue=DR("safety")
            ddlConsistency.SelectedValue=DR("consistency")
            Dim attack As Integer = DR("attackPercentage")
            Dim safety As Integer = DR("safetyPercentage")
            Dim consistency As Integer = DR("consistencyPercentage")
            Me.attack.Attributes("data-cp-percentage")=attack
            Me.safety.Attributes("data-cp-percentage")=safety
            Me.consistency.Attributes("data-cp-percentage")=consistency
            Me.overall.Attributes("data-cp-percentage")=(attack+safety+consistency)/3
        End While
        DR.Close()
        DR = Nothing
        cmd.Dispose()
        cmd = Nothing
        db.Dispose()
        db.Close()
    End Using
End Sub

sql-服务器

ALTER PROCEDURE [dbo].[SelectPlayersRating]
@playerAccountID uniqueidentifier,
@raterAccountID uniqueidentifier
AS

SELECT
    attack = SUM(attack) / COUNT(attack),
    safety = SUM(safety) / COUNT(safety),
    consistency = SUM(consistency) / COUNT(consistency)
FROM tblRatings
WHERE @playerAccountID = playerAccountID AND @raterAccountID <> raterAccountID;

占总数百分比的答案：

ALTER PROCEDURE [dbo].[SelectPlayersRating]
@playerAccountID uniqueidentifier,
@raterAccountID uniqueidentifier
AS

SELECT
    t.attack,
    t.safety,
    t.consistency,
    totals.*
FROM tblRatings t
CROSS JOIN (
    SELECT
        attackPercentage = SUM(attack) / COUNT(attack),
        safetyPercentage = SUM(safety) / COUNT(safety),
        consistencyPercentage = SUM(consistency) / COUNT(consistency)
    FROM tblRatings
    WHERE @playerAccountID = playerAccountID
) totals
WHERE @playerAccountID = playerAccountID AND @raterAccountID = raterAccountID;

回到我的原始代码。它有效，但只选择的结果，和 where =。我不想要的最后一个条件。我只是想让它把我设置的值发回给读者，但我没有考虑如何将它们放入现有的 SELECT 语句中。@attackPercentage@safetyPercentage@consistencyPercentageraterAccountIDraterAccountIDraterAccountID=raterAccount

0赞 Charlieface 1/11/2021

这个查询现在给你正确的结果吗？或者你希望它作为两个单独的结果集/秒（这就是我的做法，但我想匹配你的设计）SELECT

1赞 Panagiotis Kanavos 1/10/2021 #2

看起来真正的问题是如何显示特定玩家每行的平均值。可以替换为 etc。SUM(attack)/COUNT(attack)AVG(attack)

要显示每个详细信息行的聚合值，可以使用子句，例如：OVER

SELECT
    attack, safety, consistency
    AVG(attack) OVER(PARTITION BY playerAccountiD) as attackPercentage,
    AVG(safety) OVER(PARTITION BY playerAccountiD) as safetyPercentage,
    AVG(consistency) OVER(PARTITION BY playerAccountiD) as consistencyPercentage
FROM tblRatings
WHERE @playerAccountID = playerAccountID AND @raterAccountID <> raterAccountID;

OVER 子句仅根据子句中指定的数据集计算聚合PARTITION BY

上一个：CSS 主菜单不会悬停，因为下面的超链接

下一个：从 SQL Server 返回值到 VB.Net

如何向 SELECT 语句 SQL Server 添加声明值

How to add declared values to SELECT statement SQL Server

评论

评论