如何使用SQL查询返回产品子类别记录 [英] How can I return product subcategory record using SQL query

问题描述

返回制作平均3天或更长时间的所有产品子类别记录。



我尝试过：



select *

来自[Production]。[Product]

group by [ProductID]

有avg（[DaysToManufacture]）> 3

Return all product subcategory record that take an Average 3 days or longer to manufacture.

What I have tried:

select*
From [Production].[Product]
group by[ProductID]
having avg([DaysToManufacture])>3

推荐答案

解决方案1说的是你不能做SELECT *因为你只能选择那些列在你的GROUP BY语句和SUM（字段）或COUNT（字段）等。



所以，你可以使用派生表做这样的事情：

What Solution 1 is saying is that you cannot do SELECT * because you can only SELECT the columns that are in your GROUP BY statement and the ones that you SUM(field) OR COUNT(field) etc.

So, you can do something like this using a derived table:

SELECT p.*
FROM (
  -- this is your original sql here
  select productid  -- you can get product_id because you are grouping on it
  From [Production].[Product]
  group by[ProductID] 
  having avg([DaysToManufacture])>3
) x
INNER JOIN product p ON x.productid = p.productid -- on the outside, you now join back to the same table so you can get all the rest of the fields

这不起作用：你只能从中返回聚合函数和组子句列GROUP BY语句。

我不确定你的数据是什么，或者你想要返回什么 - 所以看看这个： SQL GROUP By和Column这个名字在选择列表中无效，因为...错误 [ ^ ]

希望它能解释你的目的。

That won't work: you can only return aggregate functions and group clause columns from a GROUP BY statement.
I'm not sure exactly what your data is, or what you are trying to return - so have a look at this: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
Hopefully it will explain what you are trying to do.

这篇关于如何使用SQL查询返回产品子类别记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！