带有参数的 CRUD 本地查询

Question

我在 spring data JpaRepository 中使用本机查询，如下所示：

@Query(value = "SELECT SUBSTRING_INDEX(u.email, '@', -1) as domain, COUNT(*) as domainCount r.invite_organization_id"
+ "  FROM users u,_registrations r where u.user_id=r.user_id and r.invite_organization_id=?1"
+ "  GROUP BY "
+ "SUBSTRING_INDEX(u.email, '@', -1) ORDER BY domainCount DESC", nativeQuery = true)
List<Object[]> countTopDomain(String orgId);

以上给了我以下例外： 您的 SQL 语法有误;检查与您的MySQL服务器版本相对应的手册，了解在第1行的"r.invite_organization_id FROM 用户u，注册r在哪里u.user"附近使用的正确语法

我将如何从方法 countTopDomain(( 参数中传递 invite_organization_id(在查询中(的值。

Answer 1

'r.invite_organization_id FROM srs_users U，srs_user_registrations R'，有语法错误。计数 (*( 后缺少 '"，"' 作为域计数

Answer 2

试试这个

@Query(value = "SELECT SUBSTRING_INDEX(u.email, '@', -1) as domain, COUNT(*) as domainCount, r.invite_organization_id"
+ "  FROM srs_users u,srs_user_registrations r where u.user_id=r.user_id and r.invite_organization_id=?1"
+ "  GROUP BY "
+ "SUBSTRING_INDEX(u.email, '@', -1) ORDER BY domainCount DESC", nativeQuery = true)
List<Object[]> countTopDomain(String orgId);