sql-server - 如何在组(SQL)中找到具有最小值和最大值的记录?
问题描述
我对 SQL 语言很陌生。出于说明目的,我有一个包含两列的简单表:Item、Param1。
物品 | 参数1 |
---|---|
一种 | 0 |
乙 | 10 |
一种 | 50 |
一种 | 150 |
乙 | 50 |
C | 0 |
C | 200 |
乙 | 150 |
我需要创建一个唯一项的列表,其中 Param1 大于 100,同时最小值为 0。所以这种情况下的结果应该是:
物品 |
---|
一种 |
C |
选择唯一的项目列表看起来像这样,这是我需要的一半。
SELECT DISTINCT Item
FROM Database
WHERE Param1 > 100
我将不胜感激创建相应查询的帮助。最好的祝福
解决方案
with cte(Item,Param1) as
(
SELECT 'A', 0
UNION ALL
SELECT 'B', 10
UNION ALL
SELECT 'A', 50
UNION ALL
SELECT 'A', 150
UNION ALL
SELECT 'B', 50
UNION ALL
SELECT 'C', 0
UNION ALL
SELECT 'C', 200
UNION ALL
SELECT 'B', 150
)
SELECT C.Item
FROM CTE AS C
GROUP BY C.Item
HAVING MIN(C.Param1)=0 AND MAX(C.Param1)>100
推荐阅读
- javascript - js share array between thread
- algorithm - Minimizing the maximum cost of job assignment problem
- c - 将科学计数法中的数字转换为十进制
- pine-script - Pine 脚本:使用 plotshapes 时停用数字显示
- python - How to calculate proportion display pie chart in pandas or others?
- dictionary - Dart won't compile because of unmatching types, 'List
' can't be assigned to the parameter type 'List Function()' - typo3 - TYPO3 10.4: Menu of subpages with image
- callback - Gstreamer splitmuxsink callback when a new file is created
- python - SQL Query, Getting Rid of Repeat Selects
- elasticsearch - Named rescore queries