mysql - null 的警报数量小于特定值

Question

当“交易”表中的空值数量小于 2 时，我试图发出警报，甚至只是发出消息框。我的交易表有以下列:这是我的表“Transactions”中的数据

trans_id     taxi_id     plate_num    body_num     time_in        time_out    trans_date
90001        11001        fdf343        343      10:00:00 AM                 11/11/2013
90002        11004        pgj847        456      11:00:00 AM                 11/11/2013
90003        11005        fng455        008      11:30:00 AM                 11/11/2013

正如您所看到的“time_out”列没有值，我如何计算“time_out”列中空值的数量，如果“time_out”列中包含空值的行数少于2行，则会出现警告或者会弹出一个消息框!提前致谢。顺便说一句，我几乎不需要这样做。 :)这是我的代码:我知道它很糟糕!

Private Sub Form_Load()
On Error Resume Next

Dim qty As Integer
rsglob.Open "(select count(*) from Transactions where time_out is null) Nulls", "(select count(Nulls) from Transactionwhere time_out is null) Nulled, connglob, adOpenStatic, adLockOptimistic"
    qty = rsglob("Nulled")
    If (qty > 1) Then
        MsgBox "Your quantity entered exceeds the current unit stock of the product"

        Exit Sub
    End If

Call connect
sqlquery = "SELECT * FROM Transaction where trans_id > 90000 "

rsglob.Open sqlquery, connglob
Set DataGrid1.DataSource = rsglob

End Sub

Answer 1

您需要以下 SQL 查询:

1) 用于计算“time_out”列中空值的数量:

select count(*) from Transactions where time_out is null

2) 查找“time_out”列中空值是否少于 2 行:

select count(*) as NumberOfNulls from Transactions where time_out is null group By time_out having NumberOfNulls < 2