如何合并多个SQL select语句 [英] How do I merge multiple SQL select statements

问题描述

#//SCHEMA
CREATE TABLE `orgs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `org_name` varchar(250) NOT NULL,
  UNIQUE (org_name)
)
 
CREATE TABLE `orgs_relation` (
  `org_id` int(10) UNSIGNED NOT NULL,
  `parent_org_id` int(10) UNSIGNED NOT NULL,
  CONSTRAINT org_relation_pair UNIQUE (org_id, parent_org_id)
)
 
 
#// Get parents
SELECT parent_org_id, orgs.org_name as org_name, "parent" as parent FROM `orgs_relation`
JOIN orgs ON orgs_relation.parent_org_id = orgs.id
WHERE org_id = (SELECT id FROM orgs WHERE org_name='Black Banana')
ORDER BY org_name ASC
 
#// Get chidren
SELECT org_id, orgs.org_name as org_name, "children" as children FROM `orgs_relation`
JOIN orgs ON orgs_relation.org_id = orgs.id
WHERE parent_org_id = (SELECT id FROM orgs WHERE org_name='Black Banana')
ORDER BY org_name ASC
 
#// Get sisters
SELECT DISTINCT or2.org_id AS sister_id, org_name, "sisters" as sisters FROM `orgs_relation` AS or1
JOIN orgs_relation AS or2 ON or1.parent_org_id = or2.parent_org_id
JOIN orgs ON or2.org_id = orgs.id
WHERE or1.org_id = (SELECT id FROM orgs WHERE org_name='Black Banana')
ORDER BY org_name ASC

我尝试过：

What I have tried:

var q = "SELECT org_id, orgs.org_name as org_name, 'daughters' as daughters FROM `orgs_relation` JOIN orgs ON orgs_relation.org_id = orgs.id  WHERE parent_org_id = (SELECT id FROM orgs WHERE org_name =  'Black Banana');SELECT parent_org_id, orgs.org_name as org_name, 'parent' as parent FROM `orgs_relation`JOIN orgs ON orgs_relation.parent_org_id = orgs.id WHERE org_id = (SELECT id FROM orgs WHERE org_name= 'Black Banana');SELECT DISTINCT or2.org_id AS sister_id, org_name, 'sisters' as sisters FROM `orgs_relation` AS or1 JOIN orgs_relation AS or2 ON or1.parent_org_id = or2.parent_org_id JOIN orgs ON or2.org_id = orgs.id WHERE or1.org_id = (SELECT id FROM orgs WHERE org_name= 'Black Banana')";
connection.query(q, function (error, results) {
  if (error) throw error;
  // console.log(results[0]);
  // console.log(results[1]);
  // console.log(results[2]);
  res.send(results);
   });
});

推荐答案

您可以使用 UNION 语句。

这会产生类似的结果：

You can use the UNION statement.
This would give something like:

SELECT `parent_org_id`,`orgs`.`org_name` as `org_name`, 'parent' as parent FROM `orgs_relation`
JOIN orgs ON orgs_relation.parent_org_id = orgs.id
WHERE org_id = (SELECT id FROM orgs WHERE org_name='Black Banana')

UNION

SELECT org_id, orgs.org_name, 'children' FROM `orgs_relation`
JOIN orgs ON orgs_relation.org_id = orgs.id
WHERE parent_org_id = (SELECT id FROM orgs WHERE org_name='Black Banana')

UNION

SELECT DISTINCT or2.org_id, org_name, 'sisters' FROM `orgs_relation` AS or1
JOIN orgs_relation AS or2 ON or1.parent_org_id = or2.parent_org_id
JOIN orgs ON or2.org_id = orgs.id
WHERE or1.org_id = (SELECT id FROM orgs WHERE org_name='Black Banana')

ORDER BY org_name ASC

参考： MySQL文档 - 13.2.9.3 UNION语法 [ ^ ]



亲切。

Reference: MySQL documentation - 13.2.9.3 UNION Syntax[^]

Kindly.

这篇关于如何合并多个SQL select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！