开发者

Add information in mysql

I have a table

id_user, hour, medition  
1 0  100  
1 1  101  
1 14 102  
2 5  108  
2 17 103

How I can complete the inexistent hours with a 0 v开发者_StackOverflow中文版alue for any user?

example

1 0 100  
1 1 101  
1 2 0  
1 3 0  
...


You shouldn't do this, as it's redundant. Only add entries where someone has hours. In your program, just code it so that if a record is not found, then there are zero hours. An example, in PHP:

$row = mysql_fetch_array(mysql_query("select * from myTable where id_user = 1 and hour = 3;"));
if (!$row) $hours = 0;
else $hours = $row['medition'];


build a table hours, populate it with the numbers from 1 to 24, then use this to query with an outer join against your primary table. - replace nulls with 0

edit to add quick example:

SELECT id_user, hour, NVL(medition,0)
FROM `hours` as h
LEFT JOIN `myTable`
    ON `hours`.`hour` = `myTable`.`hour`


Check out my solution to a similar problem regarding dates at;

Select all months within given date span, including the ones with 0 values

You can create a table for the hours, as suggested by Randy, instead of the dates that I created, should be easy enough then to modify the query to return the data you need...

Hope it helps!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