首页 新闻 搜索 专区 学院

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 合并行

笋儿的主页 笋儿 | 初学一级 | 园豆:102
提问于:2013-04-26 11:24
< >
分享
所有回答(2)
0

没有用过这样的,等待高手

不负春光,努力生长 | 园豆:1382 (小虾三级) | 2013-04-26 22:47
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

沈融兴 | 园豆:404 (菜鸟二级) | 2013-04-30 15:32
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册