gpt4 book ai didi

mysql - 如何从多个数据库动态获取数据?

转载 作者:行者123 更新时间:2023-11-29 09:36:20 25 4
gpt4 key购买 nike

我在MySQL中有多个数据库来自不同的公司。前任。

1.Company1
2.Company2
3.Company3
4.Company4

在每个数据库中,表和列的结构都是相同的,但数据是为不同的公司存储的。现在,如果我必须获取不同公司的 EmployeeID 销售额计数,那么我需要编写如下查询。

    Select a.EmployeeID,Count(b.TransactionDate)
From Company1.Employee as a
Inner Join Company1.Sales as b
On a.EmployeeID=b.EmployeeID
Group By a.EmployeeID
Union
Select a.EmployeeID,Count(b.TransactionDate)
From Company2.Employee as a
Inner Join Company2.Sales as b
On a.EmployeeID=b.EmployeeID
Group By a.EmployeeID
Union
Select a.EmployeeID,Count(b.TransactionDate)
From Company3.Employee as a
Inner Join Company3.Sales as b
On a.EmployeeID=b.EmployeeID
Group By a.EmployeeID
Union
Select a.EmployeeID,Count(b.TransactionDate)
From Company4.Employee as a
Inner Join Company4.Sales as b
On a.EmployeeID=b.EmployeeID
Group By a.EmployeeID

请注意,我正在使用硬编码值更改“FROM”子句和“INNER JOIN”中的数据库。

将来可能会添加更多数据库,我不想更改后面的代码,或者我不想添加带有另一个“联盟”的代码。我们可以做些什么来动态地做到这一点吗?我的意思是,如果我们可以将数据库名称存储在表中,并且查询应该自动从表中选取这些数据库信息。

最佳答案

我一直在等待点赞和致谢;)

有了这个数据库(当然它们都是一样的,所以我只发布其中一个。

use `company3`;
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(`EmployeeID` int, `LastName` varchar(40), `Firstname` varchar(40), `Age` int)
;

INSERT INTO Employee
(`EmployeeID`, `LastName`, `Firstname`, `Age`)
VALUES
(1, 'Hansen', 'Han', 30),
(2, 'Svendson', 'Sven', 23),
(3, 'Pettersen', 'Peter', 20)
;
DROP TABLE IF EXISTS Sales;
CREATE TABLE Sales
(`EmployeeID` int, `TransactionDate` datetime)
;

INSERT INTO Sales
(`EmployeeID`, `TransactionDate`)
VALUES
(1, '2015-12-20 10:01:00'),
(1, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00')
;

还有这个存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSakesConut`()
BEGIN
DECLARE bDone INT;
DECLARE DBname TEXT;
DECLARE sqlstement LONGTEXT;
DECLARE n INT;
DECLARE curs CURSOR FOR SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE 'company%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
OPEN curs;

SET bDone = 0;
SET n =0;
SET sqlstement = '';
SALESloop: LOOP
FETCH curs INTO DBname;
IF bDone = 1 THEN
LEAVE SALESloop;
END IF;
IF n>0 THEN
SET sqlstement = CONCAT(sqlstement,' UNION ');
END IF;
SET sqlstement = CONCAT(sqlstement,'Select "',DBname,'",a.EmployeeID,');
SET sqlstement = CONCAT(sqlstement,'Count(b.TransactionDate) ');
SET sqlstement = CONCAT(sqlstement,'From ',DBname,'.Employee as a ');
SET sqlstement = CONCAT(sqlstement,'Inner Join ',DBname,'.Sales as b ');
SET sqlstement = CONCAT(sqlstement,'On a.EmployeeID=b.EmployeeID ');
SET sqlstement = CONCAT(sqlstement,'Group By a.EmployeeID ');
SET n =n+1;

END LOOP SALESloop;
CLOSE curs;
SET @sqlstement = sqlstement;
PREPARE stmt FROM @sqlstement;
EXECUTE stmt;
END

解释一下:对于游标 curs,我获取所有以 compan 开头的数据库名称在循环中,我得到一个又一个的数据 enzyme 名称,然后用它进行构建您的 select 语句具有正确的数据库名称。当然,您必须将联合添加到所有没有第一个的选择

您得到以下结果

company1   EmployeeID   Count(b.TransactionDate)
company1 1 2
company1 2 3
company2 1 2
company2 2 3
company3 1 2
company3 2 3

当然,我必须熟练使用 select 语句,因为你的语句无法正常工作。

关于mysql - 如何从多个数据库动态获取数据?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57526981/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com