顺序列where条件 - SQL /访问 [英] Sequential Column Where Conditional - SQL / Access

问题描述

假设我们有一些看起来像这样的数据

  R_Id Nm Base Dest Proj Cust_id 201203 201202 201201 
 MRBR Bob LONDON英国项目1 1 0 0 0 
 MRBU弗兰克伦敦伦敦项目2 11.68 0 248.93 
 MRBU弗兰克伦敦英国项目3 1 7.4 4.8 0 
 MRGB巴里·吉尔德福德船体项目4 1 50.36 12.85 48.92 
 MRGB Barry GUILDFORD Project5 1 0 177.31 0 
 MRGB Barry GUILDFORD INTL Project6 3 0 331.08 0 
  

假设我们有比上面更多的列，但现在我们只限于几个。

我想能够使用一个where statment显示行需要进一步调查的行。这是通过说在哪里有两个以上的大数字在一行中彼此相邻？所以我需要计算数字很大的行数。

输出应该是这样，我已经解释了我正在做什么过滤。 / p>

  R_Id Nm Base Dest Proj Cust_id 201203 201202 201201 
 MRBR Bob LONDON UK Project1 1数字不大
 MRBU弗兰克伦敦伦敦项目2 2 11.68 248.93 0 
 MRBU弗兰克伦敦英国项目3 1数字不大
 MRGB巴里·吉尔德福德船体项目4 1 50.36 12.85 48.92 
 MRGB巴里·吉尔德里项目5 1太少相邻数字
 MRGB Barry GUILDFORD INTL Project6 3太少邻近数字
  

我试图过滤的邻域数过少的情况。我需要计算这些特定列中相邻（或每隔一个！）数字的数字。

我看过这个问题：多列条件计数SQL ，但我不认为我可以使用Count（*），因为我得到这个错误：你试图执行一个查询不包括指定表达式AT_RISK？作为聚合函数的一部分。风险是一个只存储是/否的列，并且生活在R_Id的左边（为简洁起见，以上不包括）

任何人都可以帮助或至少指向我正确的方向请吗？我真的很感激。我已经阅读了上面的问题，我已经看了一下如何使用count，但这真的让我失望。

解决方案

连接和测试字符串

您的SQL语句应该是：

  SELECT * FROM tblName 
 WHERE IsSeqHigh（[201203]&;& [201202]&;& ...，1000）; 
  

然后，在VBA模块中我们定义：

 公共函数IsSeqHigh（seq As String，thres As Double）As Boolean 
 IsSeqHigh = False 
 
 Dim valStrs（）As String 
 valStrs = Split（seq，;）
 
对于n = 1到UBound（valStrs） -  1 
如果（valStrs（n）> = thres） n + 1）= thres）then 
 IsSeqHigh = True 
退出对于
结束如果
下一个n 
结束函数
  
 
 
 
另一种方法
 
 
 
或者，如果您的模式是固定的，您有一个主键值，您可以编写一个VBA函数，它采用主键值，并扫描您正在寻找的特定条件的列。
 
 
 
总之，没有好的SQL解决方案，我可以想到。
 
Suppose we have some data that looks like so
R_Id    Nm      Base       Dest    Proj      Cust_id  201203   201202   201201
MRBR    Bob     LONDON     UK      Project1  1        0        0        0
MRBU    Frank   LONDON     London  Project2  2        11.68    0        248.93
MRBU    Frank   LONDON     UK      Project3  1        7.4      4.8      0
MRGB    Barry   GUILDFORD  Hull    Project4  1        50.36    12.85    48.92
MRGB    Barry   GUILDFORD          Project5  1        0        177.31   0
MRGB    Barry   GUILDFORD  INTL    Project6  3        0        331.08   0
And suppose we have a lot more columns than above, but we've limited to a few for now.

I want to be able to use a where statment to only show rows where the row needs further investigation. This is done by saying "Where are there more than two large numbers next to each other in a row?" So I need to count the number of rows where the number is large.

The output should look like such, where I've explained what filtering I'm doing.
R_Id    Nm      Base       Dest    Proj      Cust_id  201203   201202   201201
MRBR    Bob     LONDON     UK      Project1  1        "Numbers not Large"
MRBU    Frank   LONDON     London  Project2  2        11.68    248.93   0   
MRBU    Frank   LONDON     UK      Project3  1        "Numbers not Large"
MRGB    Barry   GUILDFORD  Hull    Project4  1        50.36    12.85    48.92
MRGB    Barry   GUILDFORD          Project5  1        "Too few adjacent numbers"
MRGB    Barry   GUILDFORD  INTL    Project6  3        "Too few adjacent numbers"
It's the case where there are too few adjacent numbers I'm trying to filter for. I need to count the numer of adjacent (or every other!) numbers in those specific columns.

I've looked at this question: Multiple Column Conditional Count SQL, but I don't think I can use Count(*) as I get this error: You tried to execute a query thaty does not include the speicified expression 'AT_RISK?' as part of aggregate function. At risk is a column that just stores Yes/No and lives to the left of R_Id (not included above for brevity)

Can anyone help or at least point me in the right direction please? I'd really appreciate it. I've read the question above, and I've looked at how to use count in general, but this is really stumping me.
解决方案
Well I can think of a somewhat ugly solution to this question, but it involves the use of a custom VBA function.

Concatenate and Test String

Your SQL statement should be something like:
SELECT * FROM tblName 
WHERE IsSeqHigh([201203] & ";" & [201202] & ";" & ..., 1000);
And then, in a VBA module we define:
Public Function IsSeqHigh(seq As String, thres As Double) As Boolean
    IsSeqHigh = False

    Dim valStrs() As String
    valStrs = Split(seq, ";")

    For n = 1 To UBound(valStrs) - 1
        If (valStrs(n) >= thres) And (valStrs(n + 1) >= thres) Then
            IsSeqHigh = True
            Exit For
        End If
    Next n
End Function


Another approach

Alternatively, if your schema is fixed and unlikely to change - and you have a primary key value, you can write a VBA function which takes the primary key value and scans the columns for the specific condition you are looking for.

In short, there is no good SQL-only solution that I can think of.

                        
这篇关于顺序列where条件 -  SQL /访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！