Oracle聚合查询

发布于 2024-11-23 15:33:42 字数 538 浏览 2 评论 0原文

ColA ColB ColC Cold
数据1 比率1 T 测试1
数据1 比率3 F 测试2
数据1 比率2 T 测试1
数据2 比率1 T 测试1
数据2 比率3 T 测试1
data3 rata4 T 测试1

有四列 Varchar 类型。我想确定的是：-
1. 对于 ColB 的每个唯一值（rata1、rata2 等），找到 ColA 和其他两列（ColC、ColD）的对应值，其中 ColA 的特定值的 ColC 值等于“T”0 次或 1 次。

rata2 data1 T Test1
rata3 data2 T Test1 
rata4 data3 T Test1

2. 在上面的查询中，当 ColC 等于“T”0 次或 1 次时，查找 ColA 的 ColC 等于 ColB 的特定值的其他行。

rata3 data1 F Test2

原文

ColA ColB ColC ColD
data1 rata1 T Test1
data1 rata3 F Test2
data1 rata2 T Test1
data2 rata1 T Test1
data2 rata3 T Test1
data3 rata4 T Test1

Have four columns of type Varchar. What i wish to determine is:-
1. For each unique value of ColB(rata1, rata2 etc), find corresponding value of ColA and other two columns (ColC, ColD) where that specific value of ColA has ColC value equal to 'T' 0 or 1 times.

rata2 data1 T Test1
rata3 data2 T Test1 
rata4 data3 T Test1

2. In the above query when ColC was equal to 'T' 0 or 1 times, find other rows for which ColA has ColC equal to 'F' for that specific value of ColB.

rata3 data1 F Test2

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜尕妞 2024-11-30 15:33:42

根本没明白你的问题。如果你说别的的话我会纠正答案。但据我了解你想要这样的东西吗？

编辑：我在数据库中制作了与您提供的相同的表。这效果很好。尝试一下并告诉我它是否有效

SELECT COLUMNB,MAX(COLUMNA), MAX(ColumnC), MAX(COLUMND)
FROM DUMMY
WHERE ColumnC = 'T'
GROUP BY COLUMNB
HAVING SUM(CASE WHEN COLumnC = 'T' THEN 1 ELSE 0 END) <= 1

UNION ALL
SELECT * 
FROM  DUMMY
WHERE COLumnC = 'F'

编辑2：这个怎么样？

WITH B
AS(
SELECT A.COLUMNB, MAX(A.COLUMNA) AS COLUMNA, MAX(A.ColumnC) AS COLUMNC, MAX(A.COLUMND) AS COLUMND
FROM DUMMY A
GROUP BY A.COLUMNB
HAVING SUM(CASE WHEN A.COLumnC = 'T' THEN 1 ELSE 0 END) <= 1)


SELECT * 
FROM B 

UNION ALL
SELECT D.COLUMNB, D.ColumnA, D.ColumnC, D.ColumnD
FROM B, DUMMY D
WHERE  B.ColumnB = D.COLUMNB
AND D.ColumnC = 'F'

Didn't understand your question at all. I'll correct the answer if you say something else. but as far I understood you want something like this?

Edit: I made the same table that you gave us in a DB. And this worked fine. Try it and tell me if it works

SELECT COLUMNB,MAX(COLUMNA), MAX(ColumnC), MAX(COLUMND)
FROM DUMMY
WHERE ColumnC = 'T'
GROUP BY COLUMNB
HAVING SUM(CASE WHEN COLumnC = 'T' THEN 1 ELSE 0 END) <= 1

UNION ALL
SELECT * 
FROM  DUMMY
WHERE COLumnC = 'F'

Edit 2: What about this?

WITH B
AS(
SELECT A.COLUMNB, MAX(A.COLUMNA) AS COLUMNA, MAX(A.ColumnC) AS COLUMNC, MAX(A.COLUMND) AS COLUMND
FROM DUMMY A
GROUP BY A.COLUMNB
HAVING SUM(CASE WHEN A.COLumnC = 'T' THEN 1 ELSE 0 END) <= 1)


SELECT * 
FROM B 

UNION ALL
SELECT D.COLUMNB, D.ColumnA, D.ColumnC, D.ColumnD
FROM B, DUMMY D
WHERE  B.ColumnB = D.COLUMNB
AND D.ColumnC = 'F'

回复收藏 0 原文

~没有更多了~