假設一張表有以下欄位
table(mytable)
欄位(
account_id
account_name
given_name
family_id
create_time
last_time)
我想要抓 min(create_time ) 還有 to_timestamp(max(last_time))
應該一個account_id只會有一筆資料。
只有group by account_id 沒問題
但加入其他欄位會有group問題。
我的語法
select account_id ,account_name,given_name,family_id, to_timestamp(max(last_time)) as max ,min(create_time) as min from mytable where account_name = 'test' group by account_id,account_name,id,name,family_id ;
這樣會有group語法的問題
請問該怎麼解決呢
麻煩大大了
後來研究出來
select t1.account_id , t1.account_name,t1.given_name,t1.family_id ,to_timestamp(t1.last_saving_time),t1.create_time , t2.max , t2.min from mytable t1 join(select account_id,max(to_timestamp(last_saving_time)) as max,min(create_time) as min from mytable group by account_id )t2 on t1.account_id = t2.account_id where account_name = 'testscartest' ;
account_id | account_name |given_name |family_id | to_timestamp | create_time | max | min
------------+--------------+-----------+------------+-------+----------+-----------+------------------------+----------------------------+--------------
93360 | testscartest |123213 | 17 | 2017-08-14 12:00:34+02 | 2016-11-22 14:30:48.596572 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest | CharA41 | 0 | 2017-06-29 16:41:03+02 | 2017-05-26 09:02:56.98402 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest |rrr68 | 0 | 2017-03-27 12:09:46+02 | 2017-03-27 12:08:33.519167 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest |CharC46 | 0 | 2017-05-31 13:32:47+02 | 2017-05-26 09:09:15.235178 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest |tt78 | 0 | 2017-02-22 17:47:18+01 | 2017-02-22 17:44:53.431055 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest |tb67 | 0 | 2017-02-22 17:44:27+01 | 2017-02-22 17:41:38.875702 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest | CharD49 | 0 | 2017-05-31 13:35:37+02 | 2017-05-26 09:10:38.367314 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest | tes214a | 0 | 2017-07-18 11:57:18+02 | 2017-07-18 11:54:15.102679 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest | rt66 | 0 | 2017-02-22 17:41:10+01 | 2017-02-22 17:32:50.174684 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest | tr89 | 0 | 2017-07-05 17:51:29+02 | 2017-02-22 17:47:47.337269 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest |TesterXL | 14 | 2017-07-04 08:45:21+02 | 2016-09-26 12:27:46.510273 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest | CharB42 | 0 | 2017-05-31 13:31:02+02 | 2017-05-26 09:07:39.658745 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
93360 | testscartest | asd21q |16 | 2017-07-27 10:26:36+02 | 2017-07-18 11:58:18.694678 | 2017-08-14 12:00:34+02 | 2016-09-26 12:27:46.510273
這個帳號我只需要顯示2筆
但還是通通篩出來了
一筆資料只需要
1.最早創立時間的given_name
2.最晚登入的given_name
min,max 只有顯示這個帳號的min,max
帳號內的 id , given_name 還是沒有篩出來
已邀請的邦友 {{ invite_list.length }}/5
你要的應該是種概念，分別做兩次群組取回最早建立和最後存檔的「兩筆」資料，
然後再到原本的資料表把這兩筆的聯集取出來，再群組成一筆。語法大概像下面這樣(沒有環境測試，請自行debug)
select t1.account_id , t1.account_name,t1.family_id,max(max) as max,max(case when max is not null then t1.given_name else null end) as max_give_name
,max(min) as min,max(case when min is not null then t1.given_name else null end ) as min_give_name
from #mytable t1
left join(select account_id,max(to_timestamp(last_saving_time)) as max
from #mytable group by account_id )t2 on t1.account_id = t2.account_id and to_timestamp(t1.last_saving_time)=max
left join(select account_id,min(create_time) as min
from #mytable group by account_id )t3 on t1.account_id = t3.account_id and t1.create_time=min
where account_name = 'testscartest'
and ( t2.account_id is not null or t3.account_id is not null )
group by t1.account_id , t1.account_name,t1.family_id ;
感覺樓主還是沒弄懂 group by 的含意，
「每一個帳號要有兩筆資料」光這句話跟你的內容描述有衝突了，發現了嗎?
===↓↓↓===
1.最早創立時間的given_name
2.最晚登入的given_name
寫法中又已將 max , min 並列 >> 這叫做一筆資料
把非唯一拿來group by，然後腦中想要兩筆資料手上卻寫成一筆樓主您到底要啥?
建議把你要的結果格式直接畫成表格列出來吧
否則您個人認為的正確結果有可能其實還是錯誤的!!
根本不用Group呀~有沒有用過UNIONS指令?
將兩次Select的結果串起來就好~
Select * from mytable where account_name = 'test' Order by create_time limit 1
UNIONS
Select * from mytable where account_name = 'test' Order by last_time desc limit 1
沒有在用Postgresql,但有Google一下指令,應該是樓主要的吧~
union沒辦法哦 !!
union 我本來也是用這個 語法跟你一模一樣 但發現不是我要的資料
我也只是新手 不知道該怎麼解釋 冏
後來研究一下發現會有邏輯上的問題 如同前面幾位大大說的那樣
感謝tfchien大大看得懂我的外星語言 冏 再次謝謝各位願意回我文章的大大~
不是您要的資料嗎?那表示我還是沒看懂您的問題...
應該是我問題敘述方式沒有很詳細...
就像@tzungshiun 大大說的一樣
{1.最早創立時間的given_name
2.最晚登入的given_name
寫法中又已將 max , min 並列 >> 這叫做一筆資料}
而且還不是只有given_name,我知道這本身邏輯上就不太對...
所以就像@tfchien大的解答那樣 先做兩次群組取回我要的兩筆資料
再把資料表聯集取出來，再合成一筆
iThome鐵人賽
看影片追技術