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_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條件時正常，如果加條件，就存在問題，如下圖所示：
相關文章
- thinkPHP多表聯合查詢PHP
- JPA 之 多表聯合查詢
- jpa動態查詢與多表聯合查詢
- mybatis多表聯合查詢的寫法MyBatis
- 多表聯合查詢 - 基於註解SQLSQL
- MySQL多表關聯查詢MySql
- JPA多表關聯查詢
- MYSQL學習筆記24: 多表查詢(聯合查詢,Union, Union All)MySql筆記
- 常用資料庫查詢統計彙總資料庫
- SQL Server 多表聯合查詢取最新一條資料SQLServer
- 如何做多表關聯查詢
- mysql中的多表關聯查詢MySql
- thinkphp中的多表關聯查詢PHP
- MyBatis 多表聯合查詢，欄位重複的解決方法MyBatis
- hibernate懶載入導致多表聯合查詢失敗
- 深入sql多表差異化聯合查詢的問題詳解SQL
- MyBatisPlus怎麼多表關聯查詢？MyBatis
- 多表查詢
- 開發人員不得不會的MySQL多表聯合查詢方法！MySql
- onethinkphp 如何做多表關聯查詢PHP
- 【MySQL】多表查詢MySql
- Django 多表查詢Django
- MySQL 多表查詢MySql
- 04多表查詢
- mysql多表查詢MySql
- 史上最簡單MySQL教程詳解（基礎篇）之多表聯合查詢MySql
- SQL優化之多表關聯查詢-案例一SQL優化
- Spring Data JPA 實現多表關聯查詢Spring
- Oracle總結【SQL細節、多表查詢、分組查詢、分頁】OracleSQL
- SQL查詢的：子查詢和多表查詢SQL
- Oracle ODI 12c之多表聯合查詢以及定時任務設定Oracle
- Mybatis-技術專區-Criteria的and和or進行聯合條件查詢MyBatis
- Oracle-多表查詢Oracle
- ORM多表查詢下ORM
- SQL 三表聯合查詢SQL
- 按月分表聯合查詢
- 01-sql-聯合查詢SQL
- Mybatis【15】-- Mybatis一對一多表關聯查詢MyBatis