对 2 个表的 mysql 查询与计数

Question

我有 2 个表。一个叫做 artist。这是表结构:

artistID lastname firstname nationality dateofbirth datedcease

另一个表叫做work

workId title copy medium description artist ID

列出在数据库中记录了多个副本的任何艺术作品(包括创作该作品的艺术家)的详细信息的 SQL 查询是什么？

Answer 1

试试这个:

SELECT 
  w.copy, w.title, w.description, w.medium, 
  a.firstname + ' ' + a.lastname AS 'Artist created the work'
FROM artists a
INNER JOIN
(
    SELECT * 
    FROM work 
    WHERE artistID IN
    ( 
        SELECT artistID
        FROM work 
        GROUP BY artistID
        HAVING COUNT(*) > 1 
    )
) w ON a.artistID = w.artistID

这是一个demo in SQL Fiddle