假設我想要作為查詢結果計算的 id，并檢查相同的 id 是否出現在不同的月份。
在這里，我通過不同的 id 連接 2 個表并計算回傳的行數以了解我有多少匹配的 id。這是六月。我想要：
例如。6 月 100 個不同的 ID，例如。在 7 月 90 相同的 ID 離開
請幫忙！
我被卡住了，因為我的 Sql 不是很先進，...
with total as (
select distinct(transactions.u_id), count(*)
from transactions
join contacts using (u_id)
join table using (contact_id)
where transactions.when_created between '2020-06-01' AND '2020-06-30'
group by transactions.u_id
HAVING COUNT(*) > 1
)
SELECT
COUNT(*)
FROM
total
uj5u.com熱心網友回復：
假設您對類似的查詢感興趣
select transactions.u_id, count(*) as total
from transactions
join contacts using (u_id)
join table using (contact_ud)
where transactions.when_created between '2020-06-01' and '2020-06-30'
group by transactions.u_id;
你也有興趣
select transactions.u_id, count(*) as total
from transactions
join contacts using (u_id)
join table using (contact_ud)
where transactions.when_created between '2020-08-01' and '2020-08-30'
group by transactions.u_id;
你想得到：
- 兩者都可以找到的ID
- 最低總數
然后，你可以做類似的事情
select t1.u_id,
case
when t1.total > t2.total then t2.total
else t1.total
end as total
from (
select transactions.u_id, count(*) as total
from transactions
join contacts using (u_id)
join table using (contact_ud)
where transactions.when_created between '2020-06-01' and '2020-06-30'
group by transactions.u_id) t1
join (
select transactions.u_id, count(*) as total
from transactions
join contacts using (u_id)
join table using (contact_ud)
where transactions.when_created between '2020-06-01' and '2020-06-30'
group by transactions.u_id) t2
on t1.u_id = t2.u_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/341482.html
標籤：PostgreSQL