将MySQL查询转换为Sqlite（SUBSTRING_INDEX）

I am working on a C# project which has a library to perform common tasks among various project.

我正在开发一个C#项目,它有一个库来执行各种项目中的常见任务。

I have a function in the library which gets a list of alarms from a table. This query is done in MySQL but I now need the same functionality with an SQLite database.

我在库中有一个函数,它从表中获取警报列表。此查询在MySQL中完成,但我现在需要与SQLite数据库相同的功能。

Below is the query I am trying to port from MySQL to Sqlite.

下面是我试图从MySQL移植到Sqlite的查询。

SELECT id, min(date) as min_date, max(date) as max_date, type, partMsg, level, page, COUNT(partMsg) AS msgCount FROM (SELECT id, date, type, SUBSTRING_INDEX(message, ':', 1) AS partMsg, level, page FROM alarms a WHERE acknowledged='0') p GROUP BY partMsg

At the moment when I run this query on the Sqlite database it throws an exception stating that SUBSTRING_INDEX isn't a valid function. Does SQLite have an alternative function that would do the same as this.

在我在Sqlite数据库上运行此查询时,它会抛出一个异常,指出SUBSTRING_INDEX不是有效函数。 SQLite是否有一个与此相同的替代功能。

Thanks for any help you can provide.

感谢您的任何帮助,您可以提供。

1 个解决方案

#1

Try:

SELECT id, min(date) as min_date, max(date) as max_date, type, partMsg, level, page, COUNT(partMsg) AS msgCount FROM (SELECT id, date, type, substr(message, ':', 1) AS partMsg, level, page FROM alarms a WHERE acknowledged='0') p GROUP BY partMsg