请问在Linq to sql 中,怎么使用 sql 语句中的 max(case when ... then ... else ... end) 语法？
2
悬赏园豆：20
[已解决问题]
解决于 2012-05-22 10:52
以下是原Sql语句:
dglist = GetExamInfo(); //获取试题信息 for (int i = 0; i < dglist.Count; i++) { //其中 PExamName 、PState 是 PerScore 表中的字段 sqlstr += " max(case when PExamName='" + dglist[i].HExamName + "' then PState else null end)'" + dglist[i].HExamName + "',"; } if (sqlstr.Length > 0) sqlstr = sqlstr.Substring(0, sqlstr.Length - 1); sqlstr = " select PTName '队名', SUM(POkScore+PAdjustScore) '总分'," + sqlstr + " from PerScore where PHallID='" + Guid.Parse(Session["hid"].ToString()) + "' group by PTID,PTName order by '总分' desc ";
以下是上面sql语句执行时的语句：（假设dglist有三条记录，分别是“合并利润表”、“利润表试题”、“资金日报表”）
select PTName '队名', SUM(POkScore+PAdjustScore) '总分', max(case when PExamName='合并利润表' then PState else null end)'合并利润表', max(case when PExamName='利润表试题' then PState else null end)'利润表试题', max(case when PExamName='资金日报表' then PState else null end)'资金日报表', from PerScore where PHallID='eecb075c-3862-4519-b966-7e9e4f287890' group by PTID,PTName order by '总分' desc
现在我希望把上面这个sql语句转换成linq to sql ，谢谢！
注：红色PerScore表示数据表，咖啡色注明的表示是这张表的字段。
最佳答案
0
在LINQ TO SQL里使用行转列倒是没用过，不过，你可以在数据库服务器定义一个视图，然后再建立模型对象。
此外，也可以通过DATACONTEXT来执行一段SQL语句，获取执行后的数据，只是此时不再是MODEL，需要你自己再处理。
收获园豆：18
用传统的sql倒是可以实现，而且已经实现了此功能。但是唯一感觉不足的是整个系统其它的都是调用了WCF服务里面提供的方法(那里面的方式实现全部为Linq to sql 实现的)，而我这个现在要单独整个DBHelper去查询数据库，感觉很不协调啊。
@Technology:
1——
在LINQ TO SQL里也可以运行SQL语句，获得数据集对象。
2——
你可以使用我前面说的，创建一个VIEW来实现这个行转列，这样，你就又可以使用LINQ TO SQL里的ENTITY了。
要注意的是：行转列的VIEW不要允许修改。
刚使用LINQ TO SQL，实现了这个行转列：
using (DataClasses1DataContext ctx = new DataClasses1DataContext()) { var list = from c in ctx.成绩单s group c by c.姓名 into g select new { 姓名=g.Key, 数学 = (from d in g where d.科目 == "数学" select d.成绩).Max(), 语文 = (from d in g where d.科目 == "语文" select d.成绩).Max(), 英语 = (from d in g where d.科目 == "英语" select d.成绩).Max(), }; var array = list.ToArray(); }
数据表内容为：
数据查询结果为：
@Technology: 对list进行ToString获得生成的SQL脚本：
SELECT [t1].[姓名], ( SELECT MAX([t2].[成绩]) FROM [dbo].[成绩单] AS [t2] WHERE ([t2].[科目] = @p0) AND ([t1].[姓名] = [t2].[姓名]) ) AS [数学], ( SELECT MAX([t3].[成绩]) FROM [dbo].[成绩单] AS [t3] WHERE ([t3].[科目] = @p1) AND ([t1].[姓名] = [t3].[姓名]) ) AS [语文], ( SELECT MAX([t4].[成绩]) FROM [dbo].[成绩单] AS [t4] WHERE ([t4].[科目] = @p2) AND ([t1].[姓名] = [t4].[姓名]) ) AS [英语] FROM ( SELECT [t0].[姓名] FROM [dbo].[成绩单] AS [t0] GROUP BY [t0].[姓名] ) AS [t1]
这个脚本跟期望的脚本：
select [姓名] ,max(case [科目] when '语文' then [成绩] else null end) as [语文] ,max(case [科目] when '数学' then [成绩] else null end) as [数学] ,max(case [科目] when '英语' then [成绩] else null end) as [英语] from [成绩单] group by [姓名]
差别很大，从性能的角度来看，很不好。
@Technology: 下面是通过直接执行查询来得到结果：
var list = ctx.ExecuteQuery<成绩单1>(@"select [姓名] ,max(case [科目] when '语文' then [成绩] else null end) as [语文] ,max(case [科目] when '数学' then [成绩] else null end) as [数学] ,max(case [科目] when '英语' then [成绩] else null end) as [英语] from [成绩单] group by [姓名]");
这里的实体“成绩单1”有4个属性，分别是姓名、语文、数学、英语，同时，语文、数学、英语映射到同名的字段中：
public class 成绩单1 { private string _姓名; private int _语文; private int _数学; private int _英语; public 成绩单1() { } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_姓名", DbType="NVarChar(50) NOT NULL", CanBeNull=false)] public string 姓名 { get { return this._姓名; } set { if ((this._姓名 != value)) { this._姓名 = value; } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_语文", DbType="Int NOT NULL")] public int 语文 { get { return this._语文; } set { if ((this._语文 != value)) { this._语文 = value; } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_数学", DbType="Int NOT NULL")] public int 数学 { get { return this._数学; } set { if ((this._数学 != value)) { this._数学 = value; } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_英语", DbType="Int NOT NULL")] public int 英语 { get { return this._英语; } set { if ((this._英语 != value)) { this._英语 = value; } } } }
这里，我把“成绩单1”的表映射、partial修饰删除了（事实上，我是直接把数据库表成绩单拖过来后进行修改得到的）。
@笨笨蜗牛: 朋友，我现在按照你说的在WCF端直接执行Sql语句的方法时，出现了一个问题。因为我的WCF服务是在其它电脑上的（也就算是服务器吧）。
public List<NewPersonal> GetPersonalScoreBySql(string str)
{
using (var db = new ComDataContext())
{
var result = db.ExecuteQuery<NewPersonal>(@"" + str + "").ToList();
return result;
}
}
调用的时候，我是这样
var list = csc.GetPersonalScoreBySql(sqlstr); //其中csc是服务的Client，sqlstr就是以前那句拼接好的sql语句。
但是我设断点执行到这步之后。vs就提示“无法自动进入并单步执行服务器。未能连接到服务器计算机 192.168.2.69 ....” 因为这台电脑设置了密码的。数据库也是配置在这台电脑上的。为什么其他WCF方法都可以对数据库执行操作。而我现在这个方法就是用 db.ExecuteQuery<NewPersonal>(@"" + str + "").ToList(); 这种形式就不行了呢？盼回复，谢谢!
@Technology: 检查你的数据是否可以序列化。
你可以做个简单的实验：
在服务端返回一个长度为0的数据集合或者返回一个只有一个元素的数据集合，这个数据集合直接用new的方式构造（数据为空和模拟来自数据库），同时再做一个实验：另外写一个方法，让这个方法就返回一个元素对象，对象的构造也使用前面两个方案，检查这些实验的异同。
@笨笨蜗牛: 找到上个问题的原因了，是因为我在本地修改的服务方法，没有提交到服务器上，而我本地却调用的是服务器上的方法。服务器上现在没有那个方法，所以才出现的那个问题。
现在又遇到了一个问题，蜗牛。我看你给我的例子是
var list = ctx.ExecuteQuery<成绩单1>(@"select [姓名] ,max(case [科目] when '语文' then [成绩] else null end) as [语文] ,max(case [科目] when '数学' then [成绩] else null end) as [数学] ,max(case [科目] when '英语' then [成绩] else null end) as [英语] from [成绩单] group by [姓名]");
你这个实体类“成绩单1”，在上个语句中每个属性都有所包括了，而且分组是按照“姓名”来分的组。这样应该没什么问题。但是，我这个实体类有点特别，我查询的时候，没有把它的主键分组(加上主键分组的话，就会出现很多冗余数据)。而只是查的这个实体类的部分字段。结果执行到这句代码时 var result = db.ExecuteQuery<NewPersonal>(@"" + str + "").ToList(); ，就抛出了 我这张表 NewPersonal 的主键“ PerID 在结果中不存在”的错误信息。 盼回复，谢谢！
@笨笨蜗牛: 蜗牛，我估计找到原因了，我先测试下，待会告诉你。
var result = db.ExecuteQuery<NewPersonal>(@"" + str + "").ToList(); 这个result的类型就不能是 NewPersonal 类型了，是吧。
@Technology: 这个“成绩单1”是一个虚拟实体，也就是行转列后的实体，如果你进行操作，这个实体对象的各属性也应该是只读的，而且生成的实体数据集合也不应该允许添加、删除的。
“成绩单”实体就是原本数据库里的实体，未行转列。
假如你希望对“成绩单1”的属性修改映射到数据库的修改，这个会复杂点，简单的一个实现就是通过对“成绩单1”进行控制来实现。至于流水号ID，那是对“成绩单”来说有意义，而对于“成绩单1”来说是没有意义的，此处，已经按照“姓名”来分组了，“姓名”才是“成绩单1”的主键。
@笨笨蜗牛: 求助，蜗牛。
public List<NewPersonalForExam> GetPersonalBySql(string str)
{
using (var db = new ComDataContext())
{
var result = db.ExecuteQuery<NewPersonalForExam>(@"" + str + "").ToList();
return result;
}
}
这里result 的记录条数倒是正确了，但是每条记录值都为null(我想应该是在哪里给它赋值呢)，NewPersonalForExam这个类是我自己新定义的，只包含了数据库中某张表的部分字段(因为只需要这几个字段)。 而如果我把NewPersonalForExam这里替换成与数据库对应的实体时，因为这个实体还有很多字段，而我这里没有完全用到。所以就报了上面我给你说的那个错误。盼回复，谢谢！
@Technology: 应该是你没对实体类型的属性与查询出来的数据字段进行映射导致的。
把你的类和SQL字符串贴出来看看。
@笨笨蜗牛: 蜗牛加QQ聊吧。这样有点麻烦，503697906
其他回答(1)
