如何从多个表检索数据到一个表

I am having 3 tables Book Master, Category Master, Subcategory Master. In my Book Master table, column CategoryName have value from Category Master's ID column. And SubcategoryName column in Book Master have ID columns value from Subcategory Master.

我有三个表格，图书管理员，类别管理员，子类别管理员。在我的书主表中，列CategoryName有来自类别主ID列的值。而Book Master中的SubcategoryName列有来自子类别Master的ID列值。

Table structure of the 3 tables:

3个表的表结构:

Book Master

书的主人

Oid             uniqueidentifier,
BookName        nvarchar(100),
CategoryName    uniqueidentifier,
SubCategoryName uniqueidentifier

Category Master

类别的主人

Oid             uniqueidentifier,
CategoryName    nvarchar(100)

Subcategory Master

子类的主人

Oid             uniqueidentifier,
CategoryName    uniqueidentifier,
SubCategoryName nvarchar(100)

I am working on one windows application,in that I have to show top 5 books from each category of book.Now I am having 10 records in table and I have used the following query to display the books on form.

我正在开发一个windows应用程序，在这个应用程序中，我必须显示每种类别的前五本书。现在我在表中有10条记录，我使用以下查询来显示表单上的图书。

select BM.BookName ,BM.BookImage,CM.CategoryName,SCM.SubCategoryName
from BookMaster BM,CategoryMaster CM,SubCategoryMaster SCM 
where BM.CategoryName=CM.Oid and BM.SubCategoryName=SCM.Oid 
order by CM.CategoryName

this query gives me all 10 records. But, this query is fine when records in table are less. But when records goes on increasing, It will be difficult to show all records. For this reason, I want to show Top 5 books from each category.

这个查询给出了所有10条记录。但是，当表中的记录较少时，此查询是有效的。但当记录继续增加时，将很难显示所有记录。出于这个原因，我想展示每个类别的前五本书。

I have tried it with group by clause

我已按条款试过了

select top 5 BM.BookName ,BM.BookImage,CM.CategoryName,SCM.SubCategoryName
from BookMaster BM,CategoryMaster CM,SubCategoryMaster SCM 
where BM.CategoryName=CM.Oid and BM.SubCategoryName=SCM.Oid
group by CM.CategoryName

but it gives me error saying,

但它给了我错误的答案，

Column 'BookMaster.BookName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

“BookMaster列。BookName'在select列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

Suggest me any solution for this. Thanks in advance

给我建议解决这个问题的办法。谢谢提前

3 个解决方案

#1

Write a function that takes CategoryName and returns top 5 books for that category as a table Then use T SQL Apply to print your desired result

编写一个函数，获取CategoryName并返回该类别的前5本书作为一个表格，然后使用T SQL Apply打印您想要的结果

More more details http://msdn.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx

更多更多的细节http://msdn.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx

#2

Try this:

试试这个:

WITH CategoryCTE AS (
    SELECT 
        BookName,
        ROW_NUMBER() OVER (
            PARTITION BY CategoryName
            ORDER BY CategoryName DESC
        ) AS CTE_Order
    FROM BookMaster
)
SELECT bm.BookName, cm.CategoryName, scm.SubCategoryName
FROM 
    CategoryCTE 
    INNER JOIN BookMaster bm ON CategoryCTE.BookName = bm.BookName
    INNER JOIN CategoryMaster cm ON bm.CategoryName = cm.Oid
    INNER JOIN SubCategoryMaster scm ON bm.SubCategoryName = scm.Oid
WHERE CategoryCTE.CTE_Order < 6
GROUP BY cm.CategoryName, scm.SubCategoryName, bm.BookName

#3

Please look over this link for solution

请查看这个链接寻找解决方案

http://sqlzoo.net/howto/source/z.dir/err979/sqlserver

While using group by clause in query you have to specify the selected list of columns in select query in group clause, Otherwise its showing error what you get

在查询中使用group by子句时，您必须在group子句中的select query中指定选定的列列表，否则会显示错误

#1