Xojo+SQLite：如何基于聚合公式条件查询数据表？

Hey there! Let's work through this SQL issue you're facing with Xojo and SQLite. From what you described, you want to pull records where the value in TextBox111 is greater than the ratio SUM(Cramount)*26/(SUM(Dramount+Iamount))—but your current SQL is throwing syntax errors. Let's break down the problems and fix them step by step.

Common Issues in Your Original Query #

First, let's cover why your initial SQL might be failing:

• Aggregate functions need grouping: When using SUM() (or any aggregate), you either need to group your results (with GROUP BY) or use a subquery to calculate aggregated values. You can't use SUM() directly in a WHERE clause because WHERE filters rows before aggregation happens.
• Division by zero risk: If SUM(Dramount+Iamount) equals 0, your query will throw an error. We need to handle this edge case.
• Unsafe text input handling: Directly inserting TextBox111.Text into your SQL string risks SQL injection and formatting errors (like number formatting issues). Always use parameter binding in Xojo.

Solution 1: Filter Groups That Meet the Condition #

If you want to get aggregated groups (e.g., by Fullname or GroupNo) that satisfy your condition, use HAVING (which filters after aggregation) instead of WHERE. Here's the corrected SQL:

SELECT 
  Fullname, 
  GroupNo, 
  SUM(Cramount) AS TotalCramount, 
  SUM(Dramount + Iamount) AS TotalDI
FROM your_table_name
GROUP BY Fullname, GroupNo
HAVING ? > (SUM(Cramount) * 26) / NULLIF(SUM(Dramount + Iamount), 0)

Xojo Code to Execute This Query

' Replace "your_table_name" with your actual table name
Dim sqlQuery As String = _
  "SELECT Fullname, GroupNo, SUM(Cramount) AS TotalCramount, SUM(Dramount + Iamount) AS TotalDI " + _
  "FROM your_table_name " + _
  "GROUP BY Fullname, GroupNo " + _
  "HAVING ? > (SUM(Cramount) * 26) / NULLIF(SUM(Dramount + Iamount), 0)"

' Convert the textbox value to a number (critical for numeric comparison)
Dim threshold As Double = TextBox111.Text.ToDouble

' Execute the query with parameter binding
Dim db As SQLiteDatabase = YourDatabaseConnection ' Replace with your DB instance
Dim rs As RecordSet = db.SQLSelect(sqlQuery, threshold)

' Check for errors
If db.Error Then
  MsgBox "Query error: " + db.ErrorMessage
  Return
End If

' Process the recordset as needed
While Not rs.EOF
  ' Access fields like rs.Field("Fullname").StringValue
  rs.MoveNext
Wend
rs.Close

Solution 2: Get Individual Records Linked to Aggregated Groups #

If you want to retrieve every individual record that belongs to a group meeting your condition, use a subquery to calculate the aggregated values first, then join back to your original table:

SELECT t.*
FROM your_table_name t
JOIN (
  SELECT 
    Fullname, 
    SUM(Cramount) AS TotalCramount, 
    SUM(Dramount + Iamount) AS TotalDI
  FROM your_table_name
  GROUP BY Fullname
) AS aggregated ON t.Fullname = aggregated.Fullname
WHERE ? > (aggregated.TotalCramount * 26) / NULLIF(aggregated.TotalDI, 0)

This query first calculates the aggregated values per Fullname, then links each original record to its group's data and filters those that meet your condition.

Key Notes #

• Replace placeholders: Make sure to swap your_table_name with your actual table name, and YourDatabaseConnection with your Xojo SQLite database instance.
• NULLIF explanation: NULLIF(SUM(Dramount + Iamount), 0) converts any zero sum to NULL, which means the ratio becomes NULL—and since NULL is never greater than your threshold value, those groups are safely excluded without throwing an error.
• Parameter binding: Using ? in the SQL and passing the textbox value as a parameter avoids SQL injection and ensures numeric values are handled correctly (even if your textbox uses commas for decimal separators).

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