带连接的复杂sql

iqxoj9l9 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(303)

我在mysql中创建了这三个表。我很难创建一个查询，它将为我提供一个组织的列表，其中列出了这些组织筹集到的资金总额。这些资金将来自未经培训的学生和捐赠者。任何帮助都将不胜感激。

CREATE TABLE STUDENT (
StudentID INT PRIMARY KEY,
StudentFirstName char(100) NOT NULL,
StudentLastName char(100) NOT NULL,
OrgID INT NOT NULL
AmountRaised INT NOT NULL,
Captain Char(3) Null,
);

CREATE TABLE ORGANIZATIONS(
OrgID INT PRIMARY KEY,
OrgName char(100) Not Null,
OrgCategory char(100) Not NULL,
AmountRaised int NOT NULL
);

CREATE TABLE KIDS (
KidId int PRIMARY KEY,
KidFirstName char(100) NOT NULL,
KidLastName char(100) NOT NULL,
OrgID INT NOT NULL,
);

CREATE TABLE DONORS(
DonorID int Primary Key,
DonorFirstName char(100) NOT NULL,
DonorLastName char(100) NOT NULL,
DonorOrganization char(100) NOT NULL,
AmountContributed int NOT NULL,
);

sql mysql

来源：https://stackoverflow.com/questions/50055522/complex-sql-with-joins

2条答案

按热度按时间

6uxekuva1#

我倾向于：

select orgid, sum(AmountRaised) as AmountRaised, sum(AmountDonated) as AmountDonated,
       (sum(AmountRaised) + sum(AmountDonated)) as total
from ((select s.orgid, AmountRaised, 0 as AmountDonated
       from student s
      ) union all
      (select d.orgid, 0 as AmountRaised, AmountDonated
       from donors d
      )
     ) o
group by orgid;

你可以使用一个额外的 join 引入有关组织的其他数据。

赞(0）回复(0）举报 2021-06-23

huus2vyu2#

看起来是这样的：

SELECT id, sum(amount) FROM (
    (
        SELECT o.id as id, sum(d.AmountContributed) as amount
        FROM organizations o
        JOIN donors d ON d.donororganization = o.id
        GROUP BY o.id
    ) AS t
    UNION ALL
    (
        SELECT o.id as id, sum(s.AmountRaised) as amount
        FROM organizations o
        JOIN student s ON s.orgid = o.id
        GROUP BY o.id
    ) AS t
) as t
GROUP BY o.id;

赞(0）回复(0）举报 2021-06-23

我来回答

带连接的复杂sql

2条答案

相关问题

热门标签

最新问答