查询提取记录范围 [英] Query to Extract Range of Records

问题描述

大家好，


我目前正在努力研究如何通过SQL查询提取记录列表的方法。


标准只是查看名为locn的位置代码，此代码包含3~4个字符的结构。第1个字符是字母，第2个是数字，第3个是另一个字母。第二个数字可以是2位数，这使得代码长达4个字符。


使用简单的LIKE函数（例如A *，A1 *，A12）提取记录并不困难*或A12A，位于本地区内，最多只能输入4个字符。但是我在这个主题上写的范围意味着如果用户输入这样的范围值： A1-C20G


这里的难点是，我怎么能用这个输入范围提取记录？有谁知道如何构建这样的算法？任何帮助将不胜感激。

解决方案

我不认为大于>小于<也会工作得很好，但它会好一点。

我很想在数据库中添加一个查找表格式

展开 | 选择 | Wrap | 行号

我同意你的代码为绿色，因为更大，更少只能使用数字和日期。虽然有一个使用字符的数字形式的工作，但这只适用于VBA，而不是我想要的查询。


如果我是如何查询'我使用查找表代码绿色？


我确实认为在VBA中生成一个带有各种标准的长SQL查询。

Eg A10C-B ，使用OR对每个区域进行分组

展开 | 选择 | Wrap | 行号

如果只有问题是表达得更清楚，处理起来会更加简单。


当你说 A1-C20G 时，你的意思是来自A1 *直到并包括C20G *？

如果是这样，那么我应该解释一下，虽然有一些结构可以处理一系列值（[']在''A1''和'之间'C20G''）和值的模式匹配（比如''A1 *''）没有什么可以处理两者的混合。


>和<也可以在SQL和VBA中使用相当明显的字符串。


因为''A1''是从''A1开始的任何字符串的最小值''，这可以用作Between构造中的较低值（在''A1''和''C20G'之间包括所有''A1 *''）。不幸的是，这并不适用于上限值。 ''A1 *''到''C2 *''之间的所有值都需要写成在''A1''和''C2ZZ''。


如果这不是你的意思，那么如果不进一步澄清你的意思，我就无法帮助。




Hi all,

I''m currently struggling on a method of how to extracting a list of records through SQL query.

The criteria is simply looking on location code called locn, this code contains a structure of 3~4 characters. The 1st character is alphabet, 2nd is numeric, and 3rd is another alphabet. The 2nd numeric can be 2 digit, which makes the code to 4 characters long.

Its not that difficult to extract the record with simple LIKE functions such as A*, A1*, A12*, or A12A, within the locn only with up to 4 characters input. But the range I written on the topic means if the user enters a range value like this: A1-C20G

The hard part here is, how can I extract the records with this range of input? Do anyone have an idea of how such algorithm be structured? Any help would be greatly appreciated.

解决方案

I don''t suppose greater than > less than < would work very well either, but it is a bit better.
I would be tempted adding a lookup table to the database of the format

Expand|Select|Wrap|Line Numbers




I agreed with you code green, since greater and less than only works with numbers and dates. Althought there''s a work around with using character''s number form, but this only works in VBA, not in query as I wanted to.

How would the query be if I''m using a lookup table code green?

I did thinked of generating a long SQL query in VBA with all sorts of criterias.
E.g. A10C-B, using the OR to group each area

Expand|Select|Wrap|Line Numbers




If only the question were expressed more clearly this would be so much simpler to deal with.

When you say A1-C20G, do you mean from "A1*" up to and including "C20G*"?
If so, then I should explain that while there are constructs to handle both a range of values ([X] Between ''A1'' And ''C20G'') and pattern matching of values (Like ''A1*'') there is nothing to handle a mixture of the two.

> and < also can be used quite sensibly with strings in both SQL and VBA.

As ''A1'' is the least value of any string starting ''A1'', this can be used as the lower value in a Between construct (Between ''A1'' And ''C20G'' includes all of ''A1*''). Unfortunately, this doesn''t hold true for the upper value. All values between, and including, ''A1*'' to ''C2*'' would need to be written as Between ''A1'' And ''C2ZZ''.

If that isn''t what you mean, then I can''t help without further clarification of what you do mean.




这篇关于查询提取记录范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！