求一条SQL的写法

来源：百度知道 编辑：UC知道 时间：2024/09/22 01:33:52
一表test，A,B,C,D四个字段
A B C D
1 1 Y 1
1 1 Y 2
1 1 N 10
1 1 Y 3
2 1 Y 4
2 1 Y 5
2 3 N 9
2 3 Y 8

其中C字段只有Y和N两值，按A,B分组分别求得D值最大和最小时的C字段的值
SELECT t.c as min
FROM test t
WHERE t.e = (SELECT MIN(d) FROM test GROUP BY a,b HAVING a=t.a AND b=t.b )

SELECT t.c as max
FROM test t
WHERE t.e = (SELECT MAX(d) FROM test GROUP BY a,b HAVING a=t.a AND b=t.b )
如何用SQL来判断min和max中的内容是否相等？比如让Y为1，N为-1，相等的话让结果为0，2个Y则为2，2个N为-2
请不要建立临时表来存段啊-。-！ 最好能显示结果为
A B C
1 1 0
2 1 2
2 3 0

botool提供的方法有问题哦，执行效果为：
A B C
1 1 2
2 1 2
2 3 0
-------------------------------------
267025676 这位朋友的SQL我在PL/SQL里执行的效果为
Error ORA-0098: missing SELECT keyword ,我改了下，可以执行，感谢了

WITH t1 as (
SELECT t.a,t.b,t.c
FROM test t
WHERE t.d = (SELECT MIN(d) FROM test GROUP BY a,b HAVING a=t.a AND b=t.b )
),
t2 as (
SELECT t.a,t.b,t.c

create TABLE temptb(A INT, B INT, C VARCHAR(1), D INT)
INSERT temptb
SELECT 1, 1, 'Y', 1 UNION ALL
SELECT 1, 1, 'Y', 2 UNION ALL
SELECT 1, 1, 'N', 10 UNION ALL
SELECT 1, 1, 'Y', 3 UNION ALL
SELECT 2, 1, 'Y', 4 UNION ALL
SELECT 2, 1, 'Y', 5 UNION ALL
SELECT 2, 3, 'N', 9 UNION ALL
SELECT 2, 3, 'Y', 8

select a.A,a.b,C=case when a.C=b.C and a.C='Y' then 2 when a.C=b.C and a.C='N' then -2 else 0 end from
(select *,row_number()over( order by A ) 次号 from temptb a
where not exists (select 1 from temptb where a.A=A and a.B=b and D>a.D)) a,
(select * ,row_number()over (order by A) 次号 from temptb a
where not exists (select 1 from temptb where a.A=A and a.B=b and D<a.D)) b
where a.A=b.a AND A.B=B.B AND A.次号=B.次号

这回没错了吧，执行就是你想要的
-----------
希望对你有帮助

with