mysql,PostgreSQL,Oracle数据库sql的细微差异(2) [whith as； group by； 日期引用]

sql示例(MySQL)

WITH

    temp1 AS (

        SELECT name AS results

        FROM Users u

        JOIN MovieRating m ON u.user_id = m.user_id

        GROUP BY m.user_id

        ORDER BY COUNT(*) DESC,left(name,1)

        LIMIT 1

    ),

    temp2 AS (

        SELECT title AS results

        FROM Movies m

        JOIN MovieRating r ON m.movie_id = r.movie_id

        WHERE r.created_at BETWEEN '2020-02-01' AND '2020-02-29'

        GROUP BY m.title

        ORDER BY AVG(r.rating) DESC, m.title ASC

        LIMIT 1

)

SELECT * FROM temp1

UNION ALL

SELECT * FROM temp2;

 这里使用了CTE,即WITH子句中定义的临时表,temp1 和 temp2 是临时的结果集,它们在 WITH 子句后面被创建，并在主查询中被引用,SELECT * FROM temp1 这部分被称作查询块（query block）或者查询语句（query statement）。

易犯的书写错误:

结果集缺乏( )括号 ; 

结果集之间缺失逗号; 

查询块的表名写错;

错写无效日期: 比如'2020-02-30',在 SQL 中，日期值必须有效。2 月并没有 31 日，这是一个无效的日期

whith  as

在oracle,mysql中,不允许在 CTE 内部使用 ORDER BY/LIMIT 组合，如果你需要在 CTE 中限制结果集，考虑使用子查询或者在 CTE 外部应用 LIMIT,

PostgreSQL可以,但是在多次引用 CTE 时，ORDER BY 的顺序不一定会被保留。此外，CTE 在 PostgreSQL 中总是被物化，这意味着它们的结果集会被实际存储，而不是仅仅作为一个临时的查询结果。这可能会导致性能问题，特别是当处理大量数据时.

解决方案:

1. 在 CTE 外部使用 LIMIT。

2. 使用窗口函数，如 ROW_NUMBER()，来为每行分配一个唯一的序号，然后在 CTE 外部的查询中使用这个序号来限制结果集

3.  PostgreSQL( 9.4 及以上版本)，Oracle (12c 及以上版本) 可以在 CTE 中使用 FETCH FIRST 子句来限制结果集的大小

group by 

oracle和PostgreSQL里面,出现group by分组,则select 后的必须是聚合函数,或者在group by里出现的分组字段

在mysql没有这方面的要求 (上面的示例的temp1里面name)

日期格式

3个数据库都使用都使用单引号引用字符字面量

不同: mysql比较宽松,哪怕使用了双引号,日期格式正确也可以识别;

        oracle和PostgreSQL,对于字符字面量的引用只能是单引号;  

关于字面量的引用(3个数据库)

• 字符串字面量（包括纯英文和中文）使用单引号。
• 日期字面量使用单引号。
• 数字字面量无需。

补充

标识符的引用:

oracle/PostgreSQL-----双引号" "

mysql-----反引号``

标识符:数据库对象,比如表名,列名,存储过程

避免日期的隐式转换

'2020-02-01'

oracle 用 to_date('日期','YYYY-MM-DD')

TO_DATE('2020-02-01', 'YYYY-MM-DD')

PostgreSQL 用  ::date 

'2020-02-01'::date

建议:

始终使用单引号来定义日期字符串,可以确保你的代码在不同的数据库系统中具有更好的可移植性和可读性。

日期存在隐式转换可能存在的问题

1. 性能问题：这可能会增加查询的执行时间，尤其是在处理大量数据时。

2. 数据一致性问题：如果应用程序在不同地方使用不同的日期格式，可能会导致数据不一致性，因为隐式转换可能在不同情况下产生不同的结果。

3. 时区问题：如果数据库服务器和应用程序服务器位于不同的时区，隐式转换可能会导致时区处理上的问题

4. 文化差异问题：不同的文化和地区可能有不同的日期格式习惯，隐式转换可能不会正确处理这些差异，导致日期被错误解析。