问题定义
我有一个SQL查询,如下所示:
SELECT *
FROM table
WHERE criteria = 1
ORDER BY group;结果
我得到了:
group | value | criteria
------------------------
A | 0 | 1
A | 1 | 1
B | 2 | 1
B | 3 | 1预期结果
但是,我想将结果限制为第一组(在本例中为A)。也就是说,
group | value | criteria
------------------------
A | 0 | 1
A | 1 | 1我尝试过的
分组依据
SELECT *
FROM table
WHERE criteria = 1
GROUP BY group;我可以使用GROUP BY子句聚合这些组,但这将使我:
group | value
-------------
A | 0
B | 2或每组的某个聚合函数。但是,我不想聚合这些行!
子查询
我还可以指定group by子查询:
SELECT *
FROM table
WHERE criteria = 1 AND
group = (
SELECT group
FROM table
WHERE criteria = 1
ORDER BY group ASC
LIMIT 1
);这是可行的,但是像往常一样,子查询是混乱的。特别是,这需要为criteria指定两次WHERE子句。当然,肯定有一种更干净的方法来做到这一点。
发布于 2015-08-21 23:47:38
您可以尝试以下查询:
SELECT *
FROM table
WHERE criteria = 1
AND group = (SELECT MIN(group) FROM table)
ORDER BY value;发布于 2015-08-22 04:02:08
如果您的数据库支持WITH子句,请尝试这样做。它类似于使用子查询,但是您只需要指定一次criteria输入。这也更容易理解正在发生的事情。
with main_query as (
select *
from table
where criteria = 1
order by group, value
),
with min_group as (
select min(group) from main_query
)
select *
from main_query
where group in (select group from min_group);
-- this where clause should be fast since there will only be 1 record in min_group发布于 2017-12-22 21:16:05
使用DENSE_RANK()
DECLARE @yourTbl AS TABLE (
[group] NVARCHAR(50),
value INT,
criteria INT
)
INSERT INTO @yourTbl VALUES ( 'A', 0, 1 )
INSERT INTO @yourTbl VALUES ( 'A', 1, 1 )
INSERT INTO @yourTbl VALUES ( 'B', 2, 1 )
INSERT INTO @yourTbl VALUES ( 'B', 3, 1 )
;WITH cte AS
(
SELECT i.* ,
DENSE_RANK() OVER (ORDER BY i.[group]) AS gn
FROM @yourTbl AS i
WHERE i.criteria = 1
)
SELECT *
FROM cte
WHERE gn = 1
group | value | criteria
------------------------
A | 0 | 1
A | 1 | 1 https://stackoverflow.com/questions/32144425
复制相似问题