几个ＳＱＬ语句，汇总查询，查询合并排序

将一个用户的不同电子币收入从不同表查询出来，核对该用户电子币余额是否正确。

/// <summary>
    /// 查询电子币计算是否正确
    /// </summary>
    /// <param name="Id">用户ID</param>
    protected void Get_User_Wage(string Id)
    {
        string Sql = "select User_Id,User_Code,User_Name,User_Dzb from [User] where User_Id="+Id;
        string Ssql = "";
        string User_Id;
        string User_Code = "";
        int Dzb = 0;
        DataSet dss = null;
        DataSet ds = SqlHelper.ExecuteDateSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, Sql, null);
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            //输出用户信息
            Response.Write("-------------<br>[ " + ds.Tables[0].Rows[i]["User_Id"].ToString() + " ] " + ds.Tables[0].Rows[i]["User_Code"].ToString() + " | " + ds.Tables[0].Rows[i]["User_Name"].ToString() + " | [ " + ds.Tables[0].Rows[i]["User_Dzb"].ToString() + " ]<br>");
            User_Id = ds.Tables[0].Rows[i]["User_Id"].ToString();
            Dzb = 0;
            Ssql = "select '直推',isnull(sum(Wage_Temp_Money*Wage_Temp_Tax*datediff(day,Wage_Temp_Date_Begin,(case when datediff(day,getdate(),Wage_Temp_Date_End)>0 then getdate() else Wage_Temp_Date_End end))*0.2),0) as Money from [Wage_Temp] where Wage_Temp_User_Id in(select User_Id from [User] where User_TuJian=" + User_Id + ") union all select '业绩',isnull(sum(Wage_Temp_Money*Wage_Temp_Tax*datediff(day,Wage_Temp_Date_Begin,(case when datediff(day,getdate(),Wage_Temp_Date_End)>0 then getdate() else Wage_Temp_Date_End end))),0) as Money from [Wage_Temp] where Wage_Temp_User_Id=" + User_Id + " union all select '提现',isnull(-sum(ToCash_Money),0) from [ToCash] where ToCash_User_Id=" + User_Id + " union all select '转账',isnull(sum((case when Transfer_To=" + User_Id + " then Transfer_Money else 0-Transfer_Money end)),0) as Money from [Transfer] where Transfer_To=" + User_Id + " or Transfer_From=" + User_Id + " union all select '充值',isnull(sum(Dzb_Temp_Num),0) from [Dzb_Temp] where Dzb_Temp_User_Id=" + User_Id + " union all select '奖金',isnull(sum(Bonus_Money),0) from [Bonus] where Bonus_User_Id=" + User_Id + " and Bonus_Date_Re is not null union all select '报单',isnull(-sum(Remittance_Money),0) as Money from [Remittance] where Remittance_Status>0 and Remittance_Bdzx_Id=(select Bdzx_Id from [Bdzx] where Bdzx_User_Id=" + User_Id + ") union all select '3%奖',isnull(sum(Remittance_Money),0)*0.03 as Money from [Remittance] where Remittance_Bdzx_Stauts='true' and Remittance_Bdzx_Date is not null and Remittance_Bdzx_Id=(select Bdzx_Id from [Bdzx] where Bdzx_User_Id=" + User_Id + ")";
            dss = SqlHelper.ExecuteDateSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, Ssql, null);
            for (int j = 0; j < dss.Tables[0].Rows.Count; j++)
            {
                Response.Write(dss.Tables[0].Rows[j][0].ToString() + " | " + dss.Tables[0].Rows[j][1].ToString() + "<br>");
                Dzb += Convert.ToInt32(dss.Tables[0].Rows[j][1]);
            }
            Response.Write("该账户余额应有：" + Dzb);
            if (Dzb != Convert.ToInt32(ds.Tables[0].Rows[i]["User_Dzb"]))
            {
                Response.Write("需要改正");
            }
            Response.Write("<br>");
        }

    }

将一个用户的电子币收入支出从不同表中读取出来，排序后导入Repeater

protected void Get_Wage_List(DataView dv)
    {

        Repeater_List_Page.RecordCount = dv.Count;
        PagedDataSource pds = new PagedDataSource();
        pds.DataSource = dv;
        pds.AllowPaging = true;
        pds.CurrentPageIndex = Repeater_List_Page.CurrentPageIndex - 1;
        pds.PageSize = Repeater_List_Page.PageSize;
        Repeater_Dzb.DataSource = pds;
        Repeater_Dzb.DataBind();

        this.Repeater_List_Page.CustomInfoHTML = string.Format("当前第{0}/{1}页 共{2}条记录 每页{3}条", new object[] { this.Repeater_List_Page.CurrentPageIndex, this.Repeater_List_Page.PageCount, this.Repeater_List_Page.RecordCount, this.Repeater_List_Page.PageSize });
    }

    /// <summary>
    /// 查询电子币相关详细信息
    /// </summary>
    /// <param name="Id">用户ID</param>
    protected DataView Get_User_Wage_Dzb(string User_Id)
    {
        string Ssql = "";
        DataSet dss = null;
        Ssql = "select '提成' as Dzb_Type,Wage_Temp_Date_Begin as Dzb_Date,Wage_Temp_Money*Wage_Temp_Tax*datediff(day,Wage_Temp_Date_Begin,(case when datediff(day,getdate(),Wage_Temp_Date_End)>0 then getdate() else Wage_Temp_Date_End end))*0.2 as Dzb_Money_1,'' as Dzb_Money_2,('['+User_Code+'] 每日'+convert(varchar,Wage_Temp_Money*Wage_Temp_Tax*0.2)+'×'+convert(varchar,datediff(day,Wage_Temp_Date_Begin,(case when datediff(day,getdate(),Wage_Temp_Date_End)>0 then getdate() else Wage_Temp_Date_End end)))+'天') as Dzb_Bz from [Wage_Temp],[User] where Wage_Temp_User_Id=User_Id and Wage_Temp_User_Id in (select User_Id from [User] where User_TuJian=" + User_Id + ") union all select '工资' as Dzb_Type,Wage_Temp_Date_Begin as Dzb_Date,Wage_Temp_Money*Wage_Temp_Tax*datediff(day,Wage_Temp_Date_Begin,(case when datediff(day,getdate(),Wage_Temp_Date_End)>0 then getdate() else Wage_Temp_Date_End end))*0.2 as Dzb_Money_1,'' as Dzb_Money_2,('每日'+convert(varchar,Wage_Temp_Money*Wage_Temp_Tax)+'×'+convert(varchar,datediff(day,Wage_Temp_Date_Begin,(case when datediff(day,getdate(),Wage_Temp_Date_End)>0 then getdate() else Wage_Temp_Date_End end)))+'天') as Dzb_Bz from [Wage_Temp] where Wage_Temp_User_Id =" + User_Id + " union all select '提现' as Dzb_Type,ToCash_Date as Dzb_Date,'' as Dzb_Money_1,ToCash_Money as Dzb_Money_2,convert(varchar(10),ToCash_Date_Re,111)+' 打款'as Dzb_Bz from [ToCash] where ToCash_User_Id=" + User_Id + " union all select '转账' as Dzb_Type, Transfer_Date as Dzb_Date ,Transfer_Money as Dzb_Money_1,'' as Dzb_Money_2, '' as Dzb_Bz from [Transfer] where Transfer_To=" + User_Id + " union all select '转账' as Dzb_Type, Transfer_Date as Dzb_Date ,'' as Dzb_Money_1,Transfer_Money as Dzb_Money_2, '' as Dzb_Bz from [Transfer] where Transfer_From=" + User_Id + " union all select '充值' as Dzb_Type,Dzb_Temp_Date as Dzb_Date,Dzb_Temp_Num as Dzb_Money_1,'' as Dzb_Money_2,Dzb_Temp_Yh as Dzb_Bz from [Dzb_Temp] where Dzb_Temp_User_Id=" + User_Id + " union all select '奖金' as Dzb_Type,Bonus_Date as Dzb_Date,Bonus_Money as Dzb_Money_1,'' as Dzb_Money_2,convert(varchar(10),Bonus_Date_Re,111)+'打款' as Dzb_Bz from [Bonus] where Bonus_User_Id=" + User_Id + " and Bonus_Date_Re is not null union all select '报单服务费' as Dzb_Type,Remittance_Bdzx_Date as Dzb_Date,Remittance_Money*0.03 as Dzb_Money_1,'' as Dzb_Money_2,convert(varchar(10),Remittance_Bdzx_Date,111)+'打款'as Dzb_Bz from [Remittance] where Remittance_Bdzx_Stauts='true' and Remittance_Bdzx_Date is not null and Remittance_Bdzx_Id=(select Bdzx_Id from [Bdzx] where Bdzx_User_Id=" + User_Id + ") union all select '转币' as Dzb_Type,DTB_Temp_Date as Dzb_Date,'' as Dzb_Money_1,DTB_Temp_Dzb as Dzb_Money_2,'' as Dzb_BZ from [DTB_Temp] where DTB_Temp_User_Id=" + User_Id + " union all select '物流费用' as Dzb_Type,Wl_Temp_Kd_Date_1 as Dzb_Date,'' as Dzb_Money_1,Wl_Temp_Kd_Dzb as Dzb_Money_2,'' as Dzb_Bz from [Wl_Temp_Kd] where Wl_Temp_Kd_Dzb>0 and Wl_Temp_Kd_Bdzx_Id =(select Bdzx_Id from [Bdzx] where Bdzx_User_Id=" + User_Id + " ) order by Dzb_Date desc";
        dss = SqlHelper.ExecuteDateSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, Ssql, null);
        return dss.Tables[0].DefaultView;

    }

 

//调用

Get_Wage_List(Get_User_Wage_Dzb(Regular.GetMyCookie("User_Id")));

前台文件：

<table width="100%" cellpadding="5" cellspacing="1" bgcolor="#009900">
  <tr>
                        <td rowspan="2" bgcolor="#F0FFF0" style="width:20%;">日期</td>
                        <td rowspan="2" bgcolor="#F0FFF0"style="width:20%;">类型</td>
                        <td height="25" colspan="2" bgcolor="#F0FFF0" style="width:40%;">发生金额</td>
                        <td rowspan="2" height="25" bgcolor="#F0FFF0" style="width:20%;">备注提要</td>
        </tr>
  <tr>
    <td style="width:20%;" width="100"  height="25" bgcolor="#F0FFF0">入帐</td>
    <td style="width:20%;"  height="25" bgcolor="#F0FFF0">出帐</td>
    </tr>
                   
              <asp:Repeater ID="Repeater_Dzb" runat="server">
              <ItemTemplate>
              <tr>
                        <td  height="20" bgcolor="#ffffff" style="width:200px">&nbsp;&nbsp;<%# Eval("Dzb_Date","{0:d}")%></td>
                        <td bgcolor="#ffffff">&nbsp;&nbsp;<%# Eval("Dzb_Type")%></td>
                        <td bgcolor="#ffffff" align="right">&nbsp;&nbsp;<%# Eval("Dzb_Money_1","{0:0.00}")%></td>
                        <td bgcolor="#ffffff" align="right">&nbsp;&nbsp;<%# Eval("Dzb_Money_2", "{0:0.00}")%></td>
                        <td bgcolor="#ffffff">&nbsp;&nbsp;<%# Eval("Dzb_Bz")%></td>
                    </tr>
             </ItemTemplate>      
             </asp:Repeater>
                <tr>
                      <td  height="20" colspan="5" bgcolor="#F0FFF0">&nbsp;</td>
                    </tr>
          </table>
                </div>
                <div><webdiyer:AspNetPager  CssClass="paginator"   CurrentPageButtonClass="cpb"   FirstPageText="首页" NextPageText="下一页" LastPageText="最后一页" PrevPageText="上一页" ID="Repeater_List_Page" runat="server" Width="100%"   ShowPageIndexBox="Always" PageIndexBoxType="DropDownList" TextBeforePageIndexBox="转到: " HorizontalAlign="left" PageSize="13" OnPageChanged="Repeater_List_PageChanged" EnableTheming="true"   showcustominfosection="left"   CustomInfoTextAlign="right" LayoutType="Table" NumericButtonCount="5" ></webdiyer:AspNetPager>   
                </div>