我正在尝试在PostgreSQL 9.1中完成以下操作:
结果是:
我用一个解决方法来解决这个情况:
SELECT m.id, vm.id, vm.value
FROM m
LEFT JOIN vm ON vm.m_id = m.id and vm.variation_id = 1
ORDER BY lower(trim(vm.value)) COLLATE "C" ASC LIMIT 10 OFFSET 120
结果是:
这是结果。
id | id | value
----+-----+---------------
504 | 511 | "andr-223322"
506 | 513 | "andr-322223"
824 | 831 | "angHybrid"
866 | 873 | "Another thing"
493 | 500 | "App update required!"
837 | 844 | "App update required!"
471 | 478 | "April"
905 | 912 | "Are you sure you want to delete this thing?"
25 | 29 | "Assignment"
196 | 201 | "AT ADDRESS"
好的,让我们使用OFFSET 130
执行相同的查询:
id | id | value
----+-----+---------------
196 | 201 | "AT ADDRESS"
256 | 261 | "Att Angle"
190 | 195 | "Att Angle"
273 | 278 | "Att Angle:"
830 | 837 | "attAngle"
475 | 482 | "August"
710 | 717 | "Averages"
411 | 416 | "AVG"
692 | 699 | "AVG SHAPE"
410 | 415 | "AVGs"
我们再次看到了我们的AT ADDRESS
项目,但是在开头!!!
事实上,vm
表包含以下两个项目:
id | m_id | value
----+------+---------------
201 | 196 | "AT ADDRESS"
599 | 592 | "At Address"
我用一个解决方法来解决这个情况:
(lower(trim(vm.value)) || vm.id)
但是到底怎么回事?为什么我必须使用一种变通方法?