表 hotel_pre_sale_order 有字段 order_no,order_remarks,表hotel_order_remark有字段order_no,remark, 表hotel_pre_sale_order和表hotel_order_remark是一对多,现需要将hotel_pre_sale_order中所对应得表hotel_order_remark 中得字段remark进行拼接 然后导入order_remarks
时间: 2023-07-22 19:04:49 浏览: 108
您可以使用以下SQL查询来将hotel_order_remark表中的remark字段进行拼接,并导入到hotel_pre_sale_order表的order_remarks字段中:
```sql
UPDATE hotel_pre_sale_order AS p
SET order_remarks = (
SELECT GROUP_CONCAT(r.remark SEPARATOR '; ')
FROM hotel_order_remark AS r
WHERE r.order_no = p.order_no
GROUP BY r.order_no
);
```
这个查询会将hotel_order_remark表中的remark字段按照order_no进行分组,并使用分号进行拼接。然后将拼接后的结果更新到hotel_pre_sale_order表的order_remarks字段中。请确保两个表之间有正确的关联关系(即order_no字段)。
注意:在执行此查询之前,请务必备份数据以防止意外情况发生。
相关问题
表 hotel_pre_sale_order 有字段 order_no,order_remarks,表hotel_order_remark有字段order_no,remark, 表hotel_pre_sale_order和表hotel_order_remark是一对多,现需要将hotel_pre_sale_order中所对应得表hotel_order_remark 中得字段remark和表hotel_pre_sale_order 中 的 order_remarks字段进行拼接 然后导入 hotel_pre_sale_order 的order_remarks中
您可以使用以下SQL查询来将hotel_order_remark表中的remark字段与hotel_pre_sale_order表中的order_remarks字段进行拼接,并更新到hotel_pre_sale_order表的order_remarks字段中:
```sql
UPDATE hotel_pre_sale_order AS p
SET order_remarks = (
SELECT GROUP_CONCAT(r.remark SEPARATOR '; ')
FROM hotel_order_remark AS r
WHERE r.order_no = p.order_no
GROUP BY r.order_no
);
```
这个查询会将hotel_order_remark表中的remark字段按照order_no进行分组,并使用分号进行拼接。然后将拼接后的结果更新到hotel_pre_sale_order表的order_remarks字段中。请确保两个表之间有正确的关联关系(即order_no字段)。
请注意,在执行此查询之前,请务必备份数据以防止意外情况发生。
编写存储过程 SELECT a.order_id, a.done_date, a.cust_id, a.subscriber_ins_id, a.access_num, c.dev_id, c.dev_name, c.corp_org, c.company_name, c.department_code, c.department_name, c.remarks FROM JOUR1.Om_Subscriber_202301 a LEFT JOIN JOUR1.om_order_202301 b ON a.order_id = b.order_id LEFT JOIN params1.sec_developer c ON b.dev_id = c.dev_id UNION ALL SELECT a.order_id, a.done_date, a.cust_id, a.subscriber_ins_id, a.access_num, c.dev_id, c.dev_name, c.corp_org, c.company_name, c.department_code, c.department_name, c.remarks FROM JOUR1.Om_Subscriber_202302 a LEFT JOIN JOUR1.om_order_202302 b ON a.order_id = b.order_id LEFT JOIN params1.sec_developer c ON b.dev_id = c.dev_id UNION ALL SELECT a.order_id, a.done_date, a.cust_id, a.subscriber_ins_id, a.access_num, c.dev_id, c.dev_name, c.corp_org, c.company_name, c.department_code, c.department_name, c.remarks FROM JOUR1.Om_Subscriber_202303 a LEFT JOIN JOUR1.om_order_202303 b ON a.order_id = b.order_id LEFT JOIN params1.sec_developer c ON b.dev_id = c.dev_id UNION ALL SELECT a.order_id, a.done_date, a.cust_id, a.subscriber_ins_id, a.access_num, c.dev_id, c.dev_name, c.corp_org, c.company_name, c.department_code, c.department_name, c.remarks FROM JOUR1.Om_Subscriber_202304 a LEFT JOIN JOUR1.om_order_202304 b ON a.order_id = b.order_id LEFT JOIN params1.sec_developer c ON b.dev_id = c.dev_id
你可以编写一个存储过程来执行这个查询。下面是一个示例:
```sql
CREATE OR REPLACE PROCEDURE get_data AS
BEGIN
-- 创建临时表来保存结果
CREATE GLOBAL TEMPORARY TABLE temp_data (
order_id NUMBER,
done_date DATE,
cust_id NUMBER,
subscriber_ins_id NUMBER,
access_num VARCHAR2(100),
dev_id NUMBER,
dev_name VARCHAR2(100),
corp_org VARCHAR2(100),
company_name VARCHAR2(100),
department_code VARCHAR2(100),
department_name VARCHAR2(100),
remarks VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;
-- 第一个查询
INSERT INTO temp_data
SELECT a.order_id,
a.done_date,
a.cust_id,
a.subscriber_ins_id,
a.access_num,
c.dev_id,
c.dev_name,
c.corp_org,
c.company_name,
c.department_code,
c.department_name,
c.remarks
FROM JOUR1.Om_Subscriber_202301 a
LEFT JOIN JOUR1.om_order_202301 b
ON a.order_id = b.order_id
LEFT JOIN params1.sec_developer c
ON b.dev_id = c.dev_id;
-- 后续查询使用UNION ALL连接
INSERT INTO temp_data
SELECT a.order_id,
a.done_date,
a.cust_id,
a.subscriber_ins_id,
a.access_num,
c.dev_id,
c.dev_name,
c.corp_org,
c.company_name,
c.department_code,
c.department_name,
c.remarks
FROM JOUR1.Om_Subscriber_202302 a
LEFT JOIN JOUR1.om_order_202302 b
ON a.order_id = b.order_id
LEFT JOIN params1.sec_developer c
ON b.dev_id = c.dev_id;
INSERT INTO temp_data
SELECT a.order_id,
a.done_date,
a.cust_id,
a.subscriber_ins_id,
a.access_num,
c.dev_id,
c.dev_name,
c.corp_org,
c.company_name,
c.department_code,
c.department_name,
c.remarks
FROM JOUR1.Om_Subscriber_202303 a
LEFT JOIN JOUR1.om_order_202303 b
ON a.order_id = b.order_id
LEFT JOIN params1.sec_developer c
ON b.dev_id = c.dev_id;
INSERT INTO temp_data
SELECT a.order_id,
a.done_date,
a.cust_id,
a.subscriber_ins_id,
a.access_num,
c.dev_id,
c.dev_name,
c.corp_org,
c.company_name,
c.department_code,
c.department_name,
c.remarks
FROM JOUR1.Om_Subscriber_202304 a
LEFT JOIN JOUR1.om_order_202304 b
ON a.order_id = b.order_id
LEFT JOIN params1.sec_developer c
ON b.dev_id = c.dev_id;
-- 查询结果
SELECT * FROM temp_data;
-- 删除临时表
DROP TABLE temp_data;
END;
/
```
这个存储过程创建了一个临时表 `temp_data` 来保存查询结果,并将每个查询的结果插入到临时表中。最后,通过查询 `temp_data` 表来获取结果。
请注意,根据你的实际需求和数据库结构,你可能需要修改表和列的定义,以及查询的逻辑。确保在执行存储过程之前,已经创建了所需的表和索引。
阅读全文