select 地区,SUM(CASE 年份 WHEN 1995 THEN 1 ELSE 0 END) as total_1995,SUM(CASE 年份 WHEN 1996 THEN 1 ELSE 0 END) as total_1996 FROM TABLE GROUP BY 地区这种方式能够实现特定年份的数据 你可以在代码里面使用字符窜连接 dim strSQL as string for i = 1995 to DateTime.Now.Year() strSQL = strSQL & "SUM(CASE 年份 WHEN '" & 1995 & "' THEN 1 ELSE 0 END) as total_" & i & " ," next
select * from (select count(no) as n1 from a001 where no = '1') as a, (select count(no) as n2 from a001 where no = '2') as b, (select count(no) as n3 from a001 where no = '3') as c, (select count(no) as n4 from a001 where no = '4') as d
begin transaction declare @sql varchar(200) declare @sql1 varchar(500) set @sql1='insert into tmp1 values (' set @sql= 'create table tmp1 (' select @sql=@sql+'A'+convert(varchar(4),i)+' int,' from ( select i from #tmp group by i ) a select @sql1=@sql1+convert(varchar(4),num)+',' from ( select count(i) as num from #tmp group by i ) a set @sql=left(@sql,len(@sql)-1)+')' set @sql1=left(@sql1,len(@sql1)-1)+')' exec(@sql) exec(@sql1) select * from tmp1 rollback transaction 第二就是楼上的方法,自己先算出统计的值,然后插入到自己构造的表中,最后绑定在datagrid.还有就是用水晶报表来生成交叉报表。我做过这种报表,有源码,如果你不嫌难看,我可以贴出来给你,但要看明白可能比较费劲。是用第二种方式实现的。
你可以在代码里面使用字符窜连接
dim strSQL as string
for i = 1995 to DateTime.Now.Year()
strSQL = strSQL & "SUM(CASE 年份 WHEN '" & 1995 & "' THEN 1 ELSE 0 END) as total_" & i & " ,"
next
有两种方式可以解决你的问题
一是用存储过程:
例如:表有字段no,问怎样分类统计出不同no的记录条数,并且要横向排列,要的结果如下:
no的值 1 2 3 4
记录条数 20 15 26 50
---------------------------------------------------------------
select no,sum(no) from table_name group by no
---------------------------------------------------------------
select * from
(select count(no) as n1 from a001 where no = '1') as a,
(select count(no) as n2 from a001 where no = '2') as b,
(select count(no) as n3 from a001 where no = '3') as c,
(select count(no) as n4 from a001 where no = '4') as d
---------------------------------------------------------------
begin transaction
declare @sql varchar(200)
declare @sql1 varchar(500)
set @sql1='insert into tmp1 values ('
set @sql= 'create table tmp1 ('
select @sql=@sql+'A'+convert(varchar(4),i)+' int,' from
(
select i from #tmp group by i
) a
select @sql1=@sql1+convert(varchar(4),num)+',' from
(
select count(i) as num from #tmp group by i
) a
set @sql=left(@sql,len(@sql)-1)+')'
set @sql1=left(@sql1,len(@sql1)-1)+')'
exec(@sql)
exec(@sql1)
select * from tmp1
rollback transaction 第二就是楼上的方法,自己先算出统计的值,然后插入到自己构造的表中,最后绑定在datagrid.还有就是用水晶报表来生成交叉报表。我做过这种报表,有源码,如果你不嫌难看,我可以贴出来给你,但要看明白可能比较费劲。是用第二种方式实现的。
谢谢,如果您能贴出来最好了
姓名/考勤 | 迟到 | 事假 | 出差 | 考勤总天数|
张三 | 3 | 2 | 3 | 8 |
李四 | 1 | 1 | 1 | 3 |基本的原理就是自己构造一个datatabel,然后绑定到datagrid
public class ty_check_work_month_report
{
public ty_check_work_month_report()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public DataTable month_report(string str_date_area)
{
//检索所有的人员及这个月的所有考勤状态
DataTable dt_result=new DataTable();
//考勤状态列
StringBuilder sb_sel_check_state=new StringBuilder("select distinct vc_state_name + '(' + vc_state_unit + ')' as vc_state from report where ");
sb_sel_check_state.Append(str_date_area);
string str_sel_check_state=sb_sel_check_state.ToString();
//用户列
string str_sel_user="select vc_fact_name,int_user_id from ty_user";
cyc_data cd_month_report=new cyc_data();
SqlConnection scn_result=cyc_data.scn_databaseconnection;
SqlDataAdapter sda_user=new SqlDataAdapter(str_sel_user,scn_result);
SqlDataAdapter sda_check_state=new SqlDataAdapter(str_sel_check_state,scn_result);
DataSet ds_result=new DataSet();
sda_user.Fill(ds_result,"tb_user");
sda_check_state.Fill(ds_result,"tb_check_state");
//构建datatable
//构建姓名列
dt_result.Columns.Add("姓名/考勤",System.Type.GetType("System.String"));
//构建所有的考勤状态列
for (int i=0;i<ds_result.Tables["tb_check_state"].Rows.Count;i++)
{
dt_result.Columns.Add((string)(ds_result.Tables["tb_check_state"].Rows[i][0]),Type.GetType("System.Int16"));
}
//构建考勤总天数列
dt_result.Columns.Add("考勤总天数",Type.GetType("System.String"));
//填充datatable
for (int j=0;j<ds_result.Tables["tb_user"].Rows.Count;j++)
{
//填姓名
DataRow dr_state=dt_result.NewRow();
dr_state[0]=(string)ds_result.Tables["tb_user"].Rows[j][0];
//统计某一状态的考勤统计数
for (int i=0;i<ds_result.Tables["tb_check_state"].Rows.Count;i++)
{
dr_state[i+1]=month_state_total(ds_result.Tables["tb_user"].Rows[j][1].ToString(),str_date_area,dt_result.Columns[i+1].Caption.ToString());
}
//考勤总天数
dr_state[ds_result.Tables["tb_check_state"].Rows.Count+1]=month_check_total(ds_result.Tables["tb_user"].Rows[j][1].ToString(),str_date_area);
dt_result.Rows.Add(dr_state);
}
return dt_result;
}
//统计某用户的月考勤总天数
private int month_check_total(string str_user_id,string str_date_area)
{
StringBuilder sb_check_total=new StringBuilder("select distinct Datepart(dd,dt_check_datetime) as check_date from report where ");
sb_check_total.Append("int_user_id=");
sb_check_total.Append(str_user_id);
sb_check_total.Append(" and ");
sb_check_total.Append(str_date_area);
string str_check_total=sb_check_total.ToString();
cyc_data cd_check_total=new cyc_data();
DataSet ds_check_total=cd_check_total.create_dataset("tb_check_total",str_check_total);
int int_total=ds_check_total.Tables["tb_check_total"].Rows.Count;
return int_total;
} //各种考勤状态的月考勤统计
private int month_state_total(string str_user_id,string str_date_area,string str_state_name)
{
StringBuilder sb_state_total=new StringBuilder("select sum(Expr1) as state_total from report where int_user_id=");
sb_state_total.Append(str_user_id);
sb_state_total.Append(" and vc_state_name=substring('");
sb_state_total.Append(str_state_name);
sb_state_total.Append("',1,charindex('(','");
sb_state_total.Append(str_state_name);
sb_state_total.Append("',1)-1)");
sb_state_total.Append(" and ");
sb_state_total.Append(str_date_area);
string str_state_total=sb_state_total.ToString();
cyc_data cd_state_total=new cyc_data();
SqlConnection scn_state_total=cyc_data.scn_databaseconnection;
SqlCommand scd_state_total=new SqlCommand(str_state_total,scn_state_total);
if (scn_state_total.State==ConnectionState.Closed)
{
scn_state_total.Open();
}
int int_state_total;
if (scd_state_total.ExecuteScalar() is DBNull)
{
int_state_total=0;
}
else
{
int_state_total=Convert.ToInt16(scd_state_total.ExecuteScalar());
}
scn_state_total.Close();
return int_state_total;
}
}
最后一个问题
以前用ado时,用rs["列名"]可以访问某条记录的某列的值
用ado.net,生成dataset后,该如何访问某条记录的某列的值小弟实有困惑
ds_result.Tables["tb_check_state"].Rows[i][0]
其中ds_result为dataset对象,它可以包含多个表,通过tables集合来访问其中的某张表,表下面是行集,也就是rows对象,行下面就是columns集合(列集合),可以通过rows[行序号][列序号]来访问。
例如你要得到dataset中第一张表第一行第一列的数据,可以这样写:
变量=dataset.Tables[0].Rows[0][0]ADO.NET 结构请参见msdn:
ms-help://MS.VSCC/MS.MSDNVS.2052/cpguide/html/cpconadonetarchitecture.htm
及ADO.NET 帮助:
ms-help://MS.VSCC/MS.MSDNVS.2052/cpguide/html/cpconaccessingdatawithadonet.htm