gpt4 book ai didi

具有额外字段的 MySQL 链接表创建笛卡尔积

转载 作者:行者123 更新时间:2023-11-30 23:23:01 24 4
gpt4 key购买 nike

我正在尝试构建一个应用程序,除了记录销售数量外,还可以维护主管与其团队之间的历史记录。

CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`nombre` varchar(45) NOT NULL,
`apellido` varchar(45) NOT NULL,
`tl_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tlFK_idx` (`tl_id`),
CONSTRAINT `tlFK` FOREIGN KEY (`tl_id`) REFERENCES `emp` (`id`)


CREATE TABLE `super` (
`id` int(11) NOT NULL,
`nombre` varchar(45) NOT NULL,
`apellido` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
)

CREATE TABLE `emp_super` (
`emp_id` int(11) NOT NULL,
`super_id` int(11) NOT NULL,
`fecha` date NOT NULL,
PRIMARY KEY (`emp_id`,`super_id`,`fecha`),
KEY `empFK_idx` (`emp_id`),
KEY `superFK_idx` (`super_id`),
CONSTRAINT `empsuperFK` FOREIGN KEY (`emp_id`) REFERENCES `emp` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `super_empFK` FOREIGN KEY (`super_id`) REFERENCES `super` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

如您所见,表 emp_super 是链接表,但有一个日期字段。

emp_id super_id fecha
101863 101404 2012-10-15
101863 101503 2012-10-01
102403 101404 2012-10-15
102403 101503 2012-10-01
103052 101404 2012-10-15
103052 101503 2012-10-01
103718 101404 2012-10-15
103718 101503 2012-10-01

关键是记录主管何时分配到一个小组,这样当有任何变化时,新主管就不会继承前任领导下的小组绩效。我一直在处理查询以提取特定组的销售额,但我立即注意到,尽管我认为我建立了所有正确的联系,但我收到了笛卡尔积。

这是销售表:

CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sale_date` date NOT NULL,
`product_id` int(11) NOT NULL,
`emp_id` int(11) NOT NULL,
`qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `empFK_idx` (`emp_id`),
KEY `campaignFK_idx` (`product_id`),
CONSTRAINT `empFK` FOREIGN KEY (`emp_id`) REFERENCES `emp_super` (`emp_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `productFK` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

通常,在处理日期时,我会尝试将日期限制在我的数据来自的主表中,但在这种情况下,我还需要考虑员工-主管关系的日期。

到目前为止我的查询是这样的:

SELECT s.sale_date,su.id,concat_ws(' ',su.nombre,su.apellido), p.name,e.id,concat_ws(' ',e.nombre,e.apellido), s.qty
from tracker.emp e, tracker.products p, tracker.sales s,tracker.super su, tracker.emp_super es
where e.id = es.emp_id
and su.id = es.super_id
and s.product_id = p.id
and e.id = s.emp_id
and s.sale_date between '2012-10-01' and '2012-10-15'
and es.fecha between '2012-10-01' and '2012-10-15'
order by su.id,p.name,e.id;

如果我将日期减少到 14,那么我会收到正确的结果,因为另一个主管直到 15 才加入。有没有人遇到过这种业务需求:维护领导记录,以便新领导不继承前人的成就还是错误?

感谢您提出任何建议。

最佳答案

您需要考虑监督角色何时结束。

这会给你那个,但我怀疑它不会很有效。

SELECT e1.emp_id 
,e1.super_id
,e1.fecha
,MIN(DATEADD(DAY, -1, e2.fecha)) AS end
FROM emp_super e1
INNER JOIN
emp_super e2 ON e1.emp_id=e2.emp_id
AND
e1.fecha<e2.fecha
GROUP BY e1.emp_id
,e1.super_id
,e1.fecha

也许由 TRIGGER 更新的链接表的结束列会更好?

关于具有额外字段的 MySQL 链接表创建笛卡尔积,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14719218/

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