两表联查问题，本人新手求指导，在线等！！！！

[ysboy]

product 产品表

字段 productID UserID productTime productType

Storeup 产品收藏统计表

字段 StoreupID UserID productID

现在就是说，UserID是客户名 点击收藏 product 这时 Storeup 里面 存的是 客户ID 产品ID

我现在要提取两个被收藏最多的产品 怎么做，求指导在线等呢~

[duoxu1983]

不太明白你product表里的userid有什么用 select top 2 productID，count(1) from Storeup group by productID order by 2 asc

[ysboy]

select * from (select top(2) ProductID, count(ProductID) as CF from Storeup group by ProductID order by ProductID desc )aa inner join Product on aa.ProductID=Product.ProductID 我这么差只能查出一条数据，我要两天啊。。。

[习惯性蹭分]

select pd.* from product as pd join(
select top 2 productid,count(1) [count] from storeup group by  productid
order by [count] desc
)as sel on pd.productid=sel.productid

[Kilin_Zhang]

;WITH Storeup(ProductID)
AS
(
	SELECT 'P-001' UNION ALL
	SELECT 'P-002' UNION ALL
	SELECT 'P-003' UNION ALL
	SELECT 'P-001' UNION ALL
	SELECT 'P-002' UNION ALL
	SELECT 'P-004' UNION ALL
	SELECT 'P-001' 
)
,Product(ProductID,ProductType)
AS 
(
	SELECT 'P-001','A' UNION ALL
	SELECT 'P-002','A' UNION ALL
	SELECT 'P-003','B' UNION ALL
	SELECT 'P-004','C' UNION ALL
	SELECT 'P-005','A'
)

SELECT p.* FROM Product p 
INNER JOIN (
	SELECT TOP(2) s.ProductID,COUNT(1) AS [Count]
	FROM Storeup s 
	GROUP BY s.ProductID
	ORDER BY [Count] DESC
) s 
	ON p.ProductID = s.ProductID

[Scorip]

SELECT * FROM product WHERE productID IN ( select TOP 2 a.productID from (select productID,COUNT(*)AS 'proSum' from Storeup group by productID)a ORDER BY a.proSum DESC)

[骑着蜗牛去爬山]

select top 2 productid from (select productid,ct=count(1) from product group by productid)t order by ct desc