如何同时从三个表中返回行？

Question

如何将这三个查询合并为一个？

1.

SELECT "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid", MIN("TrainerScores"."score") AS "score"
FROM "TrainerScores"
INNER JOIN "Skills" ON "TrainerScores"."skill_id" = "Skills"."id"
WHERE "TrainerScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("TrainerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid"

2.

Select "Skills"."name", "Skills"."id", MIN("PeerScores"."score") AS "score"
FROM "PeerScores"
LEFT OUTER JOIN "Skills" ON "PeerScores"."skill_id" = "Skills"."id"
WHERE "PeerScores"."evaluatee_uid" = 'google:105697533513134511631'
AND DATE("PeerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

3.

Select "Skills"."name", "Skills"."id", MIN("SelfScores"."score") AS "score"
FROM "SelfScores"
LEFT OUTER JOIN "Skills" ON "SelfScores"."skill_id" = "Skills"."id"
WHERE "SelfScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("SelfScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

我想把它作为一个报告，我不想在任何时候调用每个查询，我想要得到的数据。

Answer 1

基本上，像使用UNION ALL一样使用@jarlh已经提供。

“合并查询”章中手册中的详细信息。

但是还有一个更多的。我受过教育的猜测，你真的想要这样：

WITH vals AS (SELECT timestamp '2015-10-01 00:00' AS ts_low  -- incl. lower bound
                   , timestamp '2015-10-31 00:00' AS ts_hi   -- excl. upper bound
                   , text 'google:105697533513134511631' AS uid)
SELECT s.name, sub.*
FROM  (
   SELECT skill_id AS id, min(score) AS score, 'T' AS source
   FROM   "TrainerScores", vals v
   WHERE  fellow_uid =  v.uid
   AND    created_at >= v.ts_low
   AND    created_at <  v.ts_hi
   GROUP  BY 1

   UNION ALL
   SELECT skill_id, min(score), 'P'
   FROM   "PeerScores", vals v
   WHERE  evaluatee_uid = v.uid
   AND    created_at >= v.ts_low
   AND    created_at <  v.ts_hi
   GROUP  BY 1

   UNION ALL
   SELECT skill_id, min(score), 'S'
   FROM   "SelfScores", vals v
   WHERE  fellow_uid =  v.uid
   AND    created_at >= v.ts_low
   AND    created_at <  v.ts_hi
   GROUP  BY 1
   ) sub
JOIN   "Skills" s USING (id);

要点

• 首先，我删除了语法中的噪声(可能是ORM产生的)，以使其具有可读性:删除多余的双引号，添加表别名，修剪噪声词.
• 您对LEFT [OUTER] JOIN的使用中断了，因为您在左表的列上进行了筛选，这与LEFT JOIN相抵。用[INNER] JOIN代替。
• 在易懂子句中使用WHERE表达式，否则查询就不能使用普通索引，对于大表来说会非常慢。相关信息：

- [Get difference of another field between first and last timestamps of grouping](https://stackoverflow.com/questions/20565421/get-difference-of-another-field-between-first-and-last-timestamps-of-grouping/20574117#20574117)

​

• 在CTE (子句)中提供参数一次 (WITH子句)-这在准备好的语句中是不需要的，而是将uid、ts_low和ts_hi作为参数传递。
• 为了简化查询，我从第一个查询的输出中删除了"TrainerScores"."fellow_uid"。那只是你的输入参数。
• 您可以在加入到的之前将各自的主表聚合为"Skills"。
• 我添加了一个列source来表示每一行的源。

旁白:你似乎想要与整个2015年10月相匹配，但之后你不包括10月31日。是故意的吗？

Answer 2

替代方案1，简单地说是一个巨大的UNION ALL

SELECT "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid", MIN("TrainerScores"."score") AS "score"
FROM "TrainerScores"
INNER JOIN "Skills" ON "TrainerScores"."skill_id" = "Skills"."id"
WHERE "TrainerScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("TrainerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id", "TrainerScores"."fellow_uid"

UNION ALL

Select "Skills"."name", "Skills"."id", NULL, MIN("PeerScores"."score") AS "score"
FROM "PeerScores"
LEFT OUTER JOIN "Skills" ON "PeerScores"."skill_id" = "Skills"."id"
WHERE "PeerScores"."evaluatee_uid" = 'google:105697533513134511631'
AND DATE("PeerScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

UNION ALL

Select "Skills"."name", "Skills"."id", NULL, MIN("SelfScores"."score") AS "score"
FROM "SelfScores"
LEFT OUTER JOIN "Skills" ON "SelfScores"."skill_id" = "Skills"."id"
WHERE "SelfScores"."fellow_uid" = 'google:105697533513134511631'
AND DATE("SelfScores"."created_at") BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY "Skills"."name", "Skills"."id"

Answer 3

我提出的解决方案完全符合你的要求，但效果很好。使用未加工查询，您可以运行并获取多个查询的结果，如下所示：

var sequelize = require('./libs/pg_db_connect');

var query = "SELECT Skills.name, Skills.id, TrainerScores.fellow_uid, MIN(TrainerScores.score) AS score
FROM TrainerScores
INNER JOIN Skills ON TrainerScores.skill_id = Skills.id
WHERE TrainerScores.fellow_uid = 'google:105697533513134511631' AND DATE(TrainerScores.created_at) BETWEEN '2015-10-01' AND '2015-10-30'
GROUP BY Skills.name, Skills.id, TrainerScores.fellow_uid";


sequelize.query(query, {
   type: sequelize.QueryTypes.SELECT
}).success(function (query1) {
   done = _.after(query1.length, function () {
      callback(query1)
   })

   query = "Select Skills.name, Skills.id, MIN(PeerScores.score) AS score
        FROM PeerScores
        LEFT OUTER JOIN Skills ON PeerScores.skill_id = Skills.id
        WHERE PeerScores.evaluatee_uid = 'google:105697533513134511631' AND DATE(PeerScores.created_at) BETWEEN '2015-10-01' AND '2015-10-30'
        GROUP BY Skills.name, Skills.id";

   sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT
   }).success(function (query2) {

      query = "Select Skills.name, Skills.id, MIN(SelfScores.score) AS score
        FROM SelfScores
        LEFT OUTER JOIN Skills ON SelfScores.skill_id = Skills.id
        WHERE SelfScores.fellow_uid = 'google:105697533513134511631' AND DATE(SelfScores.created_at) BETWEEN '2015-10-01' AND '2015-10-30'
        GROUP BY Skills.name, Skills.id";

      sequelize.query(query, {
         type: sequelize.QueryTypes.SELECT
      }).success(function (query3) {
         console.log(query1); // show the returns of query 1
         console.log(query2); // show the returns of query 2
         console.log(query3); // show the returns of query 3
      });

success函数的sequelize.query结果也可以存储在json变量中。