希望创建一个只显示一列 MYSQL 的子查询 [英] Looking to create a subquery that displays only one column MYSQL

问题描述

该数据库用于图书借阅系统.如果学生拿到一本书的副本，系统会接受那里的学生编号，并为他们提供该书的归还日期.这是我正在使用的两个表

This database is for a book loan system. Where if a student takes a copy of a book the system accepts there student number and gives them a return date for the book. These are the two tables I am using

贷款表:

 CREATE TABLE loan (
    `code` INT NOT NULL,
    `no` INT NOT NULL,
    taken DATE NOT NULL,
    due DATE NOT NULL,
    `return` DATE NULL,
    CONSTRAINT pri_loan PRIMARY KEY (taken),
    CONSTRAINT for_loan 
        FOREIGN KEY (`code`) REFERENCES copy (`code`),
        FOREIGN KEY (`no`) REFERENCES student (`no`));

学生表:

CREATE TABLE student (
`no` INT NOT NULL,
`name` VARCHAR(30) NOT NULL,
school CHAR(3) NOT NULL,
embargo BIT NOT NULL,
CONSTRAINT pri_student PRIMARY KEY (`no`));

首先，我想计算一本书的最晚到期日(它会显示该书的代码以及该书的最晚到期日是什么时候)这是我通过使用这段代码完成的，效果很好

Firstly I am looking to calculate the latest due date for a book copy (it will bring up the code for the book and when the latest due date for that book is) which I have done by using this code which worked perfectly

SELECT `code`, max(due)
FROM loan
    GROUP BY `code`

现在我想修改这个查询，以便它获取每本书的最新截止日期，但只会显示拥有这本书的学号 (no).为此，我需要使用一个我知之甚少的子查询，因为我才刚刚开始使用 MySQL.基本上我不确定如何创建这个子查询，我想知道是否有人可以帮助我并解释它而不仅仅是向我展示代码.

Now I want to modify this query so that it fetches the latest due dates for each book but will only display the student number (no) that has the book. To do this I need to use a sub query which I know very little about as I am only starting to use MySQL. Basically im unsure how to create this sub query and I am wondering if someone can help me out and also explain it not just show me the code.

推荐答案

您正在寻找的是 HAVING 语法:

What you're looking for is the HAVING syntax:

SELECT
    `no` as student
FROM
    `loan`
GROUP BY
    `code`
HAVING
    DATETIME(`due`) = MAX(DATETIME(`due`));

HAVING 允许您向 SELECT 语句添加条件，该语句通过可用值(包括选定的列及其最终值，如果你在做计算).

HAVING lets you add a condition to a SELECT statement that references and filters by the available values (including selected columns and their final values, which can be really handy if you're doing calculations).

请参阅此处了解更多信息:https://www.guru99.com/group-by.html

See here for more info: https://www.guru99.com/group-by.html

这篇关于希望创建一个只显示一列 MYSQL 的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！