在 SQL 中有一个查询我被严重卡住了，我已经尝试了所有可能的方法但无法得到解决方案。我有 4 个表，分别命名为:user、item、buys、rates。

CREATE TABLE User (
    id integer,
    name varchar(30),
    Primary Key (id)
)

INSERT INTO User(id, name)
VALUES
('1', 'Lorren'),
('2', 'Smith'),
('3', 'Stephen'),
('4', 'David'),
('5', 'Sophie'),
('6', 'Alex'),
('7', 'Henry'),
('8', 'Jasmine'),
('9', 'Anderson'),
('10', 'Bilal')

CREATE TABLE Item (
    id integer,
    description varchar(50),
    category varchar(30),
    price integer,
    Primary Key (id)
)

INSERT INTO Item(id, description, category, price)
VALUES
('50', 'Princess Diary', 'Book', '8'),
('51', 'Frozen', 'Book', '4'),
('52', 'Tangled', 'Book', '3'),
('53', 'Oak Table', 'Furniture', '370'),
('54', 'Doble Bed', 'Furniture', '450'),
('55', 'Metal Cupboard', 'Furniture', '700'),
('56', 'Levi 501', 'Clothes', '90'),
('57', 'Corduroy Coat', 'Clothes', '230'),
('58', 'Straight Trousers', 'Clothes', '45'),
('59', 'Black Sequin Top', 'Clothes', '85')


CREATE TABLE Buys (
    user integer,
    item integer,
    price integer,
    Primary Key (user, item),
    Foreign key (user) REFERENCES User(id),
    Foreign Key (item) REFERENCES Item(id)
)

INSERT INTO Buys
VALUES ('1', '52', '3'),
('1', '56', '90'),
('2','56','100'),
('2', '54', '450'),
('5', '53', '400'),
('5', '55', '700'),
('5', '59', '90'),
('6', '57', '230'),
('10', '58', '50'),
('8', '50', '8')


CREATE TABLE Rates (
    user integer,
    item integer,
    rating integer CHECK (0<=rating<=5),
    Primary Key (user, item),
    Foreign key (user) REFERENCES User(id),
    Foreign Key (item) REFERENCES Item(id)
)

INSERT INTO Rates
VALUES
('1', '52', '5'),
('1', '56', '3'),
('2', '54', '5'),
('2', '55', '4'),
('2', '56', '2'),
('5', '53', '5'),
('5', '55', '5'),
('8', '50', '1'),
('8', '55', '3'),
('9', '55', '4')

我必须针对每个用户找到他未购买的所有商品，但仅显示其中具有最高平均评级的那些/多个商品。因此，结果应该只显示他没有购买且平均评分最高的那些元素。评分为 1-5，每个项目可能有不同的评分，因此可以计算每个项目的平均评分，但我无法找出每个用户未购买的平均评分最高的项目。我在MYSQL中工作，我被困在这里6天了，甚至我的 friend 都试过没有人能解决它。有人可以帮忙吗？

考虑到当前表的预期输出应该是这样的:

User    Items With Highest Average      
Lorren     53
Lorren     54
Smith      52
Smith      53
Stephen    52
Stephen    53
Stephen    54
David      52
David      53
David      54
Sophie     52
Sophie     54
Alex       52
Alex       53
Alex       54
Henry      52
Henry      53
Henry      54
Jasmine    52
Jasmine    53
Jasmine    54
Anderson   52
Anderson   53
Anderson   54
Bilal      52
Bilal      53
Bilal      54

最佳答案

好吧，绝对不是我最漂亮的工作，特别是因为我通常不在 MySQL 中工作(编辑:SQLFiddle 已备份。修复了一个内部组，现在可以工作了):

SELECT topItemsAllUsers.* FROM
  (SELECT 
    u.id AS userId, 
    u.name, 
    topItems.itemId
  FROM
    (SELECT 
      iwa.id AS itemId
    FROM
      (SELECT 
        MAX(AverageRating) AS MaxRating
      FROM
        (SELECT 
          i.id, 
          AVG(COALESCE(r.rating, 0)) AS AverageRating
        FROM Item i
        LEFT JOIN Rates r ON r.item = i.id
        GROUP BY i.id
        ) AS averages
      ) AS MaxOuterRating
      INNER JOIN 
      (SELECT 
        i.id, 
        AVG(COALESCE(r.rating, 0)) AS AverageRating
      FROM Item i
      LEFT JOIN Rates r ON r.item = i.id
      GROUP BY i.id
      ) as iwa ON iwa.AverageRating = MaxOuterRating.MaxRating
    ) as topItems
  CROSS JOIN
  User u
  ) as topItemsAllUsers
LEFT JOIN Buys b ON topItemsAllUsers.userId = b.user AND topItemsAllUsers.itemId = b.item
WHERE b.user IS NULL

Here is the SQLFiddle

在 TSQL 中，我至少会为该平均评分表使用 CTE。这比最初看起来要困难得多!

编辑:下面是一些解释。首先要获得的是每个项目的平均评分，对没有评分的项目使用 0(因此 COALESCE() 语句):

(SELECT 
  i.id, 
  AVG(COALESCE(r.rating, 0)) AS AverageRating
FROM Item i
LEFT JOIN Rates r ON r.item = i.id
GROUP BY i.id)

这将列出每个项目 ID 一次及其平均评分。我将此命名为 averages，实际上我用它查询了两次(第二次将其命名为 iwa。我不记得“iwa”应该是什么意思了。 ..)，一旦获得实际最高评价:

SELECT
  MAX(AverageRating) AS MaxRating
FROM averages

并将其命名为 MaxOuterRating，然后在 AverageRating = MaxRating 上INNER JOIN将该结果返回到 iwa , 仅获取具有最高评分的项目:

SELECT
  iwa.itemId
FROM
MaxOuterRating
INNER JOIN iwa ON iwa.AverageRating = MaxOuterRating.MaxRating

此结果包含在 topItems 别名中。

现在我们只有评分最高的项目，CROSS JOIN 和 User 可以得到一个表，其中包含每个用户的每个最高项目:

SELECT 
  ... 
FROM
topItems
CROSS JOIN
Users

此结果在 topItemsAllUsers 中。

最后，对用户 ID 和商品 ID 执行 LEFT JOIN 和 Buys，然后将结果限制为只有那些没有 Buys 的行> 关联的记录(这通常称为排除连接):

SELECT 
  topItemsAllUsers.*
FROM
topItemsAllUsers
LEFT JOIN Buys b ON topItemsAllUsers.userId = b.user AND topItemsAllUsers.itemId = b.item
WHERE b.user IS NULL

中提琴。这些操作都不是特别困难，但它们嵌套得很厉害，很难看出如何攻击。我不怀疑这可以大大改进，但这确实返回了预期的结果。

关于mysql - 无法在 MySQL 中找出涉及 4 个表的 SQL 查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/28972355/