时间戳

首页 > 解决方案 > SQL查询：如何将整数更改为布尔值

sql - SQL查询：如何将整数更改为布尔值

问题描述

我正在使用 Firebird 2.5.8 和 Delphi 10.2.3，我想用查询填充 DBGrid：

SELECT c.ID, l.ID,
(
SELECT COUNT(pl.ID)
FROM Tbl_ProtocolLicense AS pl
WHERE (pl.ReferenceId=l.ID)
) AS ReferenceCount

FROM  Tbl_License AS l, tbl_client AS c
WHERE l.ClientId=c.Id;

如何将值（ ReferenceCount > 0 ）作为布尔值或（0/1）添加到该查询？

标签： sqldelphifirebird

解决方案

为什么还要使用会为每一行一次又一次地重新计算的相关查询？

第一个查询实际上不起作用。太仓促了。

SELECT 
  c.ID,
  l.ID, 
  IIF( r.CNT > 0, 1, 0 )
FROM Tbl_License AS l
  JOIN tbl_client AS c ON l.ClientId=c.Id
  JOIN (
    SELECT COUNT(*) as CNT, ReferenceId as ID
    FROM Tbl_ProtocolLicense 
    GROUP BY 2
        ) as r ON r.ID = l.ID

注意：这假设该Tbl_ProtocolLicense.ID列是 never NULL。

UPD。我在http://stackoverflow.com/a/51159126/976391上做了一些关于 COUNT 和其他聚合的讲座- 但在这里我自己错过了。

 SELECT COUNT(*) as CNT, ReferenceId as ID
        FROM Tbl_ProtocolLicense 
        GROUP BY 2

运行查询并查看结果。注意到有什么可疑的吗？

此查询仅返回确实存在的行，而不返回那些不存在的行。

中间分组查询不会有一行，其中 count=0 ！因此，整个基于 Inner Join 的查询也不会有它们！

我们应该做的是使用外连接，即使在另一个表中没有匹配的行时也可以让行存在。阅读：https://en.wikipedia.org/wiki/Join_(SQL)

SELECT 
  c.ID,
  l.ID, 
  IIF( r.CNT is not NULL, 1, 0 )
FROM Tbl_License AS l
  JOIN tbl_client AS c ON l.ClientId=c.Id
  LEFT JOIN (
    SELECT COUNT(*) as CNT, ReferenceId as ID
    FROM Tbl_ProtocolLicense 
    GROUP BY 2
        ) as r ON r.ID = l.ID

将输出与第一个查询进行比较并查看差异。

UPD 2。但即使这样也不够好，可能。这里的问题是“你说你想要你实际上并不想要的东西”。

当您真的不关心计数时，您要求 Firebird 计数所有行。您所关心的只是“是否至少有一排或根本没有”。如果只有一行 - 你不在乎是否会有 10 或 100 或 1000 更多。因此，当您不想计数对象时，实际计数对象是一项白费力气的额外工作。

这在 Interbase/Firebird 系列中尤其浪费，在表格中计数会触发garbage collection和减慢工作。但即使在纯 Delphi 中也是如此 - 如果您对找到第一个适合的元素感到满意，您不想遍历所有数组。

然后我们可以回到相关的子查询。

SELECT 
  c.ID,
  l.ID,
  IIF( EXISTS ( 
         SELECT * FROM Tbl_ProtocolLicense AS pl
         WHERE pl.ReferenceId=l.ID 
       ), 1, 0 )   
FROM  Tbl_License AS l, tbl_client AS c
WHERE l.ClientId=c.Id;
  • csq 苦涩的一面是它会为每个结果行一次又一次地运行
  • 计算分组总数的苦涩的一面是您实际上不需要该数据，也不需要确切的计数。

哪个更糟？谁知道。根据真实数据和真实表/索引 - 可能存在一种或另一种方法会更快的情况。人类不会注意到小数据的差异。这是关于“扩大”到成千上万的真实数据的问题，差异会在哪里显示出来。

UPD 3. 我们可以兼得这两种方法吗？我希望我们可以。诀窍是——准确地询问我们需要什么，仅此而已。我们可以要求 Firebird 列出我们在表中的所有 ID 而不实际计算它们吗？就在这里。

 SELECT DISTINCT ReferenceId FROM Tbl_ProtocolLicense

运行查询并查看结果！

请注意，它仍然不会列出不在表中的 ID。明显的？好吧，我在第一种方法中错过了它，然后两个支持我的人也错过了。愚蠢的错误是最难发现的，因为你无法相信这种愚蠢。

所以，现在我们必须插入它而不是“计数”第二次尝试的查询。

SELECT 
  c.ID,
  l.ID, 
  IIF( r.ReferenceId is NULL, 0, 1 )
FROM Tbl_License AS l
  JOIN tbl_client AS c ON l.ClientId=c.Id
  LEFT JOIN (
    SELECT DISTINCT ReferenceId 
    FROM Tbl_ProtocolLicense 
      ) as r ON r.ReferenceId = l.ID

UPD。4 最后一招。如果我是正确的，这个查询将得到与上面完全相同的结果，而不使用 IIF/CASE。尝试并比较。如果结果相同 - 然后尝试了解它为什么和如何工作，以及它需要哪些额外的数据假设。

SELECT 
  c.ID,
  l.ID, 
  COUNT( r.ReferenceId )
FROM Tbl_License AS l
  JOIN tbl_client AS c ON l.ClientId=c.Id
  LEFT JOIN (
    SELECT DISTINCT ReferenceId 
    FROM Tbl_ProtocolLicense 
      ) as r ON r.ReferenceId = l.ID
GROUP BY c.ID, l.ID

这个查询并不比 Upd.3 好，它只是一个思考然后更好地理解 SQL 的探索。

现在做一些工作来实际检查和比较结果，因为盲目相信互联网上的陌生人是不好的。即使那个人没有恶意，他也可以犯愚蠢的错误。

无论你从互联网论坛上偷看什么，那都只是“例子”和“思想示范”，理解和检查那个例子始终是你的责任。也许拒绝它。

阅读和理解：

此外，阅读一些关于一般 SQL 的好书，比如 Martin Gruber 的书，对你来说真的很有用

推荐阅读