我不知道如何比較ist 和 osv列中的計數元素。
select
r.parent_id
, count(case when c.config_field_id=100 then c.key_value end) as ist
, count(case when c.config_field_id=101 then c.key_value end) as osv
from relation as r
left join config_value_number as c
on r.child_id = c.key_value
where c.config_field_id in(100, 101)
and r.relation_type_id in(200, 201)
group by parent_id
結果：
| Parent_id | 伊斯特 | 作業系統 |
|---|---|---|
| 500 | 3 | 3 |
| 501 | 2 | 2 |
| 502 | 5 | 1 |
所以在上面我必須比較ist列和osv列的值是否相同。請幫我解決這個問題。提前致謝。
uj5u.com熱心網友回復：
或者您可以對聚合結果使用標準謂詞HAVING：
select
r.parent_id
, count(case when c.config_field_id=100 then c.key_value end) as ist
, count(case when c.config_field_id=101 then c.key_value end) as osv
from relation as r
left join config_value_number as c
on r.child_id = c.key_value
where c.config_field_id in(100, 101)
and r.relation_type_id in(200, 201)
group by parent_id
HAVING count(case when c.config_field_id=100 then c.key_value end)
= count(case when c.config_field_id=101 then c.key_value end)
;
您甚至不必在 SELECT 串列中包含 CASE 運算式 - 您只需在HAVING子句中評估它們即可。
uj5u.com熱心網友回復：
用這個
select * from (
select r.parent_id,
count(case when c.config_field_id=100 then c.key_value end) as ist,
count(case when c.config_field_id=101 then c.key_value end) as osv
from relation as r
left join config_value_number as c on r.child_id = c.key_value
where c.config_field_id in(100, 101)
and r.relation_type_id in(200, 201)
group by parent_id
)a
where ist=osv
uj5u.com熱心網友回復：
簡單的解決方案是將當前查詢視為主查詢的表運算式。然后，很容易進行比較。
例如：
select x.*,
case when ist = osv then 'same' else 'diff' end as are_equal
from (
-- your existing query here
) x
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432934.html
上一篇：Oracle中所有列的全文搜索