сохранение результата выборки в mysql

150

13 ноября 2019, 21:20

создал следующий запрос

SELECT theme, second_name, first_name 
FROM (SELECT DISTINCT reader_key, theme_key 
      FROM Books_giving 
      NATURAL JOIN Books) AS it 
NATURAL JOIN Readers 
NATURAL JOIN Themes
WHERE (SELECT COUNT(*) 
       FROM (SELECT DISTINCT reader_key, theme_key 
             FROM Books_giving 
             NATURAL JOIN Books) AS nt 
       NATURAL JOIN Readers AS nReaders 
       WHERE it.theme_key = nt.theme_key 
         AND LEFT(Readers.second_name, 1) = LEFT(nReaders.second_name, 1) 
         AND LEFT(Readers.first_name, 1) = LEFT(nReaders.first_name, 1) 
         AND nt.reader_key <> it.reader_key) >= 1

проблема в дублировании одного и того же запроса в двух местах: в таком случае:

SELECT theme, second_name, first_name 
FROM (SELECT DISTINCT reader_key, theme_key 
      FROM Books_giving 
      NATURAL JOIN Books) AS it 
NATURAL JOIN Readers 
NATURAL JOIN Themes
WHERE (SELECT COUNT(*) 
       FROM it AS nReaders 
       WHERE it.theme_key = nt.theme_key 
         AND LEFT(Readers.second_name, 1) = LEFT(it.second_name, 1) 
         AND LEFT(Readers.first_name, 1) = LEFT(it.first_name, 1) 
         AND nt.reader_key <> it.reader_key) >= 1

говорит о несуществовании таблицы под именем it

Table 'lab.it' doesn't exist

можно ли вынести результат запроса в переменную или вроде того? версия mysql - 5.7.25-0ubuntu0.18.04.2

таблицы:

Answer 1

Думаю запрос можно упростить примерно так:

SELECT DISTINCT theme, second_name, first_name
  FROM Books_giving 
NATURAL JOIN Readers 
NATURAL JOIN Themes
 WHERE (theme_key, LEFT(second_name, 1), LEFT(first_name, 1))
    IN (
         SELECT theme_key, LEFT(second_name, 1), LEFT(first_name, 1)
           FROM Books_giving 
        NATURAL JOIN Readers
        NATURAL JOIN Books
          GROUP BY 1, 2, 3
         HAVING count(distinct reader_key)>1
       )