NHibernate SQLFunction group by

本文关键字：by group SQLFunction NHibernate | 更新日期: 2023-09-27 18:00:30

我正在尝试这样做：

SELECT round(song.rating), count(song.song_id) FROM song
GROUP BY round(song.rating);

我的QueryOver:

 var output = sess.QueryOver<song>()
                .SelectList(list => list
                    .Select(Projections.SqlFunction("round", NHibernateUtil.Int32, Projections.GroupProperty("rating")))
                    .SelectCount(s => s.song_id))
                .List<object[]>()
                .Select(prop => new RatingStat
                {
                    rating = (int)prop[0],
                    count = (int)prop[1]
                }).ToList<RatingStat>();

预期输出：

+---------------------------+---------------------------+
|                         0 |                        12 |
|                         1 |                         1 |
|                         3 |                         1 |
|                         4 |                         6 |
|                         5 |                         3 |
|                         6 |                         6 |
|                         7 |                        12 |
|                         8 |                         7 |
|                         9 |                         9 |
|                        10 |                         2 |
+---------------------------+---------------------------+

实际输出：

0                         12
1                         1
3                         1
4                         1
4                         3
4                         1
4                         1
5                         1
5                         1
5                         1
6                         2
6                         1
6                         3
7                         2
7                         9
7                         1
8                         1
8                         3
8                         2
8                         1
9                         1
9                         3
9                         1
9                         4
10                        2

我使用我自己的方言继承自MySQL5Dialect，因为我的MySQL方言不支持round函数。以下是圆函数在我的方言中的定义：

 RegisterFunction("round", new StandardSafeSQLFunction("round", NHibernateUtil.Int32,1));

我的问题是，为什么我有多个具有相同评级值的组？四舍五入的值应不同。圆形函数可能不能正常工作吗？编辑：添加生成的SQL语句

 SELECT round(this_.rating) as y0_, count(this_.song_ID) as y1_ FROM song this_ GROUP BY this_.rating

NHibernate SQLFunction group by

找到的解决方案：

 var t = Projections.SqlFunction("round", NHibernateUtil.Int32, Projections.GroupProperty("rating"));
 var output = sess.QueryOver<Song>()
       .SelectList(list => list
       .Select(Projections.SqlFunction("round", NHibernateUtil.Int32, Projections.GroupProperty(t)))
       .SelectCount(s => s.song_id))
       .List<object[]>()
       .Select(prop => new RatingStat
        {
            rating = (int)prop[0],
            count = (int)prop[1]
        }).ToList<RatingStat>();
相关文章：