SQL需要帮助缩短查询以迭代列。

huangapple go评论63阅读模式
英文:

SQL help needed to shorten the query to iterate through columns

问题

我有一个大约有200列的表,主键是ID,其中大约有180列是y/n类型的。但是我需要通过计算表中每个ID的所有y/n列中的“y”总数来计算一个名为“百分比”的列。

任何帮助将不胜感激。

我必须使用Access,我正在寻找一个在SQL中完成这个任务的简短查询。我可以使用列名来编写查询,但那将需要很多时间。

英文:

I have a table of about 200 columns having primary key as the ID and almost 180 of them are y/n fed. But i have to calculate one column “percentage” through calculating total “y” present from overall y/n columns for each ID in the table.

Any help will be appreciated.

I’m bound to use access and I’m searching for a short query to do it in SQL. I can use column names to write the query but that will take lots of time.

答案1

得分: 2

使用 VBADAO 来循环表格的代码如下:

Public Function CheckPercent()

    Const Sql           As String = "Select * From YourTable"
    
    Dim Records         As DAO.Recordset
    Dim Field           As DAO.Field
    
    Dim Total           As Integer
    Dim Selected        As Integer
    
    Set Records = CurrentDb.OpenRecordset(Sql)
    While Not Records.EOF
        ' Calculate.
        Total = 0
        Selected = 0
        For Each Field In Records.Fields
            If Field.Type = dbBoolean Then
                Total = Total + 1
                Selected = Selected + Abs(Field.Value)
            End If
        Next

        ' Print result.
        Debug.Print "Id " & Records(0).Value, "Total " & Total, "Selected " & Selected, Format(Selected / Total, "Percent")

        ' Save the percentage for this record in field PercentSelected 
        ' having the data type Currency.
        Records.Edit
        Records!PercentSelected.Value = Selected / Total
        Records.Update

        ' Move on.
        Records.MoveNext
    Wend
    Records.Close
    
End Function

输出示例:

Id 1002       Total 8       Selected 5    62,50%
Id 1003       Total 8       Selected 6    75,00%
英文:

Use VBA and DAO to loop the table:

Public Function CheckPercent()

    Const Sql           As String = "Select * From YourTable"
    
    Dim Records         As DAO.Recordset
    Dim Field           As DAO.Field
    
    Dim Total           As Integer
    Dim Selected        As Integer
    
    Set Records = CurrentDb.OpenRecordset(Sql)
    While Not Records.EOF
        ' Calculate.
        Total = 0
        Selected = 0
        For Each Field In Records.Fields
            If Field.Type = dbBoolean Then
                Total = Total + 1
                Selected = Selected + Abs(Field.Value)
            End If
        Next

        ' Print result.
        Debug.Print "Id " & Records(0).Value, "Total " & Total, "Selected " & Selected, Format(Selected / Total, "Percent")

        ' Save the percentage for this record in field PercentSelected 
        ' having the data type Currency.
        Records.Edit
        Records!PercentSelected.Value = Selected / Total
        Records.Update

        ' Move on.
        Records.MoveNext
    Wend
    Records.Close
    
End Function

Output example:

Id 1002       Total 8       Selected 5    62,50%
Id 1003       Total 8       Selected 6    75,00%

huangapple
  • 本文由 发表于 2023年6月13日 18:50:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76464138.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定