asp.net 利用多表聯合查詢進行彙總統計

暖楓無敵發表於2015-07-08

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_col

SET @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條件時正常,如果加條件,就存在問題,如下圖所示:







相關文章