SQL 查询的标头和尾部

本文关键字：尾部 查询 SQL | 更新日期: 2023-09-27 18:35:52

我有一个sql查询，它从SQL数据库中检索信息，我需要为发送到客户端的每个批次添加标头和尾部。我已经搜索过，但没有运气。标题记录应具有记录类型（1）CPC编号（0000）文件序列号（001），尾页记录应具有记录类型（1）请求总数（5），总法庭费用（），总索赔金额（）。 

      string stringSql1 = " SELECT distinct  " +
             "'" + comboBox6.Text + "' as Recordtype" +
             ",'" + textBox5.Text + "' as Recordtype" +
               " , space(1983) ";
          string stringSql = " SELECT distinct  " +
            "'" + comboBox6.Text + "' as RecordType" +
             " , left([Claimant Name] +'                              ',30) " +
             " , left([Claimant Address1] +'                             ',30)  " +
              " , left([Claimant Address2] +'                             ',30) as ClaimantAddress2 " +
              " , left([Claimant Address3] +'                             ',30) as ClaimantAddress3 " +
              " , left([Claimant Address4] +'                             ',30) as ClaimantAddress4 " +
    string whereClause = "";
===so on ....==================================
               string[] tempArray = new string[this.txt.Lines.Length];
                tempArray = this.txt.Lines;
                if (this.txt.Lines.Length == 0)
                {
                    return;
                }
                for (int counter = 0; counter <= tempArray.Length-1; counter++)
                {
                    if (tempArray[counter].Trim().Length > 0)
                    {
                        whereClause = whereClause + "'" + tempArray[counter] + "'" + ", ";
                    }
                }
                whereClause = whereClause.TrimEnd(' ', ',');
                whereClause = "(" + whereClause + ")";
                stringSql = stringSql.Replace("{where}", whereClause);
                myDataset = new DataSet("SQL");
                SqlConnection myConn = new SqlConnection();

                SqlCommand myCommand = new SqlCommand();
                myCommand.CommandType = CommandType.Text;
                myCommand.CommandText = stringSql;
                myCommand.Connection = myConn;
                SqlDataAdapter myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(myDataset);

SQL 查询的标头和尾部

假设您希望在单个SELECT语句中所有这些内容，则可以将包含汇总信息（如总计，统计信息和内容）的列添加到典型的SELECT中。例如

  declare @sumTotal decimal
  set @sumTotal = (SELECT SUM(ColumnName) FROM tableNameY)
--declare similar variables for total number of request(5), total court fee() and total claim amount(). 
--Create a query that outputs row data along with the info you just calculated
  select '0000' as 'CPC number', '001' as 'File sequence number', ColumnName, @sumTotal as 'total number of request', @totalCourtFeesvariable as 'total court fees'--etc...
  from tableNameX
相关文章：