发布于

关联表聊连表统计条数

方法一：

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();

发布于

SQL更新空值

UPDATE gzf_rent_info SET exit_price = 0 WHERE exit_price IS NULL

发布于

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);

发布于

统计SQL编写实例

根据时间查询当前季度数据，再进行分组统计数量，SQL与JPA写法。

SELECT
  a.organization_id
FROM
  zn_safe_manager_staff a
WHERE
  QUARTER (a.date_created) = QUARTER ('2018-12-01')
GROUP BY
  a.organization_id;
@Query("select a.organization.id,a.organization.name,count (a) from Staff a where quarter(a.dateCreated) = quarter(?1) group by a.organization.id")
List<Object[]> findHomeStateResult(String date);

 

发布于

SQL例子

数据库左连接查询SQL例子：

SELECT
  shaya_bs_water_meter.id,
  shaya_bs_water_meter.`name`
FROM
  shaya_bs_water_meter
LEFT JOIN (
  SELECT DISTINCT
    shaya_bs_automatic_record.water_meter_id
  FROM
    shaya_bs_automatic_record
) b ON shaya_bs_water_meter.id = b.water_meter_id
WHERE
  shaya_bs_water_meter.if_automatic = 'automatic'

数据库分组统计

select name,sum(number) from zn_safe_manager_fire_equipment GROUP BY name;

 

发布于

世界，您好！

欢迎使用WordPress。这是您的第一篇文章。编辑或删除它，然后开始写作吧！