在同一领域中选择测量单位的范围年龄

Question

我正在处理一个表格，其中一个人的年龄在字符串字段中，其格式如下:(amount UnitOfMeasurement)

1 year old = 1 y
11 months old = 11 m
5 Days old = 5 d

我正在尝试做一个年龄范围之间的搜索。这是可能的，通过SQL查询，它将顺序天(d)，然后月(m)，年(y)?

数据库是在SQL Server 2008上，但查询可能会在Access上完成，因为它用于报表的记录源。

Answer 1

在你的情况下，我要做的第一件事就是清理混乱的年龄字段，并将其标准化。一个快速的开始可能是创建一个查询，您可以使用以下表达式将age值和age单元分开:

age_unit: Right([age], 1)

和

age_value: Val([age])

如果然后按age_unit和age_value排序，您将得到正确排序的所有年龄(假设以天为单位的年龄总是小于以月为单位的年龄，而以月为单位的年龄又总是小于以年为单位的年龄)。注意，必须先按单位排序，然后按值排序。

如果您想返回某个最小值和最大值之间的年龄，如果您坚持使用单个单位，例如所有年龄在5到15岁之间，则没有问题。只需在"age_unit"字段下输入"y"作为标准(假设您在这里使用可视化查询生成器)，并在"age_value"字段下输入"Between 5 and 15"。

如果你是混合单位("所有年龄在6个月到2岁之间")，情况会变得有点复杂。在这种情况下，您需要执行以下操作:

在一个标准行中，您将为每个字段输入以下值:

age_unit:"m"

age_value:> = 6

然后在下一个条件行:

age_unit: " y "

age_value: & lt; = 2

这将返回单位为"m"且值>= 6或单位为"y"且值<=2的所有年龄。

另一个更简单的解决方案是通过一些简单的计算将所有年龄转换为标准单位，如年，例如，将"d"单位值除以365.25，并将"m"单位值除以12。然后在表中为新的标准化年龄数据创建一个新字段。

Answer 2

最好的方法是创建一个包含真实DATETIME值的新列。然后可以编写代码，比如CASE语句，来帮助将字符串转换为DATETIME。一旦完成，你的计算将变得简单得多。

Answer 3

1。这个字段没有原子值。这意味着您的表不在1NF中。

你应该把Age字段分割成2列与原子值:IntervalType(CHAR(1)... CHECK(IntervalType IN ('d','m','y'))和IntervalValue (INT;1、2等)。所以，代替Table(…，Age)你可以使用Table(…，IntervalType,IntervalValue)和

SELECT *
    ,CONVERT(VARCHAR(10),IntervalValue)
    +' '+CASE IntervalType WHEN 'd' THEN 'day' WHEN 'm' THEN 'month' WHEN 'y' THEN 'year' END
    +CASE WHEN IntervalValue > 1 THEN 's' ELSE '' END
    +' old = '
    +CONVERT(VARCHAR(10),IntervalValue)
    +' '+IntervalType
FROM table

2。30 d和1 month这两个值如何排序?一个月可以有28天到31天。

3。SQL Server解决方案:

DECLARE @TestData TABLE
(
     Age VARCHAR(25) NOT NULL
    ,IntervalValue AS CONVERT(INT,LEFT(Age,CHARINDEX(' ',Age))) PERSISTED
    ,IntervalType AS RIGHT(Age,1) PERSISTED
);
INSERT  @TestData
VALUES
 ('1 year old = 1 y')
,('2 years old = 2 y') 
,('11 months old = 11 m')
,('30 Days old = 30 d')
,('5 Days old = 5 d');
SELECT  *
FROM    @TestData a
ORDER BY a.IntervalType, a.IntervalValue;