我有一个带有许多表的MySQL数据库。与这个问题有关的是:TABLE: Schedule (For booking time on a machine) - SlotID (INTEGER - AUTO_INCREMENT - PRIMARY KEY) - SlotDate (VARCHAR) - SlotStart (INTEGER) - SlotStop (INTEGER) - UserID (INTEGER - FOREIGN KEY REFERENCES User.UserID) - OperatorID (INTEGER - FOREIGN KEY REFERENCES User.UserID)TABLE: Treatment (Defines what the machine should do) - TreatmentID (INTEGER - AUTO_INCREMENT - PRIMARY KEY) - SOME OTHER BOOLEANS TO ACTIVATE EACH SENSOR (Irrelevant)TABLE: ScheduleTreatment (Many to many relationships between Schedule & Treatment) - SlotID (INTEGER - FOREIGN KEY REFERENCES Schedule.SlotID) - TreatmentID (INTEGER - FOREIGN KEY REFERENCES Treatment.TreatmentID)TABLE: Individual (each treatment will be applied to n different individuals) - IndID (INTEGER - AUTO_INCREMENT - PRIMARY KEY) - Active (BOOLEAN if 0 this should be ignored and not counted) - TreatmentID (INTEGER - FOREIGN KEY REFERENCES Treatment.TreatmentID)TABLE: User (Table with user data) - UserID (INTEGER - AUTO_INCREMENT - PRIMARY KEY) - Username (VARCHAR) - Some other irrelevant fields因此,将保留的计算机插槽放在Schedule上,将要执行的动作放在Treatment上,要在其上执行这些动作的个人(对许多相同个人进行每次处理以进行统计)都放在Individual和将广告位链接到该广告位期间应进行的不同处理(并且可以使用ScheduleTreatment前键获取要执行此操作的个人。TreatmentID提供了指向用户(而非所有者)的链接广告位)来忽略该过程。我想执行广告位预览,以便用户可以查看所有信息(因此查询应以OperatorID结尾),这意味着获取WHERE Schedule.UserID = ?,操作员的用户名和两个计数:几种处理方式之一(使用Schedule.*和ScheduleTreatment比较简单),以及多少个人(在所有指定的治疗方法中)的另一个,这里我绝对不知道如何。没有处理的插槽或有处理且没有个人的插槽也应出现(对应的计数为0)。在我看来,这需要根据两个不同的条件进行分组,但这看起来也可以在一个(比我更智能)查询中完成。我已经设法分开做,但不在同一查询中。这是获取GROUP BY以及分配给它的所有个人的示例:SELECT Treatment.TreatmentID, COUNT(Individual.TreatmentID) AS IndividualsFROM Treatment INNER JOIN Individual ON Individual.TreatmentID =Treatment.TreatmentID GROUP BY Individual.TreatmentID ORDER BY TreatmentID提前致谢, 最佳答案 我想执行广告位预览,以便用户可以查看所有信息(因此查询应以WHERE Schedule.UserID =?结尾),这意味着获取Schedule。*select Schedule.* from Schedule WHERE Schedule.UserID = ?,即操作员的用户名,select Schedule.*, op.username as OperatorNamefrom Schedule sinner join User op on s.OperatorID = op.idwhere Schedule.UserID = ?和两个计数:多少个处理之一(使用ScheduleTreatment和GROUP BY比较简单)SELECT SlotID, COUNT(*) scountFROM ScheduleTreatmentGROUP BY SlotID还有另一个人(在所有指定的治疗方法中),在这里我绝对不知道如何。SELECT SlotID, COUNT(*) icountFROM ScheduleTreatment stINNER JOIN Individual i on st.TreatmentID = i.TreatmentIDWHERE i.actve <> 0GROUP BY SlotID没有处理的插槽或有处理且没有个人的插槽也应出现(相应计数为0)。SELECT Schedule.*, op.username as OperatorName , st.scount, i.icountFROM Schedule sINNER JOIN User op on s.OperatorID = op.idLEFT JOIN ( SELECT SlotID, COUNT(*) scount FROM ScheduleTreatment GROUP BY SlotID ) st on s.slotid = st.slotidLEFT JOIN ( SELECT SlotID, COUNT(*) icount FROM ScheduleTreatment st INNER JOIN Individual i on st.TreatmentID = i.TreatmentID WHERE i.actve <> 0 GROUP BY SlotID ) i on s.slotid = i.slotidWHERE Schedule.UserID = ?关于mysql - MySQL:数不胜数和一对多(以及其他内容),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47503599/
10-12 12:41
查看更多