涉及 group by 和 join 的 SQL 查询 [英] SQL query involving group by and joins

问题描述

我在标题部分不能更具体，但我想为我做一些有点复杂的事情.我以为我做到了，但事实证明它有问题.

I couldn't be more specific in the title part but I want to do something a little bit complex for me. I thought I did it but it turned out that it is buggy.

我有以下三个表:

项目表

• idProject
• 标题
• idOwner

报价表

• idOffer
• idProject
• idAccount

账户表

• idAccount
• 用户名

现在在一个查询中，我的目标是列出提供最多报价的所有项目，并且在查询中我还想获得所有者的用户名、报价人的用户名*等详细信息.所以我不必为每个项目再次查询.

Now in one query I aim to list all the projects with most offers made, and in the query I also want to get details like the username of the owner, username of the offerer* etc. So I don't have to query again for each project.

这是我的错误查询，这是我对 GROUP BY 的第一次实验，我可能不太明白.

Here is my broken query, it's my first experiment with GROUP BY and I probably didn't quite get it.

SELECT Project.addDate,Project.idOwner ,Account.Username,Project.idProject,
    Project.Price,COUNT(Project.idProject) as offercount 
FROM Project 
INNER JOIN Offer 
    ON Project.idProject= Offer.idProject 
INNER JOIN Account 
ON Account.idAccount = Project.idOwner  
GROUP BY Project.addDate,Project.idOwner,
    Account.Username,Project.idProject,Project.Price 
ORDER BY addDate DESC

*:我写的时候没想到我只是想提供一些额外的信息，感谢 Hosam Aly，这是毫无意义的.

*:I wrote that without thinking I was just trying to come up with example extra information, that is meaningless thanks to Hosam Aly.

推荐答案

试试这个(针对没有offer的项目修改):

Try this (modified for projects with no offers):

SELECT
  Project.addDate,
  Project.idOwner,
  Account.Username,
  Project.idProject,
  Project.Price,
  ISNULL(q.offercount, 0) AS offercount
FROM
  (
    SELECT
      o.idProject,
      COUNT(o.idProject) as offercount
    FROM Offer o
    GROUP BY o.idProject
  ) AS q
  RIGHT JOIN Project ON Project.idProject = q.idProject
  INNER JOIN Account ON Account.idAccount = Project.idOwner
ORDER BY addDate DESC

这篇关于涉及 group by 和 join 的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！