问题

以下是翻译好的部分：

问题是

> 任何艺术品的详细信息，以及创作该作品的艺术家的全名，这些作品在数据库中至少有两个副本记录。

我尝试使用这段代码，但没有显示任何内容：

SELECT w.WorkID, w.Title, w.Copy, w.Medium, w.Description,
       a.FirstName || ' ' || a.LastName AS ArtistFullName
FROM dtoohey.Work w,
     dtoohey.artist a
WHERE w.ArtistID = a.ArtistID
AND w.WorkID IN (
    	SELECT w.WorkID
    	FROM dtoohey.Work w
    	GROUP BY w.WorkID
    	HAVING COUNT(w.copy) > 2
        );

我非常困惑要添加或更改“having count”的内容。

数据显示了副本，如下图所示。

副本的数据显示

有人可以帮助我解决这个问题吗？

SELECT w.WorkID, w.Title, w.Copy, w.Medium, w.Description,
       a.FirstName || ' ' || a.LastName AS ArtistFullName
FROM dtoohey.Work w,
     dtoohey.artist a
WHERE w.ArtistID = a.ArtistID
AND w.WorkID IN (
    	SELECT w.WorkID
    	FROM dtoohey.Work w
    	GROUP BY w.WorkID
    	HAVING COUNT(w.copy) > 2
        );

答案1

得分: 3

你需要分析你的数据并确定副本中的相同部分。如果WorkID是主键，那么只会有一个具有该标识符的行；在这种情况下，您需要使用其他方法来检测副本，例如Title和ArtistID的组合。

此外，HAVING COUNT(w.copy) > 2 将检查是否有三个或更多副本。您想要使用 > 1 或 >= 2 来检查两个或更多副本。

SELECT w.WorkID,
       w.Title,
       w.Copy,
       w.Medium,
       w.Description,
       a.FirstName ||' '|| a.LastName AS ArtistFullName
FROM   dtoohey.Work w
       INNER JOIN dtoohey.artist a
       ON w.ArtistID=a.ArtistID
WHERE  EXISTS (
         SELECT 1
         FROM   dtoohey.Work x
         WHERE  x.artistid = w.artistid
         AND    x.title    = w.title
         GROUP BY x.artistid, x.title
         HAVING COUNT(*) > 1
       );

您还可以使用分析函数来计算重复项：

SELECT w.WorkID,
       w.Title,
       w.Copy,
       w.Medium,
       w.Description,
       a.FirstName ||' '|| a.LastName AS ArtistFullName
FROM   ( SELECT w.*,
                COUNT(*) OVER (PARTITION BY artistid, title) AS cnt
         FROM   dtoohey.Work w
       ) w
       INNER JOIN dtoohey.artist a
       ON w.ArtistID=a.ArtistID AND cnt > 1;

对于示例数据：

CREATE TABLE dtooney.work (workid, title, copy, medium, description, artistid) AS
SELECT 1, 'Finger Painting', '1/5', 'Acrylic on Paper', 'Finger Painting', 1 FROM DUAL UNION ALL
SELECT 2, 'Finger Painting', '4/5', 'Acrylic on Paper', 'Finger Painting', 1 FROM DUAL UNION ALL
SELECT 3, 'The Scream', '1/1', 'Wool', 'Knitted version of The Scream', 2 FROM DUAL UNION ALL
SELECT 4, 'Half a Cow', '1/2', 'Taxidermy', 'Front Half', 3 FROM DUAL UNION ALL
SELECT 5, 'Half a Cow', '2/2', 'Taxidermy', 'Left Half', 3 FROM DUAL;

CREATE

<details>
<summary>英文:</summary>

You need to analyse your data and work out what is going to be the same for the copies. If `WorkID` is a primary key then there will only ever be one row with that identifier; in that case, you need to use some other method of detecting copies such as the combination of `Title` and `ArtistID`.

Additionally, `HAVING COUNT(w.copy) &gt; 2` will check to see if there are three-or-more copies. You want either `&gt; 1` or `&gt;= 2` to check for two-or-more copies.

```lang-sql
SELECT w.WorkID,
       w.Title,
       w.Copy,
       w.Medium,
       w.Description,
       a.FirstName ||&#39; &#39;|| a.LastName AS ArtistFullName
FROM   dtoohey.Work w
       INNER JOIN dtoohey.artist a
       ON w.ArtistID=a.ArtistID
WHERE  EXISTS (
         SELECT 1
         FROM   dtoohey.Work x
         WHERE  x.artistid = w.artistid
         AND    x.title    = w.title
         GROUP BY x.artistid, x.title
         HAVING COUNT(*) &gt; 1
       );

You could also use analytic functions to count the duplicates:

SELECT w.WorkID,
       w.Title,
       w.Copy,
       w.Medium,
       w.Description,
       a.FirstName ||' '|| a.LastName AS ArtistFullName
FROM   ( SELECT w.*,
                COUNT(*) OVER (PARTITION BY artistid, title) AS cnt
         FROM   dtoohey.Work w
       ) w
       INNER JOIN dtoohey.artist a
       ON w.ArtistID=a.ArtistID AND cnt > 1;

Which, for the sample data:

CREATE TABLE dtooney.work (workid, title, copy, medium, description, artistid) AS
SELECT 1, 'Finger Painting', '1/5', 'Acrylic on Paper', 'Finger Painting', 1 FROM DUAL UNION ALL
SELECT 2, 'Finger Painting', '4/5', 'Acrylic on Paper', 'Finger Painting', 1 FROM DUAL UNION ALL
SELECT 3, 'The Scream', '1/1', 'Wool', 'Knitted version of The Scream', 2 FROM DUAL UNION ALL
SELECT 4, 'Half a Cow', '1/2', 'Taxidermy', 'Front Half', 3 FROM DUAL UNION ALL
SELECT 5, 'Half a Cow', '2/2', 'Taxidermy', 'Left Half', 3 FROM DUAL;

CREATE TABLE dtooney.artist (artistid, firstname, lastname) AS
SELECT 1, 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Baron' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Count' FROM DUAL;

Both outputs:

fiddle