mysql查询语句切换至kingBase

mysql 版本5.7
kingBase 版本V8R3

一、data_format不支持

• mysql查询：

SELECT
DATE_FORMAT(rra.create_time, '%Y-%m') AS MONTH,
SUM( CASE WHEN rrm.resource_type = 'table' THEN 1 ELSE 0 END ) AS tableTotal,
SUM(CASE WHEN rrm.resource_type = 'file' THEN 1 ELSE  0 END ) AS fileTotal
FROM res_resource_apply rra
LEFT JOIN res_resource_mount rrm ON rra.mount_id = rrm.id
WHERE rra.apply_state = 3 AND rra.create_time > '2020-01'
GROUP BY DATE_FORMAT(rra.create_time, '%Y-%m')
ORDER BY  rra.create_time DESC

修改建议：
1.需要做数值判断时，用TO_DATE()包装字符串；
2.列名用TO_CHAR()包装，注意格式化差异，类似Oracle
3.JAVA8的LocalDateTime类不能接收前两种类型的返回值，得用CAST(create_time AS timestamp )
4.add_months等函数可以用来计算时间

• kingBase查询：

SELECT TO_CHAR(rra.create_time, 'yyyy-mm') AS MONTH, 
SUM(CASE WHEN rrm.resource_type = 'table' THEN 1 ELSE 0 END) AS tableTotal,
SUM(CASE WHEN rrm.resource_type = 'file' THEN 1 ELSE 0 END) AS fileTotal
FROM res_resource_apply rra
LEFT JOIN res_resource_mount rrm ON rra.mount_id = rrm.id
WHERE rra.apply_state = 3 AND rra.create_time > to_date('2020-01')
GROUP BY TO_CHAR(rra.create_time, 'yyyy-mm')
ORDER BY MONTH DESC

二、字段必须出现在GROUP BY子句中或用于聚合函数

• mysql查询：

COUNT(DISTINCT rrm.id) AS resourceCount 
FROM 
    (SELECT * 
    FROM res_catalog_classify 
    WHERE catalog_type = 1 
    AND parent_id != '0' 
    AND is_publish = 1) rcc 
LEFT JOIN res_resource_classify rrc ON rcc.id = rrc.classify_id 
LEFT JOIN (SELECT * FROM res_resource_catalog WHERE is_publish = 1) rrc1 ON rrc.resource_id = rrc1.id 
LEFT JOIN (SELECT * FROM res_resource_mount WHERE mount_state = 0) rrm ON rrc1.id = rrm.resource_id 
GROUP BY resourceCount

修改建议：
1.用子查询的形式把符合要求的部分包起来，把不符合要求的部分（不在GROUP BY子句中，也没用于聚合函数），通过join源表的形式，再生成新的查询
2.使用窗口函数，但是不会剔除重复项，例如：MAX(avg) OVER (PARTITION BY cname) AS mx

• kingBase查询：

SELECT t1.id,rcc2.catalog_name,t1.resourceCatalogCount,t1.resourceCount
FROM(
  SELECT rcc.id,  
  COUNT(DISTINCT rrc1.id) AS resourceCatalogCount, 
  COUNT(DISTINCT rrm.id) AS resourceCount 
  FROM 
    (SELECT * 
    FROM res_catalog_classify 
    WHERE catalog_type = 1 
    AND parent_id != '0' 
    AND is_publish = 1) rcc 
  LEFT JOIN res_resource_classify rrc ON rcc.id = rrc.classify_id 
  LEFT JOIN (SELECT * FROM res_resource_catalog WHERE is_publish = 1) rrc1  ON rrc.resource_id = rrc1.id 
  LEFT JOIN (SELECT * FROM res_resource_mount WHERE mount_state = 0) rrm ON rrc1.id = rrm.resource_id 
  GROUP BY rcc.id) t1
JOIN  res_catalog_classify rcc2 ON t1.id=rcc2."id"