- 浏览: 316732 次
- 性别:
- 来自: 武汉
-
文章分类
- 全部博客 (185)
- C# (8)
- Java (21)
- Oracle (25)
- mysql (3)
- SQLite (9)
- PHP (0)
- 虚拟机 (6)
- Tomcat (5)
- WebService (9)
- Linux (4)
- Windows (0)
- 域名和空间 (5)
- 测试 (1)
- Javascript (2)
- cache (1)
- 认证 (1)
- 图形报表 (1)
- Eclipse&plugins (1)
- struts2 (2)
- Swing (2)
- maven (1)
- BAT (3)
- JqueryUI (1)
- WEB (1)
- Jquery (1)
- 软件 (1)
- Google (1)
- sitemesh (1)
- Spring (1)
- 字体 (1)
- log4j (1)
- 日志级别 (1)
- 控制中心 (1)
- Log4jManager (1)
- log4j.jsp (1)
最新评论
-
chao_t：
不可以喃，楼主
实时控制log4j日志输出级别-Log4J日志级别控制中心 -
zercle：
感谢分享，还在下载中，一直都用plsql，昨天听别人说toad ...
Toad for Oracle 11绿色版本,加上plsql developerv9.0.1.1613 -
cmland：
赞一个，可以使用
Toad for Oracle 11绿色版本,加上plsql developerv9.0.1.1613 -
mozheshashou：
大哥 关键是那个服务器端怎么写啊 ，求指教啊，
说说JSON和JSONP，也许你会豁然开朗，含jQuery用例 -
bjxyj：
net.sourceforge.jdbclogger.Jdbc ...
p6spy的替代品：jdbc logger
两张表
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","早餐,中餐,晚餐,夜宵"
.......
发表评论
相关推荐
现在我们有了每行及其下一个行号的信息，可以利用`SYS_CONNECT_BY_PATH`函数进行递归连接，从而实现行转列的目的。 ```sql SELECT NO, LTRIM(MAX(SYS_CONNECT_BY_PATH(VALUE, ';')), ';') AS VALUE, LTRIM(MAX...
2. **使用`SYS_CONNECT_BY_PATH`函数**：该函数可以递归地遍历每一组中的所有记录，并生成一个由逗号连接的字符串。 3. **使用`ROW_NUMBER()`与`PARTITION BY`结合**：再次使用`ROW_NUMBER()`函数，并结合`PARTITION...
行列转换,sys_connect_by_path,row_number等函数的用法
在Oracle中，我们可以利用`CONNECT BY`和`SYS_CONNECT_BY_PATH`函数来实现这个目标。`CONNECT BY`用于建立树形连接，而`SYS_CONNECT_BY_PATH`则可以沿着这些连接路径收集数据。 以下是一个详细的步骤解释： 1. ...
- 使用`sys_connect_by_path()`函数将`course`列中的所有值连接起来，默认分隔符为`*`。 - 通过`SUBSTR(sys_connect_by_path(course, '*'), 2)`去除第一个星号。 - 使用`REPLACE()`函数将剩余的星号替换为分号`;`...
Oracle 中分组后拼接分组字符串 本文主要介绍了在 Oracle 中如何对分组后的数据进行拼接操作，生成...通过使用 `row_number()` 函数、`lead()` 函数和 `sys_connect_by_path()` 函数，我们可以轻松地实现这个操作。
`SYS_CONNECT_BY_PATH` 是另一个常用的方法，它利用Oracle树状查询的功能将行数据连接起来形成一个字符串。通过结合使用`ROW_NUMBER()`函数，可以确保字符串的构建遵循预期的顺序。这种方法的优点在于它可以处理任意...
Oracle 中分组后拼接分组字符串 在 Oracle 中，分组后拼接...本文介绍了如何在 Oracle 中使用 `sys_connect_by_path` 函数与 `start` 递归实现分组后拼接分组字符串。该方法可以应用于各种数据分析和报表生成场景。
之后使用`SYS_CONNECT_BY_PATH`函数，通过递归的方式将每个节点下的`ROLE`列值按照指定的分隔符（此处为逗号）进行连接。 以上是对给定文档中几个主要知识点的总结与解释，希望能帮助你更好地理解和掌握这些Oracle ...
`SYS_CONNECT_BY_PATH` 函数可以用来生成层次结构的数据： ```sql SELECT ID, NAME, LTRIM(MAX(SYS_CONNECT_BY_PATH(ROLE, ',')), ',') FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY TABLE1.ID ORDER BY NAME)...
SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAME FROM ( SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1 FROM ( SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn FROM t...
为实现这个目标，我们可以先创建一个包含层级关系的临时表，然后利用`CONNECT BY`来获取整个树的路径，并通过`sys_connect_by_path`函数将路径连接成字符串。最后，通过分组和聚合函数来获取每个父节点的最大路径，...
10.4.2 SYS_CONNECT_BY_PATH函数 279 10.4.3 CONNECT_BY_ROOT运算符 281 10.4.4 CONNECT_BY_ISCYCLE伪列和NOCYCLE参数 284 10.4.5 CONNECT_BY_ISLEAF伪列 287 10.5 小结 291 第11章 半联结和反联结 292 11.1 ...
- **SYS_CONNECT_BY_PATH()**：用于构建层次路径。 - **ORDER SIBLINGS BY**：按照指定顺序排列同级节点。 - **The NOCYCLE**：防止查询进入无限循环。 通过遵循以上SQL最佳实践，可以显著提高数据库应用的性能和可...
CREATE DATABASE LINK db_link_name CONNECT TO remote_user IDENTIFIED BY remote_password USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_host)(PORT=port_number))(CONNECT_DATA=(SERVER=DEDICATED)...
- **SYS_CONNECT_BY_PATH**：用于层次查询。 - **SYS_CONTEXT**：访问上下文信息。 **第二节 聚集函数** 聚集函数用于汇总一组值。 - **AVG**：计算平均值。 - **COUNT**：统计非NULL值的数量。 - **DENSE_RANK...
- 由行和列组成的数据结构，用于存储数据。 - **列（Column）** - 表中的每一列代表一种数据类型。 - **数据类型（DataType）** - 如整数类型、字符串类型、日期类型等。 - **记录（Record）** - 表中的一行数据...