官术网_书友最值得收藏!

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 160字
  • 2021-06-24 14:38:10

HAVING

HAVING further filters the result based on values in the results, rather than the actual data. A HAVING clause only applies to columns that are included in the GROUP BY clause or in an aggregate function. Building on the same example used in the WHERE, ORDER BY, and GROUP BY sections, here we want to additionally know which locations carry an inventory of over 100 items per product. For that, after the GROUP BY clause, the query has a HAVING clause over the aggregate function, where its result is greater than 100:

SELECT P.Name AS ProductName, SUM([PI].Quantity) AS Total_Quantity, L.Name AS LocationName
FROM Production.Product AS P
INNER JOIN Production.ProductInventory AS [PI] ON P.ProductID = [PI].ProductID
INNER JOIN Production.Location AS L ON [PI].LocationID = L.LocationID
WHERE P.Name LIKE 'Touring%'
GROUP BY P.Name, L.Name
HAVING SUM([PI].Quantity) > 100
ORDER BY P.Name DESC, L.Name DESC;

The following screenshot shows the results as containing only rows with an aggregate Total_Quantity greater than 100:

主站蜘蛛池模板: 张家界市| 洱源县| 大同县| 西青区| 万全县| 鲁山县| 德钦县| 密山市| 内黄县| 阳新县| 五华县| 广东省| 凤凰县| 通山县| 上林县| 慈利县| 灵山县| 宜昌市| 老河口市| 江津市| 竹溪县| 额济纳旗| 丹阳市| 和静县| 冀州市| 凤庆县| 花垣县| 乐都县| 曲阳县| 吐鲁番市| 庐江县| 高台县| 福鼎市| 新兴县| 夏河县| 林口县| 互助| 兴安县| 丽江市| 乌海市| 安仁县|