select nodeid,(
-> select count(*) from tkey
-> where `key`<=a.nodeid
-> and `key`>nz((select max(nodeid) from tnodeid where nodeid<a.nodeid),0)
-> )
-> from tnodeid a;结果是nz函数不存在
网上搜也搜不到~~这怎么弄呢?
-> select count(*) from tkey
-> where `key`<=a.nodeid
-> and `key`>nz((select max(nodeid) from tnodeid where nodeid<a.nodeid),0)
-> )
-> from tnodeid a;结果是nz函数不存在
网上搜也搜不到~~这怎么弄呢?
+-----+
| key |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+-----+
10 rows in set (0.00 sec)mysql> select * from tnodeid;
+--------+-------------+
| nodeid | keyspernode |
+--------+-------------+
| 3 | 0 |
| 5 | 0 |
| 10 | 0 |
+--------+-------------+
3 rows in set (0.00 sec)mysql>mysql> select nodeid, (
-> select count(*) from tkey
-> where `key` <=a.nodeId
-> and `key` >ifnull((select max(nodeID) from tnodeid where nodeid<a.nodeid),0)
-> ) as keyspernode
-> from tnodeid a
-> ;
+--------+-------------+
| nodeid | keyspernode |
+--------+-------------+
| 3 | 3 |
| 5 | 2 |
| 10 | 5 |
+--------+-------------+
3 rows in set (0.00 sec)mysql>
大牛,非常感谢!
为什么我比较二进制数最后一个没有值?
mysql> select * from tkey;
+-------+
| key |
+-------+
| 0000
|
| 0001
|
| 0010
|
| 0011
|
| 0100
|
| 0101
|
| 0110
|
| 0111
|
| 1000
|
| 1001
|
| 1010
|
| 1011
|
| 1100
|
| 1101
|
| 1110
|
| 1111
+-------+
16 rows in set
mysql> select * from tnodeid;
+--------+-------------+
| nodeid | keyspernode |
+--------+-------------+
| 0010
| 0 |
| 0111
| 0 |
| 1010
| 0 |
| 1111
+--------+-------------+
4 rows in set
select nodeid,(select count(*) from tkey where `key`<=a.nodeid and `key`>ifnull((select max(nodeid) from tnodeid where nodeid<a.nodeid),0))as keyspernode from tnodeid a;
+--------+-------------+
| nodeid | keyspernode |
+--------+-------------+
| 0010
| 3 |
| 0111
| 5 |
| 1010
| 3 |
| 1111
+--------+-------------+
4 rows in set最后1111那一行的keyspernode应该是5。
但结果就是这样了。虽然也可以用加减法算出这个值,但总想搞明白为什么~~