select * from (select min(AA) as minAA from Table_1 group by BB )a, Table_1 b where a.minAA =b.AA and b.cc='Z'
错了,掉了字段 select * from (select min(AA) as minAA,BB from Table_1 group by BB )a, Table_1 b where a.minAA =b.AA and b.cc='Z'
因为你按BB分组了 所以根据BB分出来3组 每组一个最小值
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([aa] INT,[bb] VARCHAR(1),[cc] VARCHAR(1)) INSERT [tb] SELECT 1,'a','z' UNION ALL SELECT 2,'a','x' UNION ALL SELECT 3,'a','z' UNION ALL SELECT 4,'b','x' UNION ALL SELECT 5,'b','z' UNION ALL SELECT 6,'b','x' UNION ALL SELECT 7,'c','x' UNION ALL SELECT 8,'c','z' UNION ALL SELECT 9,'c','x' --------------开始查询--------------------------SELECT * FROM [tb] AS a WHERE NOT EXISTS (SELECT 1 FROM tb WHERE [bb]=a.[bb] AND [aa]<a.[aa] ) AND [cc]='z' ----------------结果---------------------------- /* aa bb cc ----------- ---- ---- 1 a z(1 行受影响) */
select * From
(
select min(AA) as minAA,BB from Table_1 group by BB
) a
where exists (select 1 From Table_1 where bb=a.bb and aa=a.minAA and cc='Z')
而现在查出的结果是““以BB分组后,CC为Z的最小值AA。”
(select min(AA) as minAA from Table_1 group by BB )a, Table_1 b
where a.minAA =b.AA and b.cc='Z'
select * from
(select min(AA) as minAA,BB from Table_1 group by BB )a, Table_1 b
where a.minAA =b.AA and b.cc='Z'
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([aa] INT,[bb] VARCHAR(1),[cc] VARCHAR(1))
INSERT [tb]
SELECT 1,'a','z' UNION ALL
SELECT 2,'a','x' UNION ALL
SELECT 3,'a','z' UNION ALL
SELECT 4,'b','x' UNION ALL
SELECT 5,'b','z' UNION ALL
SELECT 6,'b','x' UNION ALL
SELECT 7,'c','x' UNION ALL
SELECT 8,'c','z' UNION ALL
SELECT 9,'c','x'
--------------开始查询--------------------------SELECT * FROM [tb] AS a WHERE
NOT EXISTS
(SELECT 1 FROM tb WHERE [bb]=a.[bb] AND [aa]<a.[aa]
)
AND [cc]='z'
----------------结果----------------------------
/*
aa bb cc
----------- ---- ----
1 a z(1 行受影响)
*/