选择数据返回从SQL表中指定的天数

问题描述：

我想修改我的查询，只输出在我的SQL表（Ipaccounting），列（timeanddate）在最后的“天数”天数内的数据在另一个表（datapackages），柱（thresholdtimespandays）选择数据返回从SQL表中指定的天数

存储在ipaccounting,timeanddate的数据看起来像“二○一七年十月一十六日7点45分53秒”，并且指定datapackages,thresholdtimespandays就像“7”（实数从现在开始的最后7天）

我需要在查询中添加/更改什么？

我当前的查询是：

SELECT ip_address 
     ,SUM(upload_bytes) as upload_bytes 
     ,SUM(download_bytes) as download_bytes 
     ,sum(upload_bytes + download_bytes) as totalbytes 
     ,package_id 
     ,username 
     ,networkaccess 
     ,packagename 
     ,speedlimit 
     ,threshold 
     ,throttlelimit 
     ,datalimitamount 
     ,datalimitrange 
     ,thresholdtimespandays 
FROM (
     (SELECT ipaccounting.src_address as ip_address 
      ,SUM(ipaccounting.bytes) AS upload_bytes 
      ,0 as download_bytes 
      ,users.username 
      ,users.networkaccess 
      ,datapackages.package_id 
      ,datapackages.packagename 
      ,datapackages.speedlimit 
      ,datapackages.threshold 
      ,datapackages.throttlelimit 
      ,datapackages.datalimitamount 
      ,datapackages.datalimitrange 
      , datapackages.thresholdtimespandays 
     FROM ipaccounting 
     join users on users.ipaddress = ipaccounting.src_address 
     join datapackages on datapackages.package_id = users.datapackage 
     WHERE dst_address NOT BETWEEN INET_NTOA('192.168.0.1') AND INET_NTOA('192.168.255.254') 
     GROUP BY src_address) 

     UNION ALL 

     (SELECT ipaccounting.dst_address as ip_address 
      ,0 AS upload_bytes 
      ,SUM(ipaccounting.bytes) as download_bytes 
      ,users.username 
      ,users.networkaccess 
      ,datapackages.package_id 
      ,datapackages.packagename 
      ,datapackages.speedlimit 
      ,datapackages.threshold 
      ,datapackages.throttlelimit 
      ,datapackages.datalimitamount 
      ,datapackages.datalimitrange 
      ,datapackages.thresholdtimespandays 
     FROM ipaccounting 
     join users on users.ipaddress = ipaccounting.dst_address 
     join datapackages on datapackages.package_id = users.datapackage 
     WHERE src_address NOT BETWEEN INET_NTOA('192.168.0.1') AND INET_NTOA('192.168.255.254') 
     GROUP BY dst_address) 
     ) a 
GROUP BY ip_address 
ORDER BY INET_ATON(ip_address)

我不知道如何利用当前时间和减去在我的数据库datapackages,thresholdtimespandays指定的天量，然后给出来的数据。

谢谢:)

查看https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple- sql-query – Strawberry

冒着重复自己的风险... – Strawberry

别担心，我想通了。 –

答

我已经玩过，并找出它自己！

我的更新查询：

SELECT ip_address 
    ,SUM(upload_bytes) as upload_bytes 
    ,SUM(download_bytes) as download_bytes 
    ,sum(upload_bytes + download_bytes) as totalbytes 
    ,package_id 
    ,username 
    ,networkaccess 
    ,packagename 
    ,speedlimit 
    ,threshold 
    ,throttlelimit 
    ,datalimitamount 
    ,accountingdays 
FROM (
    (SELECT ipaccounting.src_address as ip_address 
     ,SUM(ipaccounting.bytes) AS upload_bytes 
     ,0 as download_bytes 
     ,users.username 
     ,users.networkaccess 
     ,datapackages.package_id 
     ,datapackages.packagename 
     ,datapackages.speedlimit 
     ,datapackages.threshold 
     ,datapackages.throttlelimit 
     ,datapackages.datalimitamount 
     ,datapackages.accountingdays 
    FROM ipaccounting 
    join users on users.ipaddress = ipaccounting.src_address 
    join datapackages on datapackages.package_id = users.datapackage 
    WHERE dst_address NOT BETWEEN INET_NTOA('192.168.0.1') AND 
INET_NTOA('192.168.255.254') 
    and timeanddate BETWEEN SUBDATE(CURRENT_TIMESTAMP(), INTERVAL 
datapackages.accountingdays DAY) AND CURRENT_TIMESTAMP() 
    GROUP BY src_address) 

    UNION ALL 

    (SELECT ipaccounting.dst_address as ip_address 
     ,0 AS upload_bytes 
     ,SUM(ipaccounting.bytes) as download_bytes 
     ,users.username 
     ,users.networkaccess 
     ,datapackages.package_id 
     ,datapackages.packagename 
     ,datapackages.speedlimit 
     ,datapackages.threshold 
     ,datapackages.throttlelimit 
     ,datapackages.datalimitamount 
     ,datapackages.accountingdays 
    FROM ipaccounting 
    join users on users.ipaddress = ipaccounting.dst_address 
    join datapackages on datapackages.package_id = users.datapackage 
    WHERE src_address NOT BETWEEN INET_NTOA('192.168.0.1') AND 
INET_NTOA('192.168.255.254') 
    and timeanddate BETWEEN SUBDATE(CURRENT_TIMESTAMP(), INTERVAL 
datapackages.accountingdays DAY) AND CURRENT_TIMESTAMP() 
    GROUP BY dst_address) 
    ) a 
GROUP BY ip_address 
ORDER BY INET_ATON(ip_address)

我也让我的数据库进行一些更改，以及如datapackage.thresholdtimespandays到datapackage.accountingdays所以我的查询将是我的问题有点不同。

选择数据返回从SQL表中指定的天数

相关推荐