c# - 从 sql server-ce 数据库中的不同表中获取列的总和并将结果显示在标签控件中

Question

我有一个带有五个表 A、B、C、D 和 E 的 sqlserver 紧凑型数据库。表之间没有定义任何关系。所有表格都有一个 amount 列，我需要从前四个表格中获取总和，然后从表格 E 中减去总和(即计算我是否应该获得利润或损失)。我搜索过与此相关的问题，但无济于事。

我希望得到的是:sum(A)+sum(B)+sum(C)+sum(D)-sum(E)，就是要定义为命令文本传递给 sqlcecommand 对象。结果将显示在标签控件中。

这是我目前所拥有的:

SqlCeCommand sqlCommand = new SqlCeCommand();
            login = login.getLogin();
            SqlCeDataReader sqlDataReader;

            string cmdString =
                "SELECT SUM(AmountReceived) FROM A WHERE Date BETWEEN (@param1) AND (@param2);" +
                "SELECT SUM(AmountCharged) FROM B WHERE DateOfEntry BETWEEN (@param1) AND (@param2);" +
                "SELECT SUM(Amount) FROM C WHERE Date BETWEEN (@param1) AND (@param2);" +
                "SELECT SUM(AmountCharged) FROM D WHERE DateOfRequest BETWEEN (@param1) AND (@param2);" +                
                "SELECT SUM(AmountDue) FROM E WHERE Date BETWEEN (@param1) AND (@param2);";

            sqlCommand.Connection = login.connection;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = cmdString;
            sqlCommand.Parameters.Add("@param1", SqlDbType.DateTime).Value = dateTimePickerFromPandL.Text;
            sqlCommand.Parameters.Add("@param2", SqlDbType.DateTime).Value = dateTimePickerToPandL.Text;
            sqlDataReader = sqlCommand.ExecuteReader();

            if (sqlDataReader.Read())
            {

                labelProfitOrLossAmount.Text = "Ksh : " + //value expected to go here

            }

我从这里去哪里？

Answer 1

SELECT SUM(amount) FROM (
  SELECT AmountReceived [Amount] FROM A WHERE Date BETWEEN (@param1) AND (@param2)
  UNION ALL
  SELECT AmountCharged FROM B WHERE DateOfEntry BETWEEN (@param1) AND (@param2)
  UNION ALL
  SELECT Amount FROM C WHERE Date BETWEEN (@param1) AND (@param2)
  UNION ALL
  SELECT AmountCharged FROM D WHERE DateOfRequest BETWEEN (@param1) AND (@param2)            
  UNION ALL
  SELECT -AmountDue FROM E WHERE Date BETWEEN (@param1) AND (@param2)
) [subquery]

如果你把它放在@"和 "之间，那么你就不需要所有的字符串连接语法。