我在VS2005 ASP.NET2.0中,写如下查询语句:
SELECT [ProductID], [ProdName], [ProdCatorgory], [ProdDesn], [ProdCreateDate], [ProdSellInDate], [ProdQuantity], [ProdInPrice], [ProdOutPrice], [PictureID] FROM [product] WHERE [ProductID] IN(SELECT TOP 10 [ProductID] FROM [comment] GROUP BY COUNT([ProductID]) ORDER BY [ProductID] DESC)然后提示错误:
GROUP BY 子句分组依据列表的表达式中,不能使用聚合或子查询。
备注:
1(该语句我在SQL 2000中,我运行了,能得出正确结果)
2 我再做一个小型购物网站,该SQL语句想实现的功能是:通过查询表comment,得出评论数最多的前十条记录的ProductID,然后在product表中,查询商品的具体信息我提出的问题可能长了点,所以劳烦大家了,希望大家给我支招,谢谢!
SELECT [ProductID], [ProdName], [ProdCatorgory], [ProdDesn], [ProdCreateDate], [ProdSellInDate], [ProdQuantity], [ProdInPrice], [ProdOutPrice], [PictureID] FROM [product] WHERE [ProductID] IN(SELECT TOP 10 [ProductID] FROM [comment] GROUP BY COUNT([ProductID]) ORDER BY [ProductID] DESC)然后提示错误:
GROUP BY 子句分组依据列表的表达式中,不能使用聚合或子查询。
备注:
1(该语句我在SQL 2000中,我运行了,能得出正确结果)
2 我再做一个小型购物网站,该SQL语句想实现的功能是:通过查询表comment,得出评论数最多的前十条记录的ProductID,然后在product表中,查询商品的具体信息我提出的问题可能长了点,所以劳烦大家了,希望大家给我支招,谢谢!
[ProdName],
[ProdCatorgory],
[ProdDesn],
[ProdCreateDate],
[ProdSellInDate],
[ProdQuantity],
[ProdInPrice],
[ProdOutPrice],
[PictureID]
FROM [product]
WHERE [ProductID] IN(SELECT TOP 10 [ProductID]
FROM [comment]
GROUP BY [ProductID]/*COUNT([ProductID]) 这里不能这样*/
ORDER BY [ProductID] DESC)
我明白这块有问题
但是你这样改之后,就得不到,我要的结果:我想通过评论的次数,然后再判断,用Group分类
FROM(
SELECT [ProductID],
COUNT([COMMENTS])
FROM [comment]
GROUP BY [ProductID]/*COUNT([ProductID]) 这里不能这样*/
ORDER BY [ProductID] DESC)
)就是先把 COMMENT 表中的PRODUCTID按照评论多少先查出来形成一个表, 然后再在其中选择前10位.
select TOP 10 COUNT([ProductID]) over(partition by ycny ) PIDASC from [comment]
ORDER BY PIDASC DESC)看看行不行.
select TOP 10 COUNT([ProductID]) over(partition by [ProductID]) PIDASC from [comment]
ORDER BY PIDASC DESC)
上边写错了,在我这么好用.
SELECT [ProductID], [ProdName], [ProdCatorgory], [ProdDesn], [ProdCreateDate], [ProdSellInDate], [ProdQuantity], [ProdInPrice], [ProdOutPrice], [PictureID]
FROM [product]
WHERE [ProductID] IN(SELECT TOP 10 [ProductID] FROM [comment] GROUP BY [ProductID] ORDER BY Count([ProductID]) DESC)
现在15楼已成功的解决了我的问题!
不管怎么样,我还是要再一次表示感谢!