MySQL中如何对重复COUNT值求和？附现有SQL代码及需求

First, let's start with your existing SQL query for reference:

Const SQLExpression As String = "SELECT site.siteid AS 'SITE ID', site_name AS 'SITE', COUNT(DISTINCT date) AS 'Attendance', 75 * (SELECT COUNT(DISTINCT date) AS 'Attendance') AS 'Total' FROM record LEFT JOIN learner on record.idNumber = learner.idNumber LEFT JOIN site ON learner.siteid=site.siteid WHERE MONTH(date) = MONTH(CURRENT_DATE()) AND YEAR(date) = YEAR(CURRENT_DATE()) GROUP BY record.idNumber "

The way to get that +-... ASCII table output depends on where you're running the query—here are the most common scenarios:

1. Command-Line Database Clients #

Most database CLI tools have built-in options to format output as clean ASCII tables:

MySQL/MariaDB #

Use the --table (or shorthand -t) flag when running your query via the mysql CLI:

mysql -u your_username -p --table -e "SELECT site.siteid AS 'SITE ID', site_name AS 'SITE', COUNT(DISTINCT date) AS 'Attendance', 75 * COUNT(DISTINCT date) AS 'Total' FROM record LEFT JOIN learner on record.idNumber = learner.idNumber LEFT JOIN site ON learner.siteid=site.siteid WHERE MONTH(date) = MONTH(CURRENT_DATE()) AND YEAR(date) = YEAR(CURRENT_DATE()) GROUP BY record.idNumber" your_database_name

This will output a formatted table like:

+---------+----------------+------------+-------+
| SITE ID | SITE           | Attendance | Total |
+---------+----------------+------------+-------+
| 1       | Downtown Campus| 12         | 900   |
| 2       | Westside Site  | 8          | 600   |
+---------+----------------+------------+-------+

PostgreSQL #

The psql client defaults to ASCII table formatting in interactive mode. For direct shell execution, use:

psql -U your_username -d your_database_name -c "SELECT site.siteid AS 'SITE ID', site_name AS 'SITE', COUNT(DISTINCT date) AS 'Attendance', 75 * COUNT(DISTINCT date) AS 'Total' FROM record LEFT JOIN learner on record.idNumber = learner.idNumber LEFT JOIN site ON learner.siteid=site.siteid WHERE EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE) GROUP BY record.idNumber"

Add --pset border=2 if you want thicker, more prominent +- separators.

SQL Server (sqlcmd) #

Use sqlcmd with formatting flags, then tweak to add separators:

sqlcmd -S your_server -d your_database -U your_username -P your_password -Q "SELECT site.siteid AS 'SITE ID', site_name AS 'SITE', COUNT(DISTINCT date) AS 'Attendance', 75 * COUNT(DISTINCT date) AS 'Total' FROM record LEFT JOIN learner on record.idNumber = learner.idNumber LEFT JOIN site ON learner.siteid=site.siteid WHERE MONTH(date) = MONTH(GETDATE()) AND YEAR(date) = YEAR(GETDATE()) GROUP BY record.idNumber" -W -h-1 -s "|"

You can pipe this output to a simple script or use PowerShell to wrap it in the +- separator lines for a complete ASCII table.

2. In Application Code (e.g., VB/VBA) #

Since your query is stored as a VB/VBA string, you'll need to manually build the ASCII table from your recordset. Here's a working example:

Dim rs As Recordset
Dim tableOutput As String
Dim headerLine As String
Dim separatorLine As String

' Assume you've executed the query and have an open Recordset (rs)
Set rs = YourDatabaseConnection.Execute(SQLExpression)

' Build header line with field names
headerLine = "| " & rs.Fields("SITE ID").Name & " | " & rs.Fields("SITE").Name & " | " & rs.Fields("Attendance").Name & " | " & rs.Fields("Total").Name & " |"

' Build separator line matching the header's length
separatorLine = "+" & String(Len(headerLine) - 2, "-") & "+"

' Start constructing the table
tableOutput = separatorLine & vbCrLf & headerLine & vbCrLf & separatorLine & vbCrLf

' Loop through records and add each row
Do While Not rs.EOF
    tableOutput = tableOutput & "| " & _
                  rs.Fields("SITE ID").Value & " | " & _
                  rs.Fields("SITE").Value & " | " & _
                  rs.Fields("Attendance").Value & " | " & _
                  rs.Fields("Total").Value & " |" & vbCrLf
    rs.MoveNext
Loop

' Add the final bottom separator
tableOutput = tableOutput & separatorLine

' Print to Immediate Window (or write to a file/UI)
Debug.Print tableOutput

This will generate the exact +- formatted table you're looking for directly within your application.

内容的提问来源于stack exchange，提问作者Keith