group_concat以及如何在sqlite中使用行号 [英] group_concat and how to use row number in sqlite

问题描述

我在表中有数据，列ID和列日期

i have a data in a table, column id and column date

id  || datetime
1   || 2013-05-24 19:23:16
2   || 2013-05-28 19:24:20
3   || 2013-05-28 19:25:05  
4   || 2013-05-30 19:25:39  
5   || 2013-05-30 19:26:05  

如何查询仅显示带计数的一天?或带计数的一行.结果是.

how query to show only one day with count?or one row with count. so the result is.

    || datetime            || count
1   || 2013-05-24 19:23:16 ||  1
2   || 2013-05-28 19:24:20 ||  2
3   || 2013-05-30 19:25:39 ||  2

我尝试在mysql中运行，但有查询.

i try in mysql and it works , there is my query.

SELECT id, GROUP_CONCAT( datetime ) AS date, COUNT( id ) AS count
FROM dataPetak
GROUP BY DATE( datetime )

但是当我使用sqlite时，结果是将我全都放在一个字符串中

but when i use sqlite the result is gives me all in one string

5 || 2013-05-24 19:23:16,2013-05-28 19:24:20,2013-05-28 19:25:05,2013-05-30 19:25:39,2013-05-30 19:26:05 || 5

该如何解决?

救救我

编辑

当我使用查询

SELECT [datetime], [count] FROM (SELECT MAX([datetime]) 'datetime', COUN
T(*) 'count' FROM dataPetak GROUP BY CAST ([datetime] AS DATE)) t;

结果是

       2013-05-24 19:23:16 ||  1
       2013-05-28 19:24:20 ||  2
       2013-05-30 19:25:39 ||  2

如何在sqlite中使用行号?

how to use row number in sqlite?

所以结果是

1   || 2013-05-24 19:23:16 ||  1
2   || 2013-05-28 19:24:20 ||  2
3   || 2013-05-30 19:25:39 ||  2

当我尝试此查询时，

SELECT ROW_NUMBER () OVER (ORDER BY [datetime]) No, [datetime], [count] FROM (SELECT MAX([datetime]) 'datetime', COUN
T(*) 'count' FROM dataPetak GROUP BY CAST ([datetime] AS DATE)) t;

显示错误

Error: near "(": syntax error

该如何解决?

推荐答案

在您的情况下，无需使用GROUP_CONCAT()功能.您在问题中的最后一个查询是针对SQL Server的. Sqlite没有ROW_NUMBER()的实现.

There is no need for GROUP_CONCAT() functionality in your case. Your last query in the question is for SQL Server. Sqlite doesn't have implementation for ROW_NUMBER().

话虽如此，请尝试

SELECT 
 (
   SELECT COUNT(*) 
     FROM 
   ( SELECT 1
       FROM dataPetak
      WHERE id <= t.id
      GROUP BY DATE(datetime)
   ) q
 ) No, datetime, count
  FROM 
 (
   SELECT id, MIN(datetime) datetime, COUNT(*) count
     FROM dataPetak
    GROUP BY DATE(datetime)   
 ) t

输出:

| No |            datetime | count |
------------------------------------
|  1 | 2013-05-24 19:23:16 |     1 |
|  2 | 2013-05-28 19:24:20 |     2 |
|  3 | 2013-05-30 19:25:39 |     2 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于group_concat以及如何在sqlite中使用行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！