SQLite select语句优化建议

Question

我有一个 SQLite 表 'Details' 的结构:

ID  Name    Category   
---------------------
1   Matt    0 
2   Shervin 0 
3   Bob     0   
4   Lee     0 
5   Rick    0   
6   Suraya  0 
7   Susan   0   
8   Adam    0   
9   Jon     1   
10  Lorna   1 
... and so on .......  

我想随机选择一行，然后从三个不同的行中选择三个名字(同样最好是随机选择)。我希望这一切都从一个 SQLite 语句返回。例如。

ID  Name  Category  Name1  Name2  Name 3  
----------------------------------------
3   Bob   0         Matt   Lee    Susan 

我的尝试如下所示，但它有两个问题:

1. 这三个额外的名称不一定总是不同 - 我似乎无法排除之前选择的名称，因为变量 b/c/d 不在其自身 COALESCE 函数之外的范围内。
2. 由于每个嵌套选择都使用 Random() 函数，所以效率不是很高。

谁能建议另一种方法来选择我需要的数据(使用 SQLite 数据库)？欢迎任何帮助/建议 - 希望清楚我正在努力实现的目标，随时要求任何澄清。

我目前的尝试:

SELECT a.Id,
       a.Name,
       a.Category,
       COALESCE((SELECT b.Name 
                   FROM Details b 
                  WHERE b.Id NOT IN (a.Id)
                    AND b.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name1",
       COALESCE((SELECT c.Name 
                   FROM Details c 
                  WHERE c.Id NOT IN (a.Id)
                    AND c.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name2",
       COALESCE((SELECT d.Name 
                   FROM Details d
                  WHERE d.Id NOT IN (a.Id)
                    AND d.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name3"
    FROM Details a
     AND a.Category IN (0)
ORDER BY Random()
   LIMIT 1

Answer 1

我在这里和 neurino 在一起。您没有说明为什么需要将随机选择的四个名称放在一行中，以及为什么必须在后端完成。

如果您担心性能，请在您的客户端中生成随机整数(范围 >= min(pkcol) 和 <= max(pkcol) )，直到您找到四个不同的行(即实体/名称)。有可能不存在具有其中一个生成的 ID 的行，但这只需要几毫秒就可以找出来。采用这种随机键方法，您可以避免订购。即使对于具有数十亿行的表，该方法也能很快奏效。

附言(后来发现是iPhone app)您需要一次调用来获取最小和最大 ID 值(它是 PK，因此使用索引)。然后，您至少需要再次调用数据库(同样是索引辅助)，以使用随机生成的 PK 值 [其中 ID 在 (a、b、c、d)] 中获取四个不同的行]最大调用次数未知;多少取决于您的主键序列的密度。我不认为这会是过多的 I/O，而且与 Random() 的命令相比，它占用的资源要少得多——尤其是在表有很多行的情况下。您始终可以随机生成一个包含 8、12、16 个 ID 的 ID 列表，如果返回的行超过 4 个，则让您的客户端仅剔除所需的 4 行。

附言通常情况下，数据库连接的实例化是昂贵的，并且您不希望在循环中或比您需要的更频繁地执行此操作。但是您可以打开一个连接，运行两个或三个高效的选择，每个选择返回几行，然后在您完成手头的任务后关闭。