select ( SELECT listagg(column_name,',') within group (order by column_name) FROM user_tab_columns T WHERE table_name = 'INSURANTLISTNEW' AND t.COLUMN_NAME != 'INSURANTNO' and t.COLUMN_NAME != 'CORRECTIONSTATUS' ) from proposal.insurantlistnew p where p.insurantno = 'e53924eed00b4787933674e538057d57'; 怎么做,才能查出对应的列名的值
时间: 2024-01-31 20:02:16 浏览: 47
您可以使用以下 SQL 查询来查找对应列名的值```sql
SELECT column_name,
( column_value
FROM proposal.insurantnew p
WHERE p.insurantno = 'e53924eed004787933674e538057d57' AND p.column_name = t.column_name) column_value
FROM user T
WHERE table_name 'INSURANTLISTNEW
AND t.COLUMN != 'INSURANTNO'
AND t.COLUMN_NAME != 'CORRECTIONSTATUS';
```
这个查询会返回一个结果集,其中包含列名和对应的值。请注意,您需要将 `INSURANTLIST` 替换为您实际的表名,以及在 `WHERE` 子句将 `'e53924eed00b4787933674e538057d57'` 替换为您要的具体 `insurantno` 值。
相关问题
将以下sql语句中wm_concat 改成listagg SELECT t1.*, t2.P_USER_NAME CREATE_NAME, t3.r_id FROM ( SELECT P_ID, ORG_ID, P_USER_NAME, LOGIN_ID, LOGIN_PWD, P_USER_TYPE, CREATE_ID, DEL_FLAG, CREATE_TIME, MODIFY_ID, MODIFY_TIME, school_rs_id FROM T_XT_ORG_USER WHERE p_id = #{userId} ) t1 LEFT JOIN T_XT_ORG_USER t2 ON t1.p_id = t2.p_id LEFT JOIN ( SELECT p_id, wm_concat (r_id) AS r_id FROM t_xt_role_users t3 GROUP BY p_id ) t3 ON t1.p_id = t3.p_id
可以使用Oracle官方支持的LISTAGG函数来替换wm_concat函数。LISTAGG函数与wm_concat函数类似,也可以将多个行连接成一个字符串,但是它是Oracle官方支持的函数,使用更加稳定可靠。将上述的SQL语句中的wm_concat函数替换为LISTAGG函数,可以这样写:
```
SELECT t1.*, t2.P_USER_NAME CREATE_NAME, t3.r_id
FROM
(
SELECT P_ID, ORG_ID, P_USER_NAME, LOGIN_ID, LOGIN_PWD, P_USER_TYPE,
CREATE_ID, DEL_FLAG, CREATE_TIME, MODIFY_ID, MODIFY_TIME, school_rs_id
FROM T_XT_ORG_USER
WHERE p_id = #{userId}
) t1
LEFT JOIN T_XT_ORG_USER t2 ON t1.p_id = t2.p_id
LEFT JOIN
(
SELECT p_id, LISTAGG(r_id, ',') WITHIN GROUP (ORDER BY r_id) AS r_id
FROM t_xt_role_users t3
GROUP BY p_id
) t3 ON t1.p_id = t3.p_id
```
这里使用了LISTAGG函数来替换了wm_concat函数,同时使用了WITHIN GROUP子句指定了连接多个值时使用的分隔符。注意,使用LISTAGG函数时需要指定WITHIN GROUP子句,否则会报错。
EDP_EDP_EXTERNAL_PLANT_REPORT_U_$RUN_DATE.dat与 SELECT BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'table_name_bad'; SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'table_name_bad'; 及$RUN_DATE 如何将上面查询语句结果集拼接为复合的SELECT语句并将结果用空格隔开?
以下是将查询语句结果集拼接为复合的SELECT语句并将结果用空格隔开的SQL语句:
```
SELECT LISTAGG(result, ' ') WITHIN GROUP (ORDER BY result_order) AS combined_results
FROM (
SELECT 'SELECT ' || bytes || ' FROM ' || segment_name || ';' AS result, 1 AS result_order
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = 'table_name_bad'
UNION ALL
SELECT 'SELECT ' || num_rows || ' FROM ' || table_name || ';' AS result, 2 AS result_order
FROM USER_TABLES
WHERE TABLE_NAME = 'table_name_bad'
UNION ALL
SELECT '$RUN_DATE' AS result, 3 AS result_order
FROM dual
)
ORDER BY result_order;
```
其中,`USER_SEGMENTS`和`USER_TABLES`是Oracle系统表,用于查询表的信息,`dual`是Oracle系统表,用于查询常量。这个查询语句将三个查询结果拼接在一起,并用空格隔开,结果集中每一行都是一个完整的SELECT语句。其中,`$RUN_DATE`是作为一个常量插入到结果集中的,可以根据需要自行修改。