JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 連續資料不同Type顯示問題
王進平





發文: 49
積分: 0
於 2018-01-30 11:21 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
假若資料狀態為

tablename t1

id Type
01  A
02  A
03  B
04  B
05  B
06  A
07  A
08  C
09  C
10  C
11  A
12  A
13  A
14  A
15  B
16  B
17  B
18  B

想要顯示結果為
01-02 A
03-05 B
06-07 A
08-10 C
11-14 A
15-18 B

請問Sql該如何下呢?謝謝


reply to postreply to post
作者 Re:連續資料不同Type顯示問題 [Re:王進平]
kentyeh





發文: 641
積分: 6
於 2018-01-31 01:27 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
要找出這個SQL有幾個要點,要能夠為查詢結果加上序號(DB2,Oracle,M$ SQL,PostgreSQL,H2都可有 rownum over 的功能),
而且你的表格t1的id也要改成整數,才好檢查是否具備連續性,所以Table Schema建立如下
1
2
3
4
create table t1(
  id int primary key,
  type varchar(1) not null
);

以Postgresql為例
查詢SQL如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select sid,eid,type from 
(select id as sid,type,row_number() OVER () as rnum
   from t1
  where exists(select 1 from t1 as t1s
                where t1s.type=t1.type and t1s.id=t1.id+1)
and not exists(select 1 from t1 as t1s
                where t1s.type=t1.type and t1s.id=t1.id-1)) as t1s
inner join 
(select id as eid,row_number() OVER () as rnum
   from t1
  where exists(select 1 from t1 as t1e
                where t1e.type=t1.type and t1e.id=t1.id-1)
and not exists(select 1 from t1 as t1e
                where t1e.type=t1.type and t1e.id=t1.id+1)
) as t1e on t1s.rnum=t1e.rnum
union
select id,id,type from t1
 where not exists(select 1 from t1 as t1b
                   where t1b.type=t1.type and t1b.id=t1.id-1)
   and not exists(select 1 from t1 as t1a
                   where t1a.type=t1.type and t1a.id=t1.id+1)
order by 1

上述查詢結果請參考這個SQLFiddle查詢


reply to postreply to post
» JWorld@TW »  JDBC/SQL討論區

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8