在状态字段中获取空值，任何人都可以解释我在做什么错吗？

Question

我想根据情况来确定状态字段，但是我得到的是空值。

Declare @Status NVarchar(20);
Set @Status = Case When Exists ( Select GunSerialNo
                                 From   dbo.ArmouryIssueGun
                                 Where  ModifiedOn != Null
                                        And CreatedOn != Null ) Then 'In Field'
                   When Exists ( Select GunSerialNo
                                 From   dbo.ArmouryIssueGun
                                 Where  ModifiedOn = Null
                                        And CreatedOn != Null ) Then 'In Armory'
              End;
   -- Insert statements for procedure here

Select  (Select BranchName
         From   Branch
         Where  BranchId = gun.BranchId
        ) As BranchName
      , gun.SerialNo As GunSerialNo
      , gun.GunType
      , gun.ModelNo
      , gun.GunId
      , Convert(Varchar(12), aig.CreatedOn, 103) IssueDate
      , Substring(Convert(Varchar(20), aig.CreatedOn, 9), 13, 5) + ' ' + Substring(Convert(Varchar(30), aig.CreatedOn, 9), 25, 2) As IssueTime
      , cl.LicenceHolderName As CarriedBy
      , (Select TypeName
         From   dbo.CommonValues
         Where  ID = aig.Purpose
        ) As Purpose
      , @Status As status
      , Convert(Varchar(12), aig.ModifiedOn, 103) As CollectedDate
      , Substring(Convert(Varchar(20), aig.ModifiedOn, 9), 13, 5) + ' ' + Substring(Convert(Varchar(30), aig.ModifiedOn, 9), 25, 2) As TimeIn
From    dbo.CarryAndUseLicence cl
Join    dbo.Branch b
        On b.BranchId = cl.BranchId
Join    dbo.Gun gun
        On cl.GunSerialNo = gun.SerialNo
Join    dbo.ArmouryIssueGun aig
        On aig.StaffId = cl.StaffId;

Answer 1

您的子查询没有关联子句！他们所做的只是查询查询表中是否有任何一行不为null。

如果您对特定状态感兴趣，则GunSerialNo必须查询该状态，例如：

SELECT
FROM dbo.ArmouryIssueGun
WHERE
   ModifiedOn IS NOT NULL
   AND CreatedOn IS NOT Null
   AND GunSerialNo = @GunSerialNo -- A specific GunSerialNo
;

但是，我怀疑您对 单个 项目的状态不感兴趣。相反，您想知道一堆行的状态。在这种情况下，将 单个
值存储在@Status变量中将无济于事。您需要将其合并到查询中，并将子查询与外部查询相关联，如下所示（请参见Status =表达式）：

Select  (Select BranchName
      From   Branch
      Where  BranchId = gun.BranchId
     ) As BranchName
   , gun.SerialNo As GunSerialNo
   , gun.GunType
   , gun.ModelNo
   , gun.GunId
   , Convert(Varchar(12), aig.CreatedOn, 103) IssueDate
   , Substring(Convert(Varchar(20), aig.CreatedOn, 9), 13, 5) + ' ' + Substring(Convert(Varchar(30), aig.CreatedOn, 9), 25, 2) As IssueTime
   , cl.LicenceHolderName As CarriedBy
   , (Select TypeName
      From   dbo.CommonValues
      Where  ID = aig.Purpose
     ) As Purpose
   , Status =
      Case When Exists (
         Select *
         From dbo.ArmouryIssueGun aig
         Where
            gun.SerialNo = aig.GunSerialNo // correlate to outer query
            AND aig.ModifiedOn != Null
            AND aig.CreatedOn != Null
      ) Then 'In Field'
      When Exists (
         Select * 
         From dbo.ArmouryIssueGun aig
         Where
            gun.SerialNo = aig.GunSerialNo // correlate to outer query
            AND aig.ModifiedOn = Null
            AND aig.CreatedOn != Null
      ) Then 'In Armory'
      End
   , Convert(Varchar(12), aig.ModifiedOn, 103) As CollectedDate
   , Substring(Convert(Varchar(20), aig.ModifiedOn, 9), 13, 5) + ' ' + Substring(Convert(Varchar(30), aig.ModifiedOn, 9), 25, 2) As TimeIn
