如何获得LIKE和COUNT以返回行数少于该行中未包含的值的行数？ [英] How do I get LIKE and COUNT to return the number of rows less than a value not in the row?

问题描述

例如：

SELECT COUNT(ID) FROM My_Table
WHERE ID < 

(SELECT ID FROM My_Table
WHERE ID LIKE '%4'
ORDER BY ID LIMIT 1)    

我的表：

  X          ID    Y     
------------------------
|      |     A1  |     |
------------------------
|      |     B2  |     |
------------------------
|      |     C3  |     |
------------------------ -----Page 1
|      |     D3  |     |
------------------------
|      |     E3  |     |
------------------------
|      |     F5  |     |
------------------------ -----Page 2
|      |     G5  |     |
------------------------
|      |     F6  |     |
------------------------
|      |     G7  |     | -----Page 3

没有以4结尾的数据，但仍有5行最后的内容小于 ％4 。

There is no data ending in 4 but there still are 5 rows that end in something less than "%4".

但是，在这种情况下没有匹配项，因此SQLite只返回 0

However, in this case were there is no match, so SQLite only returns 0

我不知道它在那里，但是如何更改此行为以仍然返回它之前的行数，就好像它在那里？

I get it is not there but how do I change this behavior to still return number of rows before it, as if it was there?

有什么建议吗？

谢谢。

推荐答案

SELECT COUNT(ID) FROM My_Table
WHERE ID < (SELECT ID FROM My_Table
  WHERE SUBSTRING(ID, 2) >= 4
ORDER BY ID LIMIT 1)   

这篇关于如何获得LIKE和COUNT以返回行数少于该行中未包含的值的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！