发布于

关联表聊连表统计条数

方法一：

SELECT
  COUNT(*)
FROM
  zn_supervise_state AS a
INNER JOIN (
  SELECT
    id
  FROM
    sys_organization
  WHERE
    sys_organization.id = '7'
  OR sys_organization.parent_id = '7'
) AS b ON a.organization_id = b.id

方法二：

SELECT
  COUNT(*)
FROM
  zn_supervise_state
WHERE
  zn_supervise_state.organization_id IN (
    SELECT
      id
    FROM
      sys_organization
    WHERE
      sys_organization.id = '7'
    OR sys_organization.parent_id = '7'
  )

发布于

SQL日期计算

//获取已经需要催缴的用户，到期时间在两个月之内的。
@Query(value = "SELECT * FROM gzf_rent_info a WHERE a.expire_time < DATE_ADD(now(), INTERVAL 2 MONTH) AND a.expire_time > NOW() AND a.if_rent = TRUE", nativeQuery = true)
List<RentInfo> findListCuijiao();

发布于

jpa查询学习实例

//模糊条件查询
@Query("select a.id from Room a where a.fullName like CONCAT('%',?1,'%')")
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
Set<Long> findIdByFullName(String name);

@Query("select a from Room a where a.name = ?1 and a.fullName like CONCAT('%',?2,'%')")
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
List<Room> findByNameAndPName(String name, String fullName);
//条件查询排序
@Query("select a from Room a where a.parentId = ?1 order by a.id")
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
List<Room> findByPidOrderName(Long pid);
//多数据更新
@Query("update GZFUser set pIdCard = ?2, holderName = ?3, type='other' where id in (?1)")
@Modifying
void updateOtherTypeByIds(Set<Long> ids, String pIdCard, String holderName);
//根据日期查询当年当月数据按照部门分组统计
@Query("select a.organization.id,count (a) from Education a where month(a.dateCreated) = month(?1) and year(a.dateCreated) = year(?1) group by a.organization.id")
List<Object[]> findHomeStateResult(String date);
//按名称分组排序
@Query("select a.name,a.spec,a.munit,sum(a.number),a.month from FireEquipment a group by a.name")
List<Object[]> findByNameAndMonth();
//删除
@Modifying
@Query("delete from OtherDocumentState where organization.id in (?1)")
int deleteIdIn(Long[] longs);

@Modifying
@Query("delete from OtherDocumentState where document.id=1")
int deleteByOtherDocumentId(Long id);
//简写方式
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByUsername(String username);

@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByEmail(String email);

@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByUsernameAndIdIsNot(String username, Long id);

@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
User findByEmailAndIdIsNot(String username, Long id);

 

发布于

jpa左连接查询写法实例

@Query(value = "SELECT a.id,a.user_name,a.room_type,a.rent_type,b.name,b.id AS room_id,b.cell,b.floor,b.type,b.sorter  FROM gzf_room b LEFT JOIN gzf_rent_info a ON a.room_id = b.id WHERE b.parent_id = ?1", nativeQuery = true)
@QueryHints({@QueryHint(name = "org.hibernate.cacheable", value = "true")})
List<Object[]> findByRoomHome(Long id);