Question

SQL语句转化成HQL语句

SELECT
l.deal_info_id as 应用,
SUM(CASE WHEN l.ser_type='回访' then 1 else 0 end) as 回访,
SUM(CASE WHEN l.ser_type='更新提示' then 1 else 0 end) as 更新提示,
SUM(CASE WHEN l.ser_type='提示' then 1 else 0 end) as 提示,
SUM(CASE WHEN l.ser_type='日常客服' then 1 else 0 end) as 日常客服,
SUM(CASE WHEN l.ser_type='温馨提示' then 1 else 0 end) as 温馨提示
FROM work_log l
GROUP BY l.deal_info_id
这种SQL语句转化成HQL语句应该怎么写呀

Answer 1

用返回map的方式吧。

select c.id,c.name,nvl(sum(h.count*w.price),0) as money

from t_client c left join t_history h on c.id = h.clientid left join t_wares w on w.id = h.waresid
group by c.id,c.name

select new map(c.id,c.name,nvl(sum(h.count*w.price),0) as money)

from Client c left join c.History h left join Wares w

group by c.id, c.name
大概这样，暂时没环境测试。

追问

(c.id,c.name,nvl(sum(h.count*w.price),0) 这个位置比如我想写 一个属性=‘1’ 这样的话应该怎么写呢