选择计数列？ [英] select count column?

问题描述

我正在使用sql server。我有3张桌子。第2个引用第1个和第3个引用第2个（两个关系1到多个）。我想要一个连接表，其中包含一个列，显示第二个表中每个记录（或行）出现在第三个表中的次数。第1列代表PK，第2列FK：

I'm working with sql server. I have 3 tables. The 2nd has a reference to the 1st and the 3rd a reference to the 2nd (both relationships 1 to many). I want to have a joining table that includes a column showing how many times each record (or row) in the 2nd table appears in 3rd table. The 1st column represent PK, 2nd column FK :

Category    Product    Orders
--------    -------    ------
1           1  3       1  4
2           2  1       2  4
3           3  1       3  3
            4  3       4  2
                       5  2
                       6  4
                       7  2

我的加入表可能如下所示：

My joining table might look something like this:

Product     Price      Category     Number of Orders
--------    -------    --------     ----------------

推荐答案

通过这种方式，

by this way,

select count(*),a.uniqueId from table1 as a inner join table2 as b on a.uniqueId=b.foreignKeyId Group by a.uniqueId

我认为您可能必须更具体，可能是通过提供示例模式，因为您的要求（至少对我来说）不清楚。



通常，连接表用于多对多关系，连接表的每一行只引用一次连接表 - 我是显然不明白这里的东西。



我怀疑你可能在寻找计算列 [ ^ ]

I think you may have to be more specific, perhaps by providing a sample schema, as your requirements are (to me at least) unclear.

Typically, a join table is used for many-to-many relations, and each row of the join table only references each of the joined tables once - I'm obviously not understanding something here.

I suspect that you may be looking for computed columns[^]

好的我想通了：

Ok I figured it out:

select productName,price,category,(select count(*) from Orders where ProductID=1) as NumofOrders
		from Products
		inner join Category on Product.CategoryID=Category.CategoryID
		inner join Orders on Orders.ProductID=Product.ProductID
		where Product.ProductID
		group by productName,price,category

这篇关于选择计数列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！