使用多个连接将 MYSQL 查询转换为 HQL

Question

everyone.

我正在使用带有mysql作为数据源的grails 3.3.0.M2框架，以下sql查询按预期工作

SELECT 
c.name,
SUM(CASE
WHEN t.status = 'open' THEN 1
ELSE 0
END) 'open',
SUM(CASE
WHEN t.status = 'pending' THEN 1
ELSE 0
END) 'in progress',
SUM(CASE
WHEN t.status = 'closed' THEN 1
ELSE 0
END) 'closed'
FROM
tickets t
INNER JOIN
users u ON t.user_id = u.id
INNER JOIN
user_coordinations uc ON uc.user_id = u.id
INNER JOIN
coordinations c ON c.id = uc.coordination_id
GROUP BY 1

我使用隐式 JOIN 转换为 HQL，但我得到的结果错误，这是 hql 查询：

SELECT 
c.name,
SUM(CASE
WHEN t.status = 'open' THEN 1
ELSE 0
END),
SUM(CASE
WHEN t.status = 'pending' THEN 1
ELSE 0
END),
SUM(CASE
WHEN t.status = 'closed' THEN 1
ELSE 0
END)
FROM
Ticket t, User u, UserCoordination uc, Coordination c
WHERE
MONTH(t.dateCreated) = :month
GROUP BY 1

为了获得正确的结果堆栈溢出用户帮助我了解查询需要使用显式 JOINS，这里是问题：按不属于所咨询表的字段分组

现在我正在尝试以下查询：

SELECT 
c.name,
SUM(CASE
WHEN t.status = 'open' THEN 1
ELSE 0
END),
SUM(CASE
WHEN t.status = 'pending' THEN 1
ELSE 0
END),
SUM(CASE
WHEN t.status = 'closed' THEN 1
ELSE 0
END)
FROM
Ticket t
INNER JOIN
User u
INNER JOIN
UserCoordination uc
INNER JOIN
Coordination c
WHERE
MONTH(t.dateCreated) = :month
GROUP BY 1

但是我得到了一个com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException，并带有导致的消息您的 SQL 语法有错误; 检查与您的MySQL服务器版本相对应的手册，了解在第1行的"内部连接协调坐标上的内部连接user_coordinations usercoordi2_"附近使用的正确语法

感谢您的帮助和时间

Answer 1

SELECT new map(
c.name as name,
(CASE
WHEN t.status = 'open' THEN 1
ELSE 0
END) as open,
(CASE
WHEN t.status = 'pending' THEN 1
ELSE 0
END) as pending, 
(CASE
WHEN t.status = 'closed' THEN 1
ELSE 0
END) as closed,
SUM(open) as openSum,
SUM(pending) as pendingSum,
SUM(closed) as closedSum
)
FROM
Ticket t
left join t.user u left join u.userCoordination uc left join uc.coordination c 
WHERE
MONTH(t.dateCreated) = :month

分组依据 1

你上面缺少的东西更像是你需要的，你需要的select new map(i.item as item...，如果你将这个的基础知识与你所拥有的和我试图做的事情进行比较，你就会明白为什么你有错误。

不确定你的组应该按某事分组。不确定内部连接是否只是意味着连接，如果是这种情况，请省略所有左侧连接，因为左侧连接尝试连接并获得任何空具有许多关系等。