Problem: query to borrow the title of the mechanical industry publishing house number is contained in "according to library book" three word book but no borrowing number is contained in the title of science publishing house, 'according to library books' three words book reader's name, title,
(personal understanding: the reader must borrow from the mechanical industry publishing house of books containing database, but the reader may borrow the database is not included in the title of scientific publishing false books, or may not have to borrow a book published by science press books, but if the readers borrow the title of science publishing house of books in the database is contained in three words, does not conform to the requirements) to write code in which of the following is my up sqlplus:


(there are three tables: book, the reader, borrow, borrow the book table and reader table father watch is)
(my understanding is first write want to query the columns: name of readers and book title, then connect the three tables, elected according to the condition to borrow the title of the mechanical industry publishing house of books in the database is contained in three words, and then grouped by borrowing to name them, selected from groups meet the conditions of the people, but why not? Then there is the back of the group by must follow by a select statement with the column name?????? )

CodePudding user response:

group by behind must be followed a select statement with the column name??????

Behind the select of aggregate column, must be in the group by the back,

CodePudding user response:

reference 1st floor wmxcn2000 response:

group by behind must be followed a select statement with the column name??????

Select the non-cohesive behind the columns, must be in the group by behind

Oracle, Mysql doesn't have to,

CodePudding user response:

On the back of the having clause can be not? If that must be placed behind the group by clause is not in conformity with the question,,,
There is again after having operated for the group? For example a man borrows the two books, one is mechanical industry publishing house database contains three words of the book, another book is published by science press also contains three words of the book database (the person should have two lines of data in the query results, but now the reader group, is on behalf of the two lines is a set of data, if the person meet the question are filtered, if you don't meet the question the two lines will not be filtered??? I'm confused here

CodePudding user response:

Having the back, to write aggregation function logic operation,

CodePudding user response:

refer to the second floor u012557814 response:

Quote: refer to 1st floor wmxcn2000 response:

group by behind must be followed a select statement with the column name??????

Select the non-cohesive behind the columns, must be in the group by behind

Oracle, Mysql doesn't have to,

Well, mysql support the grammar, if use mysql after a long time, suddenly into oracle or MSSQL, there will be such a question: "my statement is fine, how is an error,"

CodePudding user response:

Mark, do not find what is wrong

CodePudding user response:

This way the query result is only borrowed machinery industry? Not all science press does not include the database filtered out

CodePudding user response:

Recommends that the condition of having the back in the where clause, because having clause is the screening of grouping conditions, in front of the group by the where is the filter of the query results, simply speaking, is having the back of the restrict conditions reader. The name, book. The book title, not to limit the other fields, so the host's statement is an error occurred,

CodePudding user response:

try this?

 
SELECT name, t. t. title 
The FROM (the SELECT READER. The name, BOOK title, the BOOK. Press 
FROM the BOOK, the READER, BORROW 
WHERE READER. The READER number=BORROW. Readers, 
AND the BOOK. The BOOK number=BORROW. Books, 
AND the BOOK. The title LIKE '% database %' 
AND the BOOK. Press & lt;> 'science press) T 
WHERE t. press='mechanical industry publishing house' 
GROUP BY name, t. t. title 

CodePudding user response:

references 9 f gaojiagang response:

try this?

 
SELECT name, t. t. title 
The FROM (the SELECT READER. The name, BOOK title, the BOOK. Press 
FROM the BOOK, the READER, BORROW 
WHERE READER. The READER number=BORROW. Readers, 
AND the BOOK. The BOOK number=BORROW. Books, 
AND the BOOK. The title LIKE '% database %' 
AND the BOOK. Press & lt;> 'science press) T 
WHERE t. press='mechanical industry publishing house' 
GROUP BY name, t. t. title 

The above seems to be wrong, it can be

 
The CREATE VIEW v_jyts AS (SELECT READER. The name, BOOK. The BOOK title, the BOOK. Press 
FROM the BOOK, the READER, BORROW 
WHERE READER. The READER number=BORROW. Readers, 
AND the BOOK. The BOOK number=BORROW. Books, 
AND the BOOK. The title LIKE '% database %'); 
SELECT name, the title FROM v_jyts WHERE names IN (SELECT names FROM v_jyts WHERE press='mechanical industry publishing house) 
AND name NOT IN (SELECT names FROM v_jyts WHERE press='science press) 
GROUP BY name, the title