From    dbo.CarryAndUseLicence cl
Join    dbo.Branch b
     On b.BranchId = cl.BranchId
Join    dbo.Gun gun
     On cl.GunSerialNo = gun.SerialNo
Join    dbo.ArmouryIssueGun aig
     On aig.StaffId = cl.StaffId;

另外，请注意这些可能对您有帮助的其他注释：

• 与进行比较时NULL， 无法 使用=或!=或<>。答案将始终是NULL，这既不是错误的，也不是正确的。您无能为力。x.Column != NULL将被视为false，将被视为false NOT (x.Column != NULL)。您必须执行x.Column IS NULL或x.Column IS NOT NULL或NOT (x.Column IS NULL)。

• 似乎数据库设计可能存在严重的数据一致性错误。用ModifiedOn代理来判断是否检出枪支是 完全不安全的 。该设计假定一条“快乐之路”，在该枪支被检出之前，枪支不能被修改（很可能被违反），并且一旦被修改，它就不能仍然留在军械库中（例如，被检入）。如果您不立即通过某些实际状态栏或其他机制来指示喷枪的当前状态来纠正此问题，则系统可能会遇到 严重 问题。

• 如果ArmouryIssueGun表与每把枪具有一一对应的关系，那么出了点问题，除非绝对，总是，在每种情况下每把枪只能一次发行的所有情况都是100％可靠。这似乎极不可能。这 可能 是ArmouryIssueGun真的不是一个历史表，但表示的部分Gun表（如在C＃中的部分类），并且因此是正确1- TO-（0或）1.在这种情况下，我的有关使用评论ModifiedOn将绝对不可靠，无法判断枪支是否已送出战场。

• 如果我怀疑（并且应该是）一对多关系，那么使用相当于历史日志表的值来检测事物的当前状态通常不是最佳设计。可能会犯错误。日志条目可能无法插入。应该有一个规范的地方，每个地方都在同一行中GunSerialNo。使用历史表可以使不知道您在此查询中使用过奇怪的代理依赖关系的以后的人有可能认为仅插入另一ArmoryIssueGun行即可解决问题，但是您的查询假定仅具有一个ModifiedOn值证明它 目前 在现场，而不仅仅是 过去某个时候 的现场。

• 每个表中的列名都应该相同。你应该 不会 有一个名为一列SerialNo，另一个叫相同的含义GunSerialNo。

• 我可以看到枪的序列号如何为您的数据库提供一个相当不错的自然/业务密钥。但是，我怀疑在某些情况下对于所有表来说，它都不是一个很好的钥匙。序列号多少个字符？一个int仅使用4个字节，但是如果您的序列号是20个字符，那么每一行将占用该序列号5倍的空间。请记住，在每个非聚集索引中都会重复聚集索引列。我不知道您的数据库的大小，但是例如，如果您要创建一个服务于整个国家陆军的数据库，那么理论上可能有数百万个条目！到那时，性能才真正开始变得重要，列占用的空间也变得很大。

• 两次连接到同一张表不是最佳选择。您可以像上面那样更改上面的查询，以使连接仅发生一次，并且还允许SELECT子句甚至包含找到的最新行中的列：

  OUTER APPLY (
  SELECT TOP 1
     aig.*
  FROM
     dbo.ArmoryIssueGun aig
  WHERE
     gun.SerialNo = aig.GunSerialNo
  ORDER BY
     aig.ModifiedOn DESC
  

  ) aig

• 也没有理由进行子查询，例如您已经完成的子查询BranchName。它会使查询混乱，并且不一致。相反，请始终使用JOIN：

  LEFT JOIN Branch b
  

  ON gun.BranchId = b.BranchId

然后，您可以简单地b.BranchName在SELECT子句中使用，还可以从Branch表中拉出其他列。根据我的经验，使用这样的子查询会导致某些思考习惯，这些习惯会产生次优的组织且难以理解的查询，有时甚至会对性能产生负面影响（例如，如果您要从Branch表中查找另一列，而您又抛出了另一个子查询，那么您不必要地将获取数据的成本增加了一倍）。