利用sys_connect_by_path和row_number实现Oracle行转换列

最新推荐文章于 2019-09-14 13:18:20 发布
最新推荐文章于 2019-09-14 13:18:20 发布
阅读量93 收藏
点赞数
文章标签： Oracle SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xtpebg/article/details/83710288
版权

两张表

create table POS_TD_ORG

(

  ZDBH   VARCHAR2(8) not null,

  CB     NUMBER(1) not null,

  ORG_ID VARCHAR2(8) not null

)

插入数据 

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1002', '0', '7');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200093', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200094', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200095', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200096', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200098', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200099', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20011', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20010', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('zzzz', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('207', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('206', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200084', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200085', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200086', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200087', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200088', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200089', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200090', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200091', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200092', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200091', '3', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200090', '3', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200089', '3', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200092', '3', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200093', '3', '8');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20012', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20010', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2077', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2075', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2073', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2072', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2070', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2068', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2049', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2047', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2045', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2043', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2041', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2039', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2035', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2033', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2031', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2029', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2027', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200098', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20010', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2003', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2005', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2007', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('203', '2', '8');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2009', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('206', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200098', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20010', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2003', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2008', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('203', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2004', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2007', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200088', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200086', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200084', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200082', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000101', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1013', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1012', '2', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('101', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000100', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200050', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200082', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200084', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200088', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('101', '1', '11');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('99999', '0', '7');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('101', '2', '11');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1013', '1', '11');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000100', '1', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200051', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200053', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200052', '0', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200054', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200055', '0', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200056', '0', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200057', '0', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000101', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200050', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200082', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200083', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200097', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2008', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20014', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20013', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20012', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1013', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200093', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200096', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200097', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200089', '4', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200090', '4', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200091', '4', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200092', '4', '16');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200094', '3', '8');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200095', '3', '8');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2009', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2004', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20014', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20013', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2005', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('203', '4', '2');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('206', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('207', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('zzzz', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2080', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2078', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2076', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2074', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2071', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2069', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2065', '0', '19');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2063', '0', '19');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2066', '0', '19');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('207', '2', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('207', '3', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2061', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2059', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2057', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2055', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2053', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2051', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2038', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2036', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2034', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2030', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2028', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2026', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200099', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20012', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20014', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20013', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2005', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('204', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2009', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20014', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200058', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200062', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000101', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1012', '1', '7');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('6666', '0', '5');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1004', '0', '5');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1012', '3', '6');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200084', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200085', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200086', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200087', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200088', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200089', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200090', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200091', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200092', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('205', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('204', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('203', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2009', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2007', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2006', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2005', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2004', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2003', '1', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000100', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200050', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000101', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200082', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200083', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200094', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200095', '2', '15');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200096', '3', '8');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200097', '3', '8');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200093', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200095', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200094', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200097', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200096', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200099', '3', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200098', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2007', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2006', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2003', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20011', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2008', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('204', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('205', '4', '9');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2081', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2079', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2067', '0', '18');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2064', '0', '19');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2062', '0', '19');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('zzzz', '2', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('zzzz', '3', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('206', '3', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('205', '3', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2060', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2058', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2056', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2054', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2052', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2050', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2048', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2046', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2044', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2042', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2040', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2037', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2032', '0', '17');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20011', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20013', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2004', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2006', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2008', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('204', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('205', '2', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200099', '4', '10');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20011', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2006', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20012', '3', '12');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200059', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200061', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200060', '0', '14');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('209', '0', '2');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1100', '2', '6');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1000', '0', '7');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('20001', '0', '5');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1100', '4', '7');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1100', '3', '7');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1100', '1', '6');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200087', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200085', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200083', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200050', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('2000100', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('101', '3', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1013', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('1012', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200083', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200085', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200087', '4', '22');

insert into pos_td_org (ZDBH, CB, ORG_ID)

values ('200086', '4', '22');

----------------------------------------------------------

说明POS_TD_ORG表的CB与POS_MEAL中的MEAL_ID关联

 

create table POS_MEAL

(

  MEAL_ID   NUMBER(1) not null,

  MEAL_NAME VARCHAR2(10) not null,

  BEGIN     CHAR(6) not null,

  END       CHAR(6) not null

)

 

插入数据 

 

insert into pos_meal (MEAL_ID, MEAL_NAME, BEGIN, END)

values ('1', '早餐', '043000', '161959');

 

insert into pos_meal (MEAL_ID, MEAL_NAME, BEGIN, END)

values ('2', '中餐', '162000', '162959');

 

insert into pos_meal (MEAL_ID, MEAL_NAME, BEGIN, END)

values ('3', '晚餐', '163000', '163959');

 

insert into pos_meal (MEAL_ID, MEAL_NAME, BEGIN, END)

values ('4', '夜宵', '164000', '042959');

 

insert into pos_meal (MEAL_ID, MEAL_NAME, BEGIN, END)

values ('0', '全餐', '999999', '999999');

--------------------------------------------------------------------------

开始编写神奇的SQL了，

select zdbh,ltrim(max(sys_connect_by_path(meal_name, ',')), ',') meal_name from 

(

select b.*,a.meal_name,(row_number()over(partition by b.zdbh order by a.meal_id)) as num from pos_meal a ,pos_td_org b where a.meal_id<>0 and a.meal_id=b.cb and a.meal_id<>0

)

start with num = 1 

connect by num - 1 = prior num 

and zdbh = prior zdbh 

group by zdbh

查询结果：

"ZDBH","MEAL_NAME"

"101","早餐,中餐,晚餐,夜宵"

"2000100","早餐,中餐,晚餐,夜宵"

"200089","早餐,中餐,晚餐,夜宵"

"200099","早餐,中餐,晚餐,夜宵"

"2003","早餐,中餐,晚餐,夜宵"

"2005","早餐,中餐,晚餐,夜宵"

"1100","早餐,中餐,晚餐,夜宵"

"200085","早餐,中餐,晚餐,夜宵"

"200095","早餐,中餐,晚餐,夜宵"

"200096","早餐,中餐,晚餐,夜宵"

"200097","早餐,中餐,晚餐,夜宵"

"2009","早餐,中餐,晚餐,夜宵"

"203","早餐,中餐,晚餐,夜宵"

"204","早餐,中餐,晚餐,夜宵"

"205","早餐,中餐,晚餐,夜宵"

"206","早餐,中餐,晚餐,夜宵"

"200084","早餐,中餐,晚餐,夜宵"

"200092","早餐,中餐,晚餐,夜宵"

"200050","早餐,中餐,晚餐,夜宵"

"200083","早餐,中餐,晚餐,夜宵"

"20013","早餐,中餐,晚餐,夜宵"

"2000101","早餐,中餐,晚餐,夜宵"

"200082","早餐,中餐,晚餐,夜宵"

"200086","早餐,中餐,晚餐,夜宵"

"200088","早餐,中餐,晚餐,夜宵"

.......

 

xtpebg
关注
SYS_CONNECT_BY_PATH 函数综合应用 应用多字段拼在一行字符串
weixin_38466660的博客
12-07 1369
SELECT     f.nowdate AS "nowdate",     f.DAYOFWEEK "dayofweek",     f.WEEKOFMONTH AS "weekofmonth",     f.YEARMONTH "yearmonth",     COUNT (1) AS "messageNum",     LTRIM (         MAX (       
SYS_CONNECT_BY_PATH 函数
一切都是浮云
06-11 710
SELECT ename FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; -- 得到结果为： KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MART...
利用Oracle分析函数row_number和sys_connect_by_path实现多行数据合并为一行
diaosoudi0255的博客
11-11 144
demo场景，以oracle自带库中的表emp为例：select ename,deptno from emp order by deptno; ENAME DEPTNO CLARK 10 KING 10 MILLER 10 SMITH 20 ADAMS 20 FORD 20 ...
wmsys.wm_concat、sys_connect_by_path、自定义函数实现行列转换
junmail的专栏
10-08 182
构建测试表： create table TABLE1 ( ID INTEGER, NAME VARCHAR2(10) ) create table TABLE2 ( ID INTEGER, ROLE VARCHAR2(10) ) insert into TABLE1 (ID, NAME) values (1, '张三'); insert into TABLE1 (ID,...
Oracle中使用sys_connect_by_path函数实现行转列
Done
01-09 423
    select category ,MAX(sys_connect_by_path(t.name, ',')) as cate_name from( select a.category,a.name,row_number() over(PARTITION BY category ORDER BY name) as rn from biz_code a ) t START ...
oracle sys_connect_by_path实现行列转换
产品经理，程序人生
04-16 2979
<br />//原帖：http://topic.csdn.net/u/20110412/16/3eec4221-d1f7-4cd2-95d0-27f3ff086632.html?77362 //前几天，一CSDN友友问了一个行列转换的问题。 //问题之后的几分钟之内，好多CSDN友友都给出了正确答案。 //但是，这位兄弟的一句话让大家很是郁闷： //"诺诺的答谢 楼上各位大大。。。只是纠结的说下：oracle版本没到10g。。。没有wm_concat" //wm_concat函数是10g才引入的。
Oracle行转列-列转行各种方式wm_concat函数、LISTAGG函数、PIVOT函数、UNPIVOT函数、非数字，以及自动动态获取要转换的列字段名（超详细）
Hjchidaozhe的博客
09-14 1407
1、上来先看下数据以及实现结果： 行转列（decode方式）： WITH CO_ORDER AS( SELECT 'DOM1' CUSTOMER, 'ZHA01' FACILITY , 4000 TEU FROM DUAL UNION ALL SELECT 'DOM1' CUSTOMER, 'ZHA01' FACILITY , 5000 TEU FROM DUAL U...
sys connect by path 用法
qq_44884577的博客
04-24 203
sys connect by path 用法
oracle多行合并一行
12-29
2. **使用`SYS_CONNECT_BY_PATH`函数**：该函数可以递归地遍历每一组中的所有记录，并生成一个由逗号连接的字符串。 3. **使用`ROW_NUMBER()`与`PARTITION BY`结合**：再次使用`ROW_NUMBER()`函数，并结合`PARTITION...
oracle多行转为字符串总结
01-17
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
Oracle中，用一条Sql实现任意的行转列拼接（不是decode）
北亮的专栏
09-18 4876
说明一下测试环境：Oracle9i，有朋友说10g上测试结果不正确，本人没有条件，所以无法进行测试 表结构和数据如下（表名Test）：NO VALUE  NAME1       a       测试11       b       测试21       c       测试31       d       测试42       e       测试54       f        测
Oracle的SYS_CONNECT_BY_PATH函数
热门推荐
极客神殿
09-14 1万+
在Oracle中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。 sys_connect_by_path(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,’,’)。 　　还有，这个函数使...
oracle之sys_connect_by_path(行转列挺好用的)
lky5387的专栏
12-09 8072
这个函数使用之前必须先建立一个树，否则无用sys_connect_by_path(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,,)。树: start with 条件1 connect by prior 条件2条件1如 子=XX (或父=XX)条件2
向前维泰比算法Matlab代码.rar
10-09
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。
#-ssm-033-mysql-校园二手交易系统-.zip
最新发布
10-09
现如今,校园二手交易系统是商业贸易中的一条非常重要的道路，可以把其从传统的实体模式中解放中来，网上购物可以为消费者提供巨大的便利。通过校园二手交易系统这个平台，可以使用户足不出户就可以了解现今的流行趋势和丰富的商品信息，为用户提供了极大的方便，校园二手交易系统的主要功能包含：商品类别管理、商品的信息管理、订单管理、用户的管理等。网站分为管理员、会员用户这二种用户平台。校园二手交易系统主要使用目前JSP中最流行的SSM框架和Eclipse编辑器、MYSQL数据库设计并实现的。实现了一个校园二手交易系统网站。能够让广大消费者体验到网上平台订餐的乐趣，为网上购物提供了一个管理高效的、决策科学化、安全有效的电子商务平台。 关键词：校园二手交易系统，SSM，Eclipse，商业贸易
MATLAB软件的汽车框定系统【GUI界面版本】.zip
10-09
数据库课程设计
用于开发和测试大脑情感唤醒特征（BAAS）的代码.rar
10-09
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。
Oracle SYS_CONNECT_BY_PATH函数详解与示例
Oracle的SYS_CONNECT_BY_PATH函数是数据库系统中的一个非常实用工具，主要用于处理层级关系数据，尤其是在处理层级查询和行转列（pivot）操作时。该函数的主要功能是通过递归关系将数据按照指定的路径连接成字符串，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值