sql语句：多行数据合并；主外键查询，数据追加

一、多行数据合并为一行的数据

数据库中数据：

fd_id	fd_type	fd_createDate
001	a	2021-08-05
002	b	2021-08-06
003	a	2021-08-05
004	c	2021-08-06
005	a	2021-08-06
006	c	2021-08-06

需要查询出来的结果：

fd_type	fd_id
a	001,003,005
b	002
c	004,006

方法一:

select fd_type,wm_concat(fd_id)  fd_id from DICT 	 GROUP BY  fd_type

方法二：

select  fd_type,listagg( fd_id, ',' ) within group ( order by fd_order ) as fd_id from Dict 
 GROUP BY fd_type;

二、查询主表外键关联的表名称

select a.table_name 主表,
b.table_name 外键关联表,
column_name 键,
substr(position,1,1) P
from
user_constraints a, user_constraints b, user_cons_columns c
where
a.constraint_name = b.r_constraint_name
and a.constraint_name = c.constraint_name
and a.table_name='PROJECT'--PROJECT主表名称
order by 1, 2, 4;

查询结果：

主表	外键关联表	键	P
PROJECT	CONFIRM_MAIN	FD_ID	1
PROJECT	MAJORCONFIRM_MAIN	FD_ID	1
PROJECT	MAJORDISTR_MAIN	FD_ID	1
PROJECT	NOTDESIGNCONFIRM_MAIN	FD_ID	1
PROJECT	PERSONCONFIRM_MAIN	FD_ID	1
PROJECT	STAGEAPPLY_MAIN	FD_ID	1
PROJECT	STAGEDISTR_MAIN	FD_ID	1
PROJECT	TOTALDISTR_MAIN	FD_ID	1

三、根据外键名称查询表名称

## oracle:
select * from dba_constraints where constraint_name='FK558993A96DF4C8B1' and constraint_type = 'R';

## slqserver:
SELECT oSub.name AS [子表名称] ,
            fk.name AS [外键名称] ,
            SubCol.name AS [子表列名] ,
            oMain.name AS [主表名称] ,
            MainCol.name AS [主表列名]
FROM sys.foreign_keys fk
            JOIN sys.all_objects oSub ON ( fk.parent_object_id = 
            oSub.object_id )
            JOIN sys.all_objects oMain ON ( fk.referenced_object_id 
            = oMain.object_id )
            JOIN sys.foreign_key_columns fkCols ON ( fk.object_id = 
            fkCols.constraint_object_id )
            JOIN sys.columns SubCol ON ( oSub.object_id = 
            SubCol.object_id
            AND fkCols.parent_column_id = SubCol.column_id
            )
            JOIN sys.columns MainCol ON ( oMain.object_id = 
            MainCol.object_id
            AND fkCols.referenced_column_id = MainCol.column_id
            )
WHERE 
           fk.name LIKE 'FKCF54FE2227B14FED';

四、往一个字段追加数据

源表数据：

fd_id	科目
001	语文

update students set 科目=科目||'，数学' where 科目='语文'

修改后的数据：

fd_id	科目
001	语文，数学