asp.net 利用多表联合查询进行汇总统计
生活随笔
收集整理的這篇文章主要介紹了
asp.net 利用多表联合查询进行汇总统计
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
3張表相關聯，按照各個地區用戶統計每個欄目在某個時間段的上報數量和審核數量，表結構如下圖所示：
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??? ??
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
實現的效果如下圖所示：
本來打算使用行轉列實現，但是由于各個用戶在不同時間段，有的欄目沒有上報數據，造成一定問題，經過半天嘗試，不行后，考慮欄目基本固定，使用Repeater嵌套實現，源碼如下：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="InfoTJ.aspx.cs" Inherits="Portals.NewsManager.InfoTJ" %><!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>上報數據信息統計</title><script src="../Scripts/My97DatePicker/WdatePicker.js"></script><link href="../Content/ListCSS.css" rel="stylesheet" /><script src="../Scripts/jquery-1.7.2.min.js"></script><style type="text/css">body {font-size: 15px;}td {font-size: 15px;height: 20px;line-height: 20px;}.table {border-right: black 1px solid;border-bottom: black 1px solid;}.table td {border-top: black 1px solid;border-left: black 1px solid;}</style><script type="text/javascript">//保存到Excel中function saveCode(obj) {var winname = window.open('', '_blank', 'top=10000');var strHTML = document.all.divTJ.innerHTML;winname.document.open('text/html', 'replace');winname.document.writeln(strHTML);winname.document.execCommand('saveas', '', '信息統計.xls');winname.close();}</script> </head> <body><form id="form1" runat="server"><div><table><tr><td>上報賬戶<asp:DropDownList ID="ddlAccount" runat="server"></asp:DropDownList></td><td>時間自<asp:TextBox ID="tbStart" Width="100px" onFocus="WdatePicker({isShowClear:false,readOnly:true,dateFmt:'yyyy-MM-dd'})" runat="server"></asp:TextBox></td><td>至<asp:TextBox ID="tbEnd" Width="100px" onFocus="WdatePicker({isShowClear:false,readOnly:true,dateFmt:'yyyy-MM-dd'})" runat="server"></asp:TextBox></td><td><asp:ImageButton ID="btnQuery" runat="server" ImageUrl="~/images/btnQuery.gif" Text="查詢" OnClick="btnQuery_Click" /></td><td><asp:ImageButton ID="btnExport" runat="server" ImageUrl="~/images/btnExcel.gif" Text="導出" OnClientClick="saveCode(divTJ);" /></td></tr></table></div><div style="height: 5px;"></div><div id="divTJ"><table width="1500px;" id="tableTJ" class="table" cellpadding="0" cellspacing="0"><tr><td colspan="15" style="width: 1500px; text-align: center;"><table width="1500px" cellpadding="0" cellspacing="0"><tr><td style="width: 100px; text-align: center; font-weight: bold;">地區\類別</td><td colspan="14" style="width: 1400px; text-align: center;"><table width="1400px" cellpadding="0" cellspacing="0"><tr><td style="width: 100px; text-align: center; font-weight: bold;">本省動態</td><td style="width: 100px; text-align: center; font-weight: bold;">國內動態</td><td style="width: 100px; text-align: center; font-weight: bold;">通知公告</td><td style="width: 100px; text-align: center; font-weight: bold;">政策法規</td><td style="width: 100px; text-align: center; font-weight: bold;">要聞聚焦</td><td style="width: 100px; text-align: center; font-weight: bold;">公共機構節能</td><td style="width: 100px; text-align: center; font-weight: bold;">標準規范</td><td style="width: 100px; text-align: center; font-weight: bold;">節能產品</td><td style="width: 100px; text-align: center; font-weight: bold;">節能案例</td><td style="width: 100px; text-align: center; font-weight: bold;">節能常識</td><td style="width: 100px; text-align: center; font-weight: bold;">節能宣傳</td><td style="width: 100px; text-align: center; font-weight: bold;">新產品新技術</td><td style="width: 100px; text-align: center; font-weight: bold;">已審核</td><td style="width: 100px; text-align: center; color: red; font-weight: bold;">共計</td></tr></table></td></tr></table></td></tr><tr><td colspan="15" style="width: 1500px; text-align: center;"><asp:Repeater ID="rptRegion" runat="server" OnItemDataBound="rptRegion_ItemDataBound"><HeaderTemplate><table width="1500px" cellpadding="0" cellspacing="0"></HeaderTemplate><ItemTemplate><tr><td colspan="1" style="width: 100px; text-align: center;"><%#Eval("username") %></td><td colspan="14" style="width: 1400px; text-align: center;"><asp:Repeater ID="rptV" runat="server"><HeaderTemplate><table width="1400px" cellpadding="0" cellspacing="0"></HeaderTemplate><ItemTemplate><tr><td style="width: 100px; text-align: center;"><%#Eval("節能產品") %></td><td style="width: 100px; text-align: center;"><%#Eval("節能案例") %></td><td style="width: 100px; text-align: center;"><%#Eval("要聞聚焦") %></td><td style="width: 100px; text-align: center;"><%#Eval("新產品新技術") %></td><td style="width: 100px; text-align: center;"><%#Eval("標準規范") %></td><td style="width: 100px; text-align: center;"><%#Eval("本省動態") %></td><td style="width: 100px; text-align: center;"><%#Eval("國內動態") %></td><td style="width: 100px; text-align: center;"><%#Eval("通知公告") %></td><td style="width: 100px; text-align: center;"><%#Eval("政策法規") %></td><td style="width: 100px; text-align: center;"><%#Eval("資料下載") %></td><td style="width: 100px; text-align: center;"><%#Eval("節能宣傳") %></td><td style="width: 100px; text-align: center;"><%#Eval("公共機構節能") %></td><td style="width: 100px; text-align: center;"><%#Eval("已審核") %></td><td style="width: 100px; text-align: center; color: red; font-weight: bold;"><%#Eval("總計") %></td></tr></ItemTemplate><FooterTemplate></table></FooterTemplate></asp:Repeater></td></tr></ItemTemplate><FooterTemplate><tr><td style="width: 100px; text-align: center; color: blue; font-weight: bold;">單項共計：</td><td style="width: 1400px; text-align: center;"><asp:Repeater ID="rptS" runat="server"><HeaderTemplate><table width="1400px" cellpadding="0" cellspacing="0"><tr></HeaderTemplate><ItemTemplate><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T1 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T2 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T3 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T4 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T5 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T6 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T7 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T8 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T9 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T10 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T11 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T12 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T13 %></td><td style="width: 100px; text-align: center; color: blue; font-weight: bold;"><%#((Portals.BCL.TongJi)Container.DataItem).T14 %></td></ItemTemplate><FooterTemplate></tr></table></FooterTemplate></asp:Repeater></td></tr></table></FooterTemplate></asp:Repeater></td></tr></table></div></form> </body> </html>
using DAL; using Portals.BCL; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls;namespace Portals.NewsManager {public partial class InfoTJ : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){if (!IsPostBack){BindRegion();//時間框賦值this.tbStart.Text = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd");this.tbEnd.Text = DateTime.Now.ToString("yyyy-MM-dd");BindRepeater();}}public void BindRepeater(){string strSQL = @"select ID,loginid,username,tag2 from T_User";string dl = this.ddlAccount.SelectedItem.Value.ToString();// 地區if (dl != "-1"){strSQL += " where loginid='" + this.ddlAccount.SelectedItem.Value.Trim() + "'";}DataTable dt = SQLHelper.DB.Sql(strSQL).QuerySingle<DataTable>();rptRegion.DataSource = dt;rptRegion.DataBind();}protected void rptRegion_ItemDataBound(object sender, RepeaterItemEventArgs e){string dl = this.ddlAccount.SelectedItem.Value.ToString();// 地區string st = this.tbStart.Text.Trim(); //開始時間string et = this.tbEnd.Text.Trim(); //結束時間if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem){//項模版Repeater rpt2 = (System.Web.UI.WebControls.Repeater)e.Item.FindControl("rptV");DataRowView rowv = (DataRowView)e.Item.DataItem;string strSQL = @"select * from (select count(articleid) as '節能產品' from T_NEWSARTICLES where categoryid='01' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T1,(select count(articleid) as '節能案例' from T_NEWSARTICLES where categoryid='02' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T2,(select count(articleid) as '要聞聚焦' from T_NEWSARTICLES where categoryid='03' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T3,(select count(articleid) as '新產品新技術' from T_NEWSARTICLES where categoryid='04' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T4,(select count(articleid) as '標準規范' from T_NEWSARTICLES where categoryid='05' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T5,(select count(articleid) as '節能常識' from T_NEWSARTICLES where categoryid='06' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T6,(select count(articleid) as '本省動態' from T_NEWSARTICLES where categoryid='07' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T7,(select count(articleid) as '國內動態' from T_NEWSARTICLES where categoryid='08' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T8,(select count(articleid) as '通知公告' from T_NEWSARTICLES where categoryid='09' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T9,(select count(articleid) as '政策法規' from T_NEWSARTICLES where categoryid='10' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T10,(select count(articleid) as '資料下載' from T_NEWSARTICLES where categoryid='11' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T11,(select count(articleid) as '節能宣傳' from T_NEWSARTICLES where categoryid='13' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T12,(select count(articleid) as '公共機構節能' from T_NEWSARTICLES where categoryid='14' and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T13,(select count(checkuser) as '已審核' from T_NEWSARTICLES where categoryid in ('01','02','03','04','05','06','07','08','09','10','11','13','14') and checkuser is not null and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T14,(select count(articleid) as '總計' from T_NEWSARTICLES where categoryid in ('01','02','03','04','05','06','07','08','09','10','11','13','14') and createduser='" + rowv["loginid"].ToString().Trim() + @"' and createddate between '" + st + @"' and '" + et + @"') T15";DataTable dt = SQLHelper.DB.Sql(strSQL).QuerySingle<DataTable>();rpt2.DataSource = dt;rpt2.ItemDataBound += rpt2_ItemDataBound;rpt2.DataBind();}if (e.Item.ItemType == ListItemType.Footer){//在腳模版中綁定統計值Repeater rpt3 = (System.Web.UI.WebControls.Repeater)e.Item.FindControl("rptS");TongJi item = new TongJi();item.T1 = sum1;item.T2 = sum2;item.T3 = sum3;item.T4 = sum4;item.T5 = sum5;item.T6 = sum6;item.T7 = sum7;item.T8 = sum8;item.T9 = sum9;item.T10 = sum10;item.T11 = sum11;item.T12 = sum12;item.T13 = sum13;item.T14 = sum14;SUM.Add(item);rpt3.DataSource = SUM;rpt3.DataBind();}}List<TongJi> SUM = new List<TongJi>();int sum1 = 0;int sum2 = 0;int sum3 = 0;int sum4 = 0;int sum5 = 0;int sum6 = 0;int sum7 = 0;int sum8 = 0;int sum9 = 0;int sum10 = 0;int sum11 = 0;int sum12 = 0;int sum13 = 0;int sum14 = 0;void rpt2_ItemDataBound(object sender, RepeaterItemEventArgs e){if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem){DataRowView drv = (DataRowView)e.Item.DataItem;sum1 += int.Parse(drv["本省動態"].ToString());sum2 += int.Parse(drv["國內動態"].ToString());sum3 += int.Parse(drv["通知公告"].ToString());sum4 += int.Parse(drv["政策法規"].ToString());sum5 += int.Parse(drv["要聞聚焦"].ToString());sum6 += int.Parse(drv["公共機構節能"].ToString());sum7 += int.Parse(drv["標準規范"].ToString());sum8 += int.Parse(drv["節能產品"].ToString());sum9 += int.Parse(drv["節能案例"].ToString());sum10 += int.Parse(drv["節能常識"].ToString());sum11 += int.Parse(drv["節能宣傳"].ToString());sum12 += int.Parse(drv["新產品新技術"].ToString());sum13 += int.Parse(drv["已審核"].ToString());sum14 += int.Parse(drv["總計"].ToString());}}public void BindRegion(){string strSQL = @"select ID,loginid,username,tag2 from T_User";DataTable dt = SQLHelper.DB.Sql(strSQL).QuerySingle<DataTable>();ddlAccount.DataSource = dt;ddlAccount.DataTextField = "username";ddlAccount.DataValueField = "loginid";ddlAccount.DataBind();this.ddlAccount.Items.Insert(0, new ListItem("==全部==", "-1"));}/// <summary>/// 查詢/// </summary>/// <param name="sender"></param>/// <param name="e"></param>protected void btnQuery_Click(object sender, ImageClickEventArgs e){BindRepeater();}} }
附上一個半途而廢的存儲過程，使用了行轉列：
create proc [dbo].[proc_TJ] ( @sql_where NVARCHAR(MAX) ) as DECLARE @sql_str NVARCHAR(MAX) DECLARE @sql_col NVARCHAR(MAX) DECLARE @tableName SYSNAME --行轉列表 DECLARE @groupColumn SYSNAME --分組字段 DECLARE @row2column SYSNAME --行變列的字段 DECLARE @row2columnValue SYSNAME --行變列值的字段 DECLARE @alias NVARCHAR(20) set @alias= '地區\類別' SET @tableName = 'v_NewsArticles' SET @groupColumn = 'username' SET @row2column = 'name' SET @row2columnValue = 'articleid' --SET @sql_where = 'WHERE createddate between ''2015-06-01'' and ''2015-08-01'''--從行數據中獲取可能存在的列 SET @sql_str = N' SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+']) FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']' --PRINT @sql_str EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET @sql_str = N' SELECT pvt.['+@groupColumn+'] as [地區\類別],pvt.[本省動態],pvt.[國內動態],pvt.[通知公告],pvt.[政策法規],pvt.[要聞聚焦],pvt.[公共機構節能],pvt.[標準規范],pvt.[節能產品],pvt.[節能案例],pvt.[節能常識],pvt.[節能宣傳],pvt.[新產品新技術] FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']'+@sql_where+') p PIVOT (count(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt ORDER BY pvt.['+@groupColumn+']' PRINT (@sql_str) EXEC (@sql_str)在不加where條件時正常，如果加條件，就存在問題，如下圖所示：
總結
以上是生活随笔為你收集整理的asp.net 利用多表联合查询进行汇总统计的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： IOS判断用户的网络类型(2/3/4G、
- 下一篇： C语言 基础60题(5)