如何通过数据透视表选择具有特定关系的行? [英] How to select rows that have certain relationships through a pivot tabel?

问题描述

示例数据库设计

  USERS            WORKDAYS                DAYS
[id] [name]      [user_id] [day_id]      [id] [name]
 1    john        1         2             1    sunday
 2    fred        1         3             2    monday
 3    bert        1         4             3    tuesday
 4    harry       1         5             4    wednesday
                  1         6             5    thursday
                  2         2             6    friday
                  4         1             7    saturday
                  4         2
                  4         3
                  4         4
                  4         5
                  4         6
                  4         7

我将如何查询

1. 像约翰这样的人，他在一周的所有工作日完全工作:星期一、星期二、星期三、星期四、星期五
2. 像哈利和约翰这样至少一直在工作的人吗?

1. Someone like John, who works exactly all workdays of the week: monday, tuesday, wednesday, thursday, friday
2. Someone like Harry and John who works at least all those days?

我使用 mysql 有一段时间了，但目前我找不到解决方案.通常我会为此使用位标志，但我尝试在 SQL 中掌握更规范化的解决方案.

I work with mysql some time but I cannot find a solution at the moment. Normally I would use a bitflag for this but I try to grasp a more normalized solution in SQL.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `days` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `workdays` (
  `user_id` int(11) NOT NULL,
  `day_id` int(11) NOT NULL,
  KEY `workdays_users_FK` (`user_id`),
  KEY `workdays_days_FK` (`day_id`),
  CONSTRAINT `workdays_days_FK` FOREIGN KEY (`day_id`) REFERENCES `days` (`id`),
  CONSTRAINT `workdays_users_FK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO play.users
(id, name)
VALUES(1, 'john');
INSERT INTO play.users
(id, name)
VALUES(2, 'fred');
INSERT INTO play.users
(id, name)
VALUES(3, 'bert');
INSERT INTO play.users
(id, name)
VALUES(4, 'harry');

INSERT INTO play.days
(id, name)
VALUES(1, 'sunday');
INSERT INTO play.days
(id, name)
VALUES(2, 'monday');
INSERT INTO play.days
(id, name)
VALUES(3, 'tuesday');
INSERT INTO play.days
(id, name)
VALUES(4, 'wednesday');
INSERT INTO play.days
(id, name)
VALUES(5, 'thursday');
INSERT INTO play.days
(id, name)
VALUES(6, 'friday');
INSERT INTO play.days
(id, name)
VALUES(7, 'saturday');

INSERT INTO play.workdays
(user_id, day_id)
VALUES(1, 2);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(1, 3);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(1, 4);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(1, 5);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(1, 6);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(2, 2);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(4, 1);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(4, 2);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(4, 3);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(4, 4);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(4, 5);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(4, 6);
INSERT INTO play.workdays
(user_id, day_id)
VALUES(4, 7);

推荐答案

• 您可以对用户 ID 和名称进行GROUP BY.
• 使用 HAVING 子句和 SUM() 聚合来过滤案例.
• 如果用户在某一天不工作，则该天的 SUM() 将为零，发布加入.
• You can GROUP BY on user id and name.
• Use HAVING clause with SUM() aggregation to filter out cases.
• If a user does not work on a particular day, SUM() for that day will be zero, post joins.

对于第一种情况(完全适用一周的所有工作日)，请尝试:

For first case (works exactly all workdays of the week), Try:

SELECT u.id, u.name
FROM USERS AS u 
JOIN WORKDAYS AS wd ON wd.user_id = u.id 
JOIN DAYS AS d ON d.id = wd.day_id 
GROUP BY u.id, u.name 
HAVING SUM(d.name = 'monday') 
   AND SUM(d.name = 'tuesday') 
   AND SUM(d.name = 'wednesday') 
   AND SUM(d.name = 'thursday') 
   AND SUM(d.name = 'friday') 
   AND SUM(d.name = 'sunday') = 0 
   AND SUM(d.name = 'saturday') = 0

对于第二种情况，只需移除 sunday 和 saturday 的条件.尝试:

For second case, just remove the conditions on sunday and saturday. Try:

SELECT u.id, u.name
FROM USERS AS u 
JOIN WORKDAYS AS wd ON wd.user_id = u.id 
JOIN DAYS AS d ON d.id = wd.day_id 
GROUP BY u.id, u.name 
HAVING SUM(d.name = 'monday') 
   AND SUM(d.name = 'tuesday') 
   AND SUM(d.name = 'wednesday') 
   AND SUM(d.name = 'thursday') 
   AND SUM(d.name = 'friday') 

这篇关于如何通过数据透视表选择具有特定关系的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！