SQLite中一个查询中的多个选择语句 [英] Multiple Select statements in one Query in SQLite

问题描述

是否可以在 SQLite 中的一个查询中运行多个 select 语句?

Is it possible to run multiple select statements in one query in SQLite?

例如:

SELECT ( 
           SELECT ChestGemEffects.Value,
                  Effect.Name
             FROM ChestGemEffects
                  INNER JOIN Effect
                          ON ChestGemEffects.EffectId = Effect.Id
                  INNER JOIN Gems
                          ON ChestGemEffects.GemId = Gems.Id
            WHERE ( Gems.[Key] = 'SG1' )  
       ) 
       AS ChestEffects,
       ( 
           SELECT WeaponGemEffects.Value,
                  Effect.Name
             FROM WeaponGemEffects
                  INNER JOIN Effect
                          ON WeaponGemEffects.EffectId = Effect.Id
                  INNER JOIN Gems
                          ON WeaponGemEffects.GemId = Gems.Id
            WHERE ( Gems.[Key] = 'SG1' )  
       ) 
       AS WeaponEffects,
       ( 
           SELECT OthersGemEffects.Value,
                  Effect.Name
             FROM OthersGemEffects
                  INNER JOIN Effect
                          ON OthersGemEffects.EffectId = Effect.Id
                  INNER JOIN Gems
                          ON OthersGemEffects.GemId = Gems.Id
            WHERE ( Gems.[Key] = 'SG1' )  
       ) 
       AS OthersEffects;

它给了我错误:

'执行查询时出错:作为表达式一部分的 SELECT 只允许一个结果'

'Error while executing query: only a single result allowed for a SELECT that is part of an expression'

我的表达有问题吗，还是 SQLite 不支持?

Is there something wrong with my expression or is this just not supported in SQLite?

谢谢

推荐答案

使用子查询的结果作为源表进行进一步查询必须在FROM子句中完成:

Using the result of a subquery as a source table for further querying must be done in the FROM clause:

SELECT * FROM (SELECT ...), (SELECT ...)

然而，这将是一个交叉连接，这不是你想要什么.

However, this would be a cross join, which is not what you want.

要附加多个表(列数相同)，请使用 UNION ALL:

To just append multiple tables (with the same number of columns), use UNION ALL:

SELECT ChestGemEffects.Value,
       Effect.Name
  FROM ...
  ...
UNION ALL
SELECT WeaponGemEffects.Value,
       Effect.Name
  FROM ...
  ...

这篇关于SQLite中一个查询中的多个选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！