sqlite 查询需要稍微调整一下 [英] sqlite query need to tuned a bit

问题描述

当前我有代码，它将以 10 分钟为间隔显示过去一小时的记录总和.

Hi current i have code which will display the sum of records for last one hour in 10min interval.

SELECT SUM(NO_OF_ADULT) AS ADULT
     , SUM(NO_OF_ADULT_F) AS ADULT_F
     , SUM(NO_OF_CHILDREN) AS CHILDREN
     , SUM(NO_OF_CHILDREN_F) AS CHILDREN_F
     , SUM(NO_OF_SENIOR_CITIZEN) AS SENIOR
     , SUM(NO_OF_SENIOR_CITIZEN_F) AS SENIOR_F
     , SUM(NO_OF_INFANT) AS INFANT
     , SUM(NO_OF_INFANT_F) AS INFANT_F
     , SUBSTR(END_TIME_OF_SERVICE, 1, 4) || '0 - ' || SUBSTR(END_TIME_OF_SERVICE, 1, 4) || '9' AS TIME_SLOT
  FROM CLNT_RESERVATION RES
      , CLNT_ORDER ORD
      , CLNT_ORDER_CHEQUE_RESERVATION_MAPPING RM
  WHERE RES.START_DATE_OF_SERVICE ="2012-09-24"
    AND ORD.ENT_CODE="****"
    AND ORD.BRANCH_CODE="******"
    AND ORD.STATION_CODE="*******"
    AND RES.STATION_CODE=ORD.STATION_CODE 
    AND ORD.ORDER_STATUS <> 111 
    AND ORD.ORDER_ID=RM.ORDER_ID 
    AND (ORD.CREATE_TIME LIKE "2012-09-24%" OR ORD.UPDATE_TIME LIKE "2012-09-24%")
    AND ORD.STATUS_FLG ="A"
    AND ORD.DELETE_FLG="N" 
    AND RM.RESERVATION_ID = RES.RESERVATION_ID 
    AND (strftime("%H:%M", RES.END_TIME_OF_SERVICE) BETWEEN strftime("%H:%M",TIME("now","localtime","-1 hour","-00 minutes")) 
    AND strftime ("%H:%M",TIME("now","localtime")) OR strftime("%H:%M", RES.END_TIME_OF_SERVICE) BETWEEN strftime("%H:%M",TIME("now","localtime","-1 hour","-00 minutes")) 
    AND strftime("%H:%M",TIME("now","localtime")) ) 
GROUP BY 9

这将给出如下输出

ADULT  ADULT_F  CHILDREN CHILDREN_F SENIOR SENIOR_F INFANT INFANT_F  TIME_SLOT

 0       0         0       0          1        1      0      0     12:00 - 12:09
 1       0         0       0          0        1      0      0     12:30 - 12:39

现在您可以看到 12:10 - 12:29 的值不在数据库中，因此也不会显示.

now you can see that the values for 12:10 - 12:29 its not in the db hence its not display too.

我期待的是:-1) 需要创建行并在所有字段中简单地填写 0.(高)2) time_slot 应该从当前的time_stamp.(medium)

what i am expecting is : - 1) Need the row to be created and simply fill with 0 in all the fields. (high) 2) time_slot should be calculated from the current time_stamp.(medium)

帮助这是有帮助的，请建议我一些解决方案，非常感谢时间.

Help this is helpfull, kindly suggest me some solution thank a lot for time.

推荐答案

1. 数据库不会为不存在的数据创建记录.
   如果您想获取所有时间段的记录(并且在读取结果时无法在您的应用程序中处理此问题)，您必须将填充零的记录添加到表中.(您可以在所有所需的时间段内盲目执行此操作，因为总和不会受到影响.)
2. 对于 SQL 中的字符串，始终使用单引号".SQLite 将接受双引号以与其他一些 SQL 引擎兼容，但如果您有一些同名的列，您将遇到难以发现的错误.
3. 您不需要那些 TIME(...) 调用；您可以直接使用 strftime('%H:%M','now','localtime').
4. 要根据当前时间获取十分钟的时间段，您必须在不将时间拆分为 h/m/s 字段的情况下进行计算.这已在上一个问题中显示:

1. The database will not create records for data that isn't there.
   If you want to get records for all time slots (and cannot handle this in your application when reading the result), you have to add records filled with zeros to the table. (You can do this blindly for all desired time slots, as the sums won't be affected.)
2. For strings in SQL, always use 'single quotes'. SQLite will accept double quotes for compatibility with some other SQL engines, but if you ever have some column with the same name, you will get hard-to-find errors.
3. You do not need those TIME(...) calls; you can use strftime('%H:%M','now','localtime') directly.
4. To get ten-minute time slots based on the current time, you must do calculations without splitting the time into h/m/s fields. This was already shown in the previous question:

SELECT DATETIME(strftime('%s', SERVICE) +
                ((strftime('%s', 'now') - strftime('%s', SERVICE))
                 % (60 * 10)),
                'unixepoch') ...

这篇关于sqlite 查询需要稍微调整一下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！