mysql连接表创建丢失的日期 [英] mysql joins tables creating missing dates

问题描述

不确定如何正确执行此操作.我整个上午都迷恋JOINS.我所能得到的只是日期范围，但日期只是在应该为空白的日期上重复.

Not sure how to do this correct. I have messed with JOINS all morning. All I can get the is the date range but the dates just repeat over the dates that should be blank.

这是一个基本查询

SELECT `time`,`protocal`,(`octet`/1024) as `octet10243`,`percent`,`div`,FROM_UNIXTIME(`time`, '%Y-%m-%d') as `newtime3` FROM ipport
WHERE FROM_UNIXTIME(`time`, '%Y-%m-%d') >= '2011-01-05' AND FROM_UNIXTIME(`time`, '%Y-%m-%d') <= '2011-01-08' AND (`protocal` = 'Echo' ) AND `div` = 'XDIV'

这是结果.

"time";"protocal";"octet10243";"percent";"div";"newtime3"
"1290923100";"Echo";"92844.07421875";"1.04435";"XDIV";"2011-01-06"
"1291009500";"Echo";"95110.106445312";"1.0796";"XDIV";"2011-01-07"

我还有另一个表格，日期从2011-01-01到2011-01-17.我当时是用这个来加入的.

I have another table with just date from 2011-01-01 to 2011-01-17. I was using this to join.

这是我要寻找的结果.

"time";"protocal";"octet10243";"percent";"div";"newtime3"
"0";"Echo";"0";"0";"XDIV";"2011-01-05"
"1290923100";"Echo";"92844.07421875";"1.04435";"XDIV";"2011-01-06"
"1291009500";"Echo";"95110.106445312";"1.0796";"XDIV";"2011-01-07"
"0";"Echo";"0";"0";"XDIV";"2011-11-08"

这是我ho积的加入尝试...

Here is my hoarded attempt at JOINing...

SELECT makeupdate.date, FROM_UNIXTIME(ipport.time, '%Y-%m-%d'),`time`,`protocal`,(`octet`/1024) as `octet10243`,`percent`,`div`,FROM_UNIXTIME(`time`, '%Y-%m-%d')
as `newtime3` FROM ipport  JOIN makeupdate ON FROM_UNIXTIME(ipport.time, '%Y-%m-%d') >= '2011-01-05'
AND FROM_UNIXTIME(ipport.time, '%Y-%m-%d') <= '2011-01-08' AND (`protocal` = 'Echo' ) AND `div` = 'XDIV'

感谢您的帮助

推荐答案

从日期表开始，并向LEFT JOIN到ipport表，以便您从日期表中获取所有行以及ipport表中的匹配行.

Start with your table of dates and LEFT JOIN to your ipport table, so that you get all rows from the date table combined with matching rows in the ipport table.

SELECT m.date, FROM_UNIXTIME(i.time, '%Y-%m-%d'),`time`,`protocal`,(`octet`/1024) as `octet10243`,`percent`,`div`,FROM_UNIXTIME(`time`, '%Y-%m-%d') as `newtime3` 
    FROM makeupdate m
        LEFT JOIN ipport i
            on m.date = FROM_UNIXTIME(ipport.time, '%Y-%m-%d')
                AND (`protocal` = 'Echo' ) 
                AND `div` = 'XDIV'
    WHERE m.date >= '2011-01-05' AND m.date <= '2011-01-08'

这篇关于mysql连接表创建丢失的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！