表结构:
姓名 学号 系别 登记时间 导师
张三
李四都是字符类型的字段现在要按系别和导师分别来统计2006.3-2006.5之间登记的记录条数结果结构如下:
计算机 数学 外语 李四 张三 总计
最好能够行列转换下:
计算机
数学
外语
李四
张三
总计
以便导出EXCEL表
姓名 学号 系别 登记时间 导师
张三
李四都是字符类型的字段现在要按系别和导师分别来统计2006.3-2006.5之间登记的记录条数结果结构如下:
计算机 数学 外语 李四 张三 总计
最好能够行列转换下:
计算机
数学
外语
李四
张三
总计
以便导出EXCEL表
解决方案 »
- 修改数据时总报错误 CS0117: “Admin_ModifyNews”并不包含“NewTitle”的定义
- datagrid如何获取当前行选中事件
- 编译问题 求教
- 装了MapXtreme,有许可文件,但运行程序仍然出现如下问题。。。。求解!!急!!
- 用VS2008开发时采用application的模式开发web网站系统
- 超时提醒的问题
- 多语言版本的数据表设计疑问
- asp 调试问题
- 关于showdialog 的奇怪的问题???(是高手的请进!)
- wpf中word文件转xps文件报转换后的xps文件路径找不到
- 如何将dataview中排序筛选后的结果表导出到一个Table中呢?
- visual studio.net运行环境提示……
用一句SQL语句很难实现。最好搞个存储过程
from tablename a
join (select 系别,count(*) as 'sl'from tablename group by 系别) b
on a.系别= b.系别这是只按系别统计的情况,按导师的我这类似,如果是要将两个结果联合一起的话,可以使用all函数,将两个结果集联合起来
insert #test1 values('Eric1','001','数学','2006-5-5','张三')
insert #test1 values('Eric2','002','计算机','2002-5-5','李四')
insert #test1 values('Eric3','003','数学','2006-3-5','张三')
insert #test1 values('Eric4','004','计算机','2004-5-5','张三')
insert #test1 values('Eric5','005','数学','2006-5-5','李四')
insert #test1 values('Eric6','006','外语','2006-3-5','张三')
insert #test1 values('Eric7','007','数学','2006-4-5','李四')
select xibie as 'xx',count(*) from #test1
where ltime between '2006-3-1' and '2006-5-1'
group by xibie
union
select teacher,count(*) from #test1
where ltime between '2006-3-1' and '2006-5-1'
group by teacher
select xibie as 'xx',count(*) from #test1
where ltime between '2006-3-1' and '2006-5-1'
group by xibie
union all
select teacher,count(*) from #test1
where ltime between '2006-3-1' and '2006-5-1'
group by teacher
有没别的方法啊?
select sum(case when xibie='计算机' then 1 else 0 end) as open from dengjixinxi
这种语句行不行?
from tablename a
join (select 系别,count(*) as 'sl'from tablename group by 系别) b
on a.系别= b.系别 union all
select 0,0,c.导师 as '导师,d.sl
from tablename c
join (select 导师,count(*) as 'sl'from tablename group by 导师) d
on c.导师= d.导师
select a.系别 as '系别',b.sl
from tablename a
join (select 系别,count(*) as 'sl'from tablename group by 系别) b
on a.系别= b.系别可以。
下面加个union
select a.导师 as '导师',b.sl
from tablename a
join (select 导师,count(*) as 'sl'from tablename group by 导师) b
on a.导师= b.导师即可。不要加all,all是包含所有行,包括重复行。你的好象不需要!
有没别的方法啊?
select sum(case when xibie='计算机' then 1 else 0 end) as open from dengjixinxi
这种语句行不行?我的insert只是为了演示而填充数据而已
行 152: this.oleDbDataAdapter1.SelectCommand.ExecuteNonQuery();
行 153: this.oleDbDataAdapter1.Fill(this.dataSet1);
行 154: this.DataGrid1.DataBind();
行 155: this.oleDbConnection1.Close();
System.Data.ConstraintException: 未能启用约束。一行或多行中包含违反非空、唯一或外键约束的值。
而且是orcale环境
在VS.NET里就是运行不起来
作者:德仔
用途:用来统计站上所有的信息总数,包括新闻,产品,公司,等的总数
日期:2006-3-23
*/CREATE procedure datacount
as
declare @MemberCount int
declare @MemberVip int
declare @MemberNorm int
declare @MemberUnchkReg int
declare @MemberLblRegChk intdeclare @CompanyCount int
declare @CompanyRcmd intdeclare @SellCount int
declare @SellRcmd int
declare @SellUnchk int
declare @SellChk intdeclare @CountSupply int
declare @SupplyRcmd int
declare @SupplyUnchk int
declare @SupplyChk intdeclare @NewsCount int
declare @NewsRcmd int
declare @NewsClassCount intdeclare @SupplyClass int
declare @SellClass int
declare @MsgCount intdeclare @ProBigclass int
declare @proSmallclass intselect @MemberCount= count(User_Id)from Users
select @MemberVip=count(User_Id)from Users where User_Level =2
select @MemberNorm=count(User_Id)from Users where User_Level =1
select @MemberUnchkReg=count(user_id) from users where user_IsChecked=0
select @MemberLblRegChk=count(user_id) from users where user_IsChecked=1select @CompanyCount=count(COM_id) from Company
select @CompanyRcmd=count(COM_id) from Company where COM_IsRcmd=1select @SellCount =count(Sell_Id) from sellinfo
select @SellRcmd =count(Sell_Id) from sellinfo where Sell_IsRcmd=1
select @SellUnchk =count(Sell_Id) from sellinfo where Sell_Ischecked = 0
select @SellChk =count(Sell_Id) from sellinfo where Sell_Ischecked = 1select @CountSupply =count(Supply_Id)from supplyInfo
select @SupplyRcmd =count(Supply_Id)from supplyInfo where Supply_Isrcmd=1
select @SupplyUnchk =count(Supply_Id)from supplyInfo where Supply_Ischecked=0
select @SupplyChk =count(Supply_Id)from supplyInfo where Supply_Ischecked=1select @NewsCount =count(news_id) from news
select @NewsRcmd =count(news_id) from news where News_Recommand=1
select @NewsClassCount =count(news_id) from newsselect @proBigclass = count(PdtCat_SortId) from productcats where PdtCat_SortId=0
select @proSmallClass = count(PdtCat_SortId)from productcats where PdtCat_SortId<>0select @MsgCount = count(Msg_id) from MSg
select
MemberCount=@MemberCount,
MemberVip=@MemberVip,
MemberNorm=@MemberNorm,
MemberUnchkReg=@MemberUnchkReg,
MemberLblRegChk=@MemberLblRegChk,
CompanyCount=@CompanyCount,
CompanyRcmd=@CompanyRcmd,
SellCount=@SellCount,
SellRcmd=@SellRcmd,
SellUnchk=@SellUnchk,
SellChk=@SellChk,
CountSupply =@CountSupply,
SupplyRcmd =@SupplyRcmd,
SupplyUnchk=@SupplyUnchk,
SupplyChk =@SupplyChk,
NewsCount=@NewsCount,
NewsRcmd=@NewsRcmd,
NewsClassCount=@NewsClassCount,
probigclass=@probigclass,
prosmallclass=@prosmallclass,
MsgCount = @MsgCount
GO