Perl 准备 DB2 语句没有返回我需要的内容
由于我使用的是 DB2，为了选择中间数据库的一部分（如限制/偏移配对），我需要执行不同类型的准备语句。我得到的例子是这样的：
SELECT * FROM (SELECT col1, col2, col3, ROW_NUMBER() OVER () AS RN FROM 表) AS cols 其中 RN 介于 1 和 10000 之间；
我对此进行了调整：
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) AS foo WHERE rownum >= 500 AND rownum <1000
当我调用 fetchall_arrayref()，我确实得到了 500 个结果，就像我想要的那样，但它只返回一个包含行号引用的数组，而不是我想要提取的所有数据。我知道一个事实，那就是代码应该按照其编写的方式执行操作，并且我尝试了一系列排列来获得我想要的结果，但没有运气。
我想要的只是将所有列（如我之前的准备语句）抓取到数组数组中： SELECT * FROM TU_TRANSACTIONS ORDER BY 2, 3, 4, 6, 7
但仅在指定部分。我只是缺少一个基本的东西，而且我看不到它。
任何帮助都会受到赞赏，即使它伴随着一些建设性的批评。
Since I am using DB2, in order to select a portion of a database in the middle (like a limit/offset pairing), I need to do a different kind of prepare statement. The example I was given was this:
SELECT *
FROM (SELECT col1, col2, col3, ROW_NUMBER() OVER () AS RN FROM table) AS cols
WHERE RN BETWEEN 1 AND 10000;
Which I adapted to this:
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) AS foo WHERE rownum >= 500 AND rownum <1000
And when I call the fetchall_arrayref(), I do come out with 500 results like I want to, but it is only returning an array with references to the row number, and not all of the data I want to pull. I know for a fact that that is what the code is SUPPOSED to do as its written, and I have tried a bunch of permutations to get my desired result with no luck.
All I want is to grab all of the columns like my previous prepare statement into an array of arrays:
SELECT * FROM TU_TRANSACTIONS ORDER BY 2, 3, 4, 6, 7
but just on a designated section. There is just a fundamental thing I am missing, and I just cant see it.
Any help is appreciated, even if its paired with some constructive criticism.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的表表达式：
(SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) as foo
只有一列 - rownum - 所以当您从“foo”中选择“*”时，您只获取一列。
您的表表达式需要包含您想要的所有列，就像您发布的示例一样。
Your table expression:
(SELECT ROW_NUMBER() OVER (ORDER BY 2,3,4,6,7 ASC) AS rownum FROM TRANSACTIONS) as foo
Has only one column - rownum - so when you select "*" from "foo" you get only the one column.
Your table expression needs to include all of the columns you want, just like e example you posted.
我不使用 DB2，所以我可能会偏离基础，但似乎：
只会返回行号，因为虽然子查询引用了表，但主查询却没有。看起来它会看到的只是一组数字（这将返回填充了数字的单列）
也许这会起作用：
I don't use DB2 so I could be off-base but it seems that:
Would only return the row numbers because while the sub-query references the table the main query does not. All it seems it would see is the set of numbers (which would return a single column with the number filled in)
Perhaps this would work: