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条件时正常,如果加条件,就存在问题,如下图所示: