5817人阅读
C#程序小技巧（8）
Sql Server 2008（54）
Visual Studio 2008（15）
&&&&& Sql Server 有如下几种聚合函数SUM、AVG、COUNT、COUNT(*)、MAX 和 MIN，但是这些函数都只能聚合数值类型，无法聚合字符串。如下表:AggregationTable
如果想得到下图的聚合结果
利用SUM、AVG、COUNT、COUNT(*)、MAX 和 MIN是无法做到的。因为这些都是对数值的聚合。不过我们可以通过自定义函数的方式来解决这个问题。
1.首先建立测试表，并插入测试数据：
create table AggregationTable(Id int, [Name] varchar(10))
insert into AggregationTable
select 1,'赵' union all
select 2,'钱' union all
select 1,'孙' union all
select 1,'李' union all
select 2,'周'
2.创建自定义字符串聚合函数
Create FUNCTION AggregateString
RETURNS varchar(1024)
declare @Str varchar(1024)
set @Str = ''
select @Str = @Str + [Name] from AggregationTable
where [Id] = @Id
return @Str
3.执行下面的语句，并查看结果
select dbo.AggregateString(Id),Id from AggregationTable
group by Id
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1741002次
积分：15104
积分：15104
排名：第632名
原创：151篇
转载：27篇
译文：16篇
评论：870条
(1)(1)(1)(7)(1)(1)(1)(2)(1)(7)(4)(19)(6)(7)(5)(16)(7)(23)(10)(7)(11)(2)(1)(2)(1)(1)(3)(3)(12)(12)(11)(2)(1)(5)(1)不能对包含聚合或子查询的表达式执行聚合函数。解决办法 - Sql Server当前位置:& &&&不能对包含聚合或子查询的表达式执行聚合函数。解决不能对包含聚合或子查询的表达式执行聚合函数。解决办法&&网友分享于：&&浏览：298次不能对包含聚合或子查询的表达式执行聚合函数。select sum(case when QtyPrgPlanPKID_FKID_QtyID
in(select ListBudgetPKID from tbListBudget where ListBudgetName='801') then QtyPrgPlanPlanQty else 0 end) '801线路累计' from tbQtyPrgPlan--不能对包含聚合或子查询的表达式执行聚合函数。------解决方案--------------------用left join去累计吧！select sum(case when b.ListBudgetPKID is not null then a.QtyPrgPlanPlanQty&
else 0 end) '801线路累计'& from tbQtyPrgPlan a left join tbListBudget b&
on a.QtyPrgPlanPKID_FKID_QtyID = b.ListBudgetPKID and b.ListBudgetName='801'
------解决方案--------------------SQL code
--分别统计
select m.QtyPrgPlanPKID_FKID_QtyID , sum(QtyPrgPlanPlanQty) '801线路累计'
from tbQtyPrgPlan m , tbListBudget n
where m.QtyPrgPlanPKID_FKID_QtyID = n.ListBudgetPKID and n.ListBudgetName='801'
group by m.QtyPrgPlanPKID_FKID_QtyID
--总体统计
select sum(QtyPrgPlanPlanQty) '801线路累计'
from tbQtyPrgPlan m , tbListBudget n
where m.QtyPrgPlanPKID_FKID_QtyID = n.ListBudgetPKID and n.ListBudgetName='801'
12345678910
12345678910
12345678910 上一篇：下一篇：文章评论相关解决方案 12345678910 Copyright & &&版权所有关于SQL函数效率的一些测试与思考
在项目中我们经常能遇到有&一对多&的关系，比如下面两张表：
Class-Student就这样构成了一个简单的一对多关系。当然在实际的项目中，也可以再建立一张Relation表来保存他们之间的关系，在这里为了简单，就不做Relation表了。
现在在项目中，我需要将Class表中的数据list显示，当然也想显示选择了这门课的Student的StuName。也可以说是将一对多关系转换为一对一关系。我所期望的显示格式是这样的：
张三，李四，王五
张三，王五
要做到这一点并不难，大体有两种思路：
1、在数据库中写一个函数
2、在程序中获取表Class与表Student所有数据，然后对比ClassID
那么，那种方法效率比较高呢？于是我写了下面的代码来进行一个简单的测试
& View Code
&&& class Program
&&&&&&& static void Main(string[] args)
&&&&&&&&&&& Sql sql = new Sql();
&&&&&&&&&&& Stopwatch time1 = new Stopwatch();
&&&&&&&&&&& Stopwatch time2 = new Stopwatch();
&&&&&&& &&&&for (int j = 0; j & 10; j++)
&&&&&&&&&&& {
&&&&&&&&&&&&&&& time2.Start();
&&&&&&&&&&&&&&& for (int i = 0; i & 1000; i++)
&&&&&&&&&&&&&&& {
&&&&&&&&&&&&&&&&&&& string sql1 = &select ID,[StuName],[ClassID] FROM [Student]&;
&&&&&&&&&&&&&&&&&&& string sql2 = & SELECT& ID,ClassName from Class&;
&&&&&&&&&&&&&&&&&&& List&string& item = new List&string&();
&&&&&&&&&&&&&&&&&&& string bl=&&;
&&&&&&&&&&&&&&&&&&& DataTable dt1 = sql.getData(sql1);
&&&&&&&&&&&&&&&&&&& DataTable dt2 = sql.getData(sql2);
&&&&&&&&&&&& &&&&&&&foreach (DataRow dtRow2 in dt2.Rows)
&&&&&&&&&&&&&&&&&&& {
&&&&&&&&&&&&&&&&&&&&&&& foreach (DataRow dtRow1 in dt1.Rows)
&&&&&&&&&&&&&&&&&&&&&&& {
&&&&&&&&&&&&&&&&&&&&&&&&&&& if (dtRow1[&ClassID&].ToString() == dtRow2[&ID&].ToString())
&&&&&&&&&&&&& &&&&&&&&&&&&&&{
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& bl+=dtRow1[&StuName&].ToString()+&,&;
&&&&&&&&&&&&&&&&&&&&&&&&&&& }&&&
&&&&&&&&&&&&&&&&&&&&&&& }
&&&&&&&&&&&&&&&&&&&&&&& item.Add(bl);
&&&&&&&&&&&&&&&&&&&&&&& bl = &&;
&&&&&&&&&&&&&&&&&&& }
&&&&&&&&&&&&&&& }
&&&&&&&&&&&&&&& time2.Stop();
&&&&&&&&&&&&&&& Console.WriteLine(time2.Elapsed.ToString());
&&&&&&&&&&&&&&& time1.Start();
&&&&&&&&&&&&&&& for (int i = 0; i & 1000; i++)
&&&&&&&&&&&&&&& {
&&&&&&&&&&&&&&&&&&& string sql3 = &SELECT C.ID, C.ClassName, dbo.f_getStuNamesByClassID(C.ID)as stuName FROM Class C&;
&&&&&&&&&&&&&&&&&&& DataTable dt = sql.getData(sql3);
&&&&&&&&&&&&&&& }
&&&&&&&&&&&&&&& time1.Stop();
&&&&&&&&&&&&&&& Console.WriteLine(time1.Elapsed.ToString());
&&&&&&&&&&&&&&& float index = (float)time1.Elapsed.Ticks / (float)time2.Elapsed.T
&&&&&&&&&&&&&&& Console.WriteLine(&效率比& + index.ToString());
&&&&&&&&&&&&&&& Console.WriteLine(&=============================&);
&&&&&&&&&&& }
&&&&&&&&&&& Console.ReadLine();
& View Code
&&& class Sql
&&&&&&& public DataTable getData(string sql)
&&&&&&&&&&& SqlConnection conn = new SqlConnection();
&&&&&&&&&&& conn.ConnectionString = &Data Source=.\\SQLEXPRESS;Initial Catalog=TUser Id=Password=1;&;
&&&&&&&&&&& SqlCommand comm = new SqlCommand(sql, conn);
&&&&&&&&&&& conn.Open();
&&&&&&&&&&& SqlDataAdapter da = new SqlDataAdapter(comm);
&&&&&&&&&&& DataSet ds = new DataSet();
&&&&&&&&&&& da.Fill(ds, &ds&);
&&&&&&&&&&& conn.Close();
&&&&&&&&&&& return ds.Tables[0];
& View Code
--根据课程ID，返回选此课程的学生的名字，以逗号隔开
ALTER function [dbo].[f_getStuNamesByClassID] (@ID int)
RETURNS nvarchar(50)
&&& declare @Result nvarchar(50);
&&& declare @stuName nvarchar(50);
&&& Set @Result='';
&&& declare cur cursor for
&&&&&&& SELECT S.StuName FROM Class C
&&&&&&& LEFT JOIN Student S ON C.ID=S.ClassID
&&&&&&& WHERE C.ID=@ID
&&& fetch next from cur into @stuN
&&& while(@@fetch_status=0)
&&&&&&& set @Result=@Result+@stuName+',';
&&&&&&& fetch next from cur into @stuN
--去除最后多余的一个逗号
&&& IF @Result && ''
&&&&&&& SET @Result=SUBSTRING(@Result, 1, LEN(@Result)-1);
&&&&&&& SET @Result=NULL;
&&& return @R
测试结果如下：
00:00:00.5466790
00:00:00.7753704
效率比1.418329
=============================
00:00:01.0251996
00:00:01.5594629
效率比1.521131
=============================
00:00:01.5124349
00:00:02.3286227
效率比1.539652
=============================
00:00:01.9882458
00:00:03.1007960
效率比1.559564
=============================
00:00:02.4476305
00:00:03.8717636
效率比1.581842
=============================
00:00:02.9129007
00:00:04.6332828
效率比1.590608
=============================
00:00:03.4006140
00:00:05.3971930
效率比1.587123
=============================
00:00:03.8655281
00:00:06.2574500
效率比1.618783
=============================
00:00:04.4532249
00:00:07.0674710
效率比1.587046
=============================
00:00:04.9540083
00:00:07.8596999
效率比1.586533
=============================
分析一下测试结果，不难发现每一个一千次所用的时间基本符合一个等差数列。当然第一个一千次由于要初始化，所以显得慢一些。
总体来说，在程序中用处理一对多关系，比在数据库中用函数处理效率要高35%这样。
那么如果我们在Student表中再添加一行这样的数据：
ID&&&& StuNameClassID
6&&&&&& 李四&&&&&&&& 3
测试结果如下：
00:00:00.5519228
00:00:00.8206084
效率比1.486817
=============================
00:00:01.0263686
00:00:01.5813210
效率比1.540695
=============================
00:00:01.4886327
00:00:02.3516000
效率比1.579705
=============================
00:00:01.9807901
00:00:03.1495472
效率比1.590046
=============================
00:00:02.4613411
00:00:03.9278171
效率比1.595804
=============================
00:00:02.9246678
00:00:04.6961790
效率比1.605714
=============================
00:00:03.3911521
00:00:05.5018374
效率比1.62241
=============================
00:00:03.8737490
00:00:06.2716150
效率比1.619004
=============================
00:00:04.4047347
00:00:07.1796579
效率比1.629986
=============================
00:00:04.8688508
00:00:07.9477787
效率比1.632372
=============================
发现添加数据之后，效率比进一步加大
环境：vs2008,sql 2005
总结：根据测试结果来说，对于大规模高并发的数据库操作（在这里是10次循环，每次1000次读取数据），我们应该尽可能的避免使用数据库函数，而应该将数据全部取出来，在程序中进行处理
写在最后的话：对于我的程序、代码、思路等等一切的一切有不同见解者，欢迎留言讨论。这是我的第一篇博客，希望大家多多支持，如有不足望海涵
作者 CrazyJinn