分別是兩張表
學校表：id、學校id、學校名稱
教師表：id、學校id、教師id、教師名稱
因為一間學校存在多名教師，我想統計出來的是這樣的：
第一間學校名稱 教師名稱
教師名稱
教師名稱
教師名稱
第二間學校名稱 教師名稱
教師名稱
教師名稱
教師名稱
這樣格式怎么寫SQL陳述句，各位幫忙一下
學校名稱不重復
uj5u.com熱心網友回復：
/*只能這樣了 或者你直接關聯在代碼中處理格式。。
oracle 中是沒有合并單元格的
*/
with a as (
select 'xx1' "學校id",'學校1' "學校名稱" from dual union all
select 'xx2' ,'學校2' from dual union all
select 'xx3' ,'學校3' from dual
),b as (
select 'xx1' "學校id",'教師1' "教師名稱" from dual union all
select 'xx1' ,'教師1' from dual union all
select 'xx1' ,'教師1' from dual union all
select 'xx2' ,'教師2' from dual union all
select 'xx2' ,'教師2' from dual union all
select 'xx2' ,'教師2' from dual union all
select 'xx3' ,'教師3' from dual union all
select 'xx3' ,'教師3' from dual union all
select 'xx3' ,'教師3' from dual
)SELECT A."學校名稱",WMSYS.WM_CONCAT(B."教師名稱") FROM A,B WHERE A."學校id" = B."學校id"
group by A."學校名稱" order by a."學校名稱"
uj5u.com熱心網友回復：
借樓上資料：
with a as (
select 'xx1' as id,'學校1' as teacher from dual union all
select 'xx2' ,'學校2' from dual union all
select 'xx3' ,'學校3' from dual
),b as (
select 'xx1' as id,'教師1' as teacher from dual union all
select 'xx1' ,'教師1' from dual union all
select 'xx1' ,'教師1' from dual union all
select 'xx2' ,'教師2' from dual union all
select 'xx2' ,'教師2' from dual union all
select 'xx2' ,'教師2' from dual union all
select 'xx3' ,'教師3' from dual union all
select 'xx3' ,'教師3' from dual union all
select 'xx3' ,'教師3' from dual
)
select case when a.id=lag(a.id) over(partition by a.id order by a.id) then '' else a.id end as id,b.teacher
from a,b where a.id = b.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/87765.html
標籤：基礎和管理
上一篇：oracle行轉列SQL