62,052
社区成员
发帖
与我相关
我的任务
分享
select 承办人=isnull(承办人,'总计'),
SUM(CASE 二审结果 when '改判' THEN 1 ELSE 0 END) as 改判数,
SUM(CASE 二审结果 when '重审' THEN 1 ELSE 0 END) as 重审数,
SUM(CASE 二审结果 when '重审' THEN 1 ELSE 1 END) as 改判总数
from 案件
group by 承办人 with rollup
create table 案件
(
ID int identity(1,1) primary key not null,
一审案号 varchar(50) not null,
二审案号 varchar(50) not null,
上诉人 varchar(50) not null,
被上诉人 varchar(50) not null,
承办人 varchar(50) not null,
二审结果 varchar(20) not null
);
insert into 案件 values('05胶民初1721','07青民一终626','山东鑫发房地产开发公司','门松杰、青岛胶东建筑工程有限公司','赵燕','改判');
insert into 案件 values('07胶民初560','07青民一终1132','李瑞岗','邱大鹏','钟永全','重审');
insert into 案件 values('08胶民初2756','11青民一终1447','田波','胶州市民政局、胶州市公安局、汪磊','郑培臣','重审');
insert into 案件 values('09胶民初1690','11青民五终948','杨广波','唐素芳','尹欣芳','改判');
insert into 案件 values('09胶民初2910','11青民四终58','贾培山','青岛市金潮特种混凝土制品有限责任公司','周建波','改判');
insert into 案件 values('09胶民初739','11青民四终94','青岛太勋钢管有限公司','苏延来','张洪森','改判');
insert into 案件 values('09胶民初781','10鲁民提492','孙光德','山东大唐植物素有限公司','张洪森','改判');
insert into 案件 values('10胶民初1044','10青少民终208','李娜、初晓婷','楚宝荣','谢本宏','改判');
insert into 案件 values('10胶民初197','11青民五终527','王玉美、王卫东、王秀丽、王卫兵','青岛德信物业管理有限公司、林宝华','尹欣芳','改判');
insert into 案件 values('10胶民初2979','11青民五终528','周脉通、林光之','周脉通、林光之','谢本宏 ','改判');
insert into 案件 values('10胶民初3695','11青民五终860','辛建刚','辛建亭','尹欣芳','改判');
insert into 案件 values('10胶民初468','10青民一终1972','胶州市阜安街道办事处孙家岭村民委员会','杨佰有','郑培臣','重审');
--查询语句
select 承办人,
SUM(CASE 二审结果 when '改判' THEN 1 ELSE 0 END) as 改判数,
SUM(CASE 二审结果 when '重审' THEN 1 ELSE 0 END) as 重审数,
SUM(CASE 二审结果 when '重审' THEN 1 ELSE 1 END) as 改判总数
from 案件
group by 承办人;
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="Horizontal"
ShowFooter="True" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="false">
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:TemplateField HeaderText="序号">
<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="承办人" HeaderText="承办人" ReadOnly="True" />
<asp:BoundField DataField="改判数" HeaderText="改判数" ReadOnly="True" />
<asp:BoundField DataField="重审数" HeaderText="重审数" ReadOnly="True" />
<asp:BoundField DataField="改发总数" HeaderText="改发总数" ReadOnly="True" />
</Columns>
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
/// <summary>
/// 绑定案件数据
/// </summary>
public void BindGridView()
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection("Data Source=localhost;uid=sa;pwd=111111;Database=CSDN"))
{
con.Open();
string strSQL = "select 承办人,SUM(CASE 二审结果 when '改判' THEN 1 ELSE 0 END) as 改判数,SUM(CASE 二审结果 when '重审' THEN 1 ELSE 0 END) as 重审数, SUM(CASE 二审结果 when '重审' THEN 1 ELSE 1 END) as 改发总数 from 案件 group by 承办人";
using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, con))
{
adapter.Fill(ds);
}
}
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
}
private int sum1 = 0; //改判总数
private int sum2 = 0; //重审总数
private int sum3 = 0; //改发总数
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowIndex > -1)
{
sum1 += int.Parse(e.Row.Cells[2].Text);
sum2 += int.Parse(e.Row.Cells[3].Text);
sum3+= int.Parse(e.Row.Cells[4].Text);
}
else if (e.Row.RowType == DataControlRowType.Footer) // 判断当前项是否为页脚
{
e.Row.Cells[1].Text = "总计:";
e.Row.Cells[2].Text = sum1.ToString();
e.Row.Cells[3].Text = sum2.ToString();
e.Row.Cells[4].Text = sum3.ToString();
}
}
}