Nhibernate 查询语句CreateCriteria或HQL 实现竖向数据转为横向数据
0
悬赏园豆：100
[待解决问题]
我有两个数据库表，一个是Company：
| Company | CompanyID | CompanyName |
| 1 | 宝钢 | |
| 2 | 林木 | |
| 3 | 立纺 |
另一个是Workers：
| Workers | WorkersID | WorkersName | CompanyID | Index |
| 1 | 王发 | 1 | 1 | |
| 2 | 李大 | 1 | 2 | |
| 3 | 金常 | 2 | 1 | |
| 4 | 吴福 | 2 | 2 |
希望把一个公司的所有员工名称列出来，并且一个公司设定只有2个员工，结果如下：
| 要求的结果 | 公司序号 | 公司名称 | Index为1 | Index为2 |
| 1 | 宝钢 | 王发 | 李大 | |
| 2 | 林木 | 金常 | 吴福 | |
| 3 | 立纺 | null | null |
我用SQL语句这样写，可以实现：
SELECT c.CompanyID,
c.Name as cName,
(select Name from T_Worker where CompanyID = c.CompanyID and Index1=1) as wName1,
(select Name from T_Worker where CompanyID = c.CompanyID and Index1=2) as wName2
from T_Company c
现在我希望通过Nhibernate的查询语句来做，例如CreateCriteria，或者HQL。应该怎么写？
var results = session.CreateCriteria(typeof(Company), "Com")
.CreateAlias("Workers", "Wor")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("Com.ID"), "ID")
.Add(Projections.Property("Com.Name"), "ComName")
.Add(Projections.Property("Wor.Index1"), "WorIndex1")
.Add(Projections.Property("Wor.Name"), "WorName")
)
.AddOrder(Order.Asc("ID"))
.AddOrder(Order.Asc("kitName"))
.List();
我这样写，结果会有四条记录，罗列出
1 宝钢 王发 ，
1 宝钢 李大 ，
2 林木 金常 ，
2 林木 吴福 ，
而且第三个公司“立纺”也没有检索出来。
请高手指点，很急啊~~
问题补充：
也就是怎么用CreateCriteria 合并行
所有回答(2)
0
没有用过这样的，等待高手
0
1 public dynamic GetView() 2 { 3 Company company = null; 4 dynamic xlist = Session.QueryOver<Company>(() => company) 5 .SelectList 6 ( 7 list => GetSelect(list, company) 8 ) 9 .List<dynamic>(); 10 11 return xlist; 12 } 13 private dynamic GetSelect(NHibernate.Criterion.Lambda.QueryOverProjectionBuilder<Company> list, Company company) 14 { 15 int maxindex = Session.QueryOver<Workers>() 16 .Select( 17 Projections 18 .ProjectionList() 19 .Add(Projections.Max<Workers>(x => x.Index))) 20 .List<int>().First(); 21 //select CompanyID and CompanyName 22 list = list.Select(x => x.CompanyID).Select(x => x.CompanyName); 23 Workers workers = null; 24 //遍历index,添加列 25 for (int i = 1; i <= maxindex; i++) 26 { 27 var subq = QueryOver.Of<Workers>(() => workers).Where(() => workers.CompanyID == company.CompanyID).Where(() => workers.Index == i).Select(x => x.WorkersName); 28 list.SelectSubQuery(subq); 29 } 30 return list; 31 }
print:
1 StringBuilder sb = new StringBuilder(); 2 foreach (var line in coms.GetView()) 3 { 4 foreach (var item in line) 5 { 6 sb.Append(item+"-"); 7 } 8 sb.Append("\r\n"); 9 } 10 Console.WriteLine(sb);
生成的sql:
1 SELECT this_.CompanyID as y0_, 2 this_.CompanyName as y1_, 3 (SELECT this_0_.WorkersName as y0_ 4 FROM `Workers` this_0_ 5 WHERE this_0_.CompanyID = this_.CompanyID 6 and this_0_.Index = 1 /* ?p0 */) as y2_, 7 (SELECT this_0_.WorkersName as y0_ 8 FROM `Workers` this_0_ 9 WHERE this_0_.CompanyID = this_.CompanyID 10 and this_0_.Index = 2 /* ?p1 */) as y3_ 11 FROM `Company` this_
我使用的是mysql 生成的sql语法可能不一,Nhibernate版本:3.3.1.4000
