C#钱包应用SQL查询：如何添加关联表独有的App_User筛选条件？

Hey there! Let's work through this together since you're building your own C# wallet app—sounds like a great project!

First, let's start with the core idea: since you're already using an INNER JOIN to link tables, adding a filter on the App_User column (from the joined table) is straightforward once you structure your query properly.

Step 1: Structure Your Query with Table Aliases #

First, give your tables short, clear aliases to avoid confusion (especially if multiple tables have similar column names). Let's assume your main expense table is called Expenses, and the linked table with App_User is something like UserWalletLinks (adjust these names to match your actual schema).

Here's what your original sum query might look like (before adding the filter):

SELECT SUM(ExpenseAmount) AS TotalSpent, SUM(TransactionFee) AS TotalFees
FROM Expenses
INNER JOIN UserWalletLinks ON Expenses.WalletId = UserWalletLinks.WalletId

Step 2: Add the App_User Filter in the WHERE Clause #

Since App_User lives in the joined table, you just reference it (using the alias for clarity) in your WHERE clause. Here's the modified query:

SELECT SUM(e.ExpenseAmount) AS TotalSpent, SUM(e.TransactionFee) AS TotalFees
FROM Expenses e
INNER JOIN UserWalletLinks u ON e.WalletId = u.WalletId
WHERE u.App_User = 'john_doe' -- Replace with the username you want to filter for

Key Notes for Your C# Implementation #

• Use Parameterized Queries: Never hardcode or concatenate the App_User value directly into your SQL string—this opens you up to SQL injection attacks. Here's how to do it safely in C#:

  string connectionString = "YourDatabaseConnectionString";
  string targetUser = "john_doe"; // Get this from your app's auth system, e.g., current logged-in user
  
  string sql = @"
  SELECT SUM(e.ExpenseAmount) AS TotalSpent
  FROM Expenses e
  INNER JOIN UserWalletLinks u ON e.WalletId = u.WalletId
  WHERE u.App_User = @Username";
  
  using (SqlConnection conn = new SqlConnection(connectionString))
  {
      SqlCommand cmd = new SqlCommand(sql, conn);
      cmd.Parameters.AddWithValue("@Username", targetUser);
      conn.Open();
      decimal totalSpent = (decimal)cmd.ExecuteScalar();
      // Use totalSpent in your app logic
  }
  

• Confirm Join Logic: Double-check that your INNER JOIN uses the correct foreign key relationship (like WalletId in the example) to ensure you're only pulling expenses linked to the right user.
• Avoid Ambiguity: Always prefix columns with their table alias (like e.ExpenseAmount instead of just ExpenseAmount)—this prevents errors if multiple tables have columns with the same name.

Bonus: Grouping by User (If Needed) #

If you ever want to get totals for multiple users at once, add a GROUP BY clause:

SELECT u.App_User, SUM(e.ExpenseAmount) AS TotalSpent
FROM Expenses e
INNER JOIN UserWalletLinks u ON e.WalletId = u.WalletId
GROUP BY u.App_User

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