mysql - 将 MS Access 查询转换为 MariaDB

Question

我正在努力创建 MariaDB SQL 命令，该命令将产生与我当前在 MS Access 数据库中使用的这三个查询(如下)相同的输出。我的 Excel VBA 脚本使用仅此 SQL 命令调用下面的第三个 SQL 查询命令(Hours to Heat Electric WH)，其中日期值被动态替换。出于此问题的目的，该命令如下所示:

SELECT ElectricWH_Data.* 
FROM ElectricWH_Data
WHERE (ElectricWH_Data.Date_Reading) > #06/01/19#;

这是结果表的摘要:

Date_Time       Date        Time     Max WH Out   Min WH Out6/27/18 0:52    06/27/18    00.52    60.38        43.566/28/18 0:52    06/28/18    00.52    60.50        44.446/29/18 0:32    06/29/18    00.32    60.13        45.386/30/18 0:32    06/30/18    00.32    60.19        47.13 7/1/18 0:12    07/01/18    00.12    60.50        47.56 7/2/18 0:42    07/02/18    00.42    60.44        44.94 7/3/18 0:42    07/03/18    00.42    60.38        46.88

I would like to duplicate this process but using a MariaDB database and SQL commands. Can you assist?

By the way, I am aware that dates and date formats are handled differently in MariaDB.

Below are the SQL queries from the MS Access database.

GetTemDataByDay:

SELECT 
  Min(PiSolarWH.Electric_WH_Out) AS MinOfElectric_WH_Out, 
  Max(PiSolarWH.Electric_WH_Out) AS MaxOfElectric_WH_Out, 
  Format(PiSolarWH.Date_Reading,'mm/dd/yy') AS TheDay
FROM
  PiSolarWH
GROUP BY
  Format(PiSolarWH.Date_Reading,'mm/dd/yy');

ElectricWHData:

SELECT
  PiSolarWH.Date_Reading, 
  Format([PiSolarWH.Date_Reading],'mm/dd/yy') AS TheDate,
  Format([Date_Reading],'hh.mm') AS DayTime, 
  GetTempDataByDay.MaxOfElectric_WH_Out AS Expr1, 
  GetTempDataByDay.MinOfElectric_WH_Out AS Expr2
FROM
  GetTempDataByDay, PiSolarWH
WHERE 
  Format([PiSolarWH.Date_Reading],'mm/dd/yy') = [GetTempDataByDay].[TheDay] 
  AND GetTempDataByDay.MaxOfElectric_WH_Out = [PiSolarWH].[Electric_WH_Out];

加热电 WH 的时间:

SELECT
  PiSolarWH.Date_Reading, 
  Format([Date_Reading],'hh.mm') AS DayTime,
  GetTempDataByDay.MaxOfElectric_WH_Out, 
  PiSolarWH.Electric_WH_Out, 
  Format([PiSolarWH.Date_Reading],'mm/dd/yy') AS Expr1
FROM
  GetTempDataByDay,
  PiSolarWH
WHERE 
  GetTempDataByDay.MaxOfElectric_WH_Out = [PiSolarWH].[Electric_WH_Out]
  AND Format([PiSolarWH.Date_Reading],'mm/dd/yy') = [GetTempDataByDay].[TheDay];

Answer 1

好吧，我明白了! MariaDB 的存储 View 的工作方式类似于 MS Access 存储查询。我能够将三个 MS Access 查询(当然使用修改后的语法)作为存储的 View 添加到数据库中。其工作方式与 MS Access 中的工作方式完全相同。这是一个例子:

CREATE VIEW GetTempDataByDay AS
SELECT 
  date_reading,
  Min(temps.Electric_WH_Out) AS MinOfElectric_WH_Out, 
  Max(temps.Electric_WH_Out) AS MaxOfElectric_WH_Out, 
  date(temps.Date_Reading) AS TheDay
FROM
  temps
GROUP BY
  date(temps.Date_Reading);

然后在我创建的其他两个 View 中使用它来复制 MS Access 存储的查询。

感谢您的宝贵时间......RDK