描述一下需求:一张序列号表 : serialinfo 用于保存生成的序列号
一张注册信息表: registerlog 用于记录序列号的使用情况假如现在序列号表serialinfo 中有 10个序列号,同一批次的 条件(productid = 1 and mapgroupid = 1)现在,有5个已经使用过,所以registerlog 记录这5个使用过的序列号信息,并保存安装号和激活码SQL:查处这一批的序列号中,那些使用过,那些没使用过,使用过的显示安装号和激活码
CREATE TABLE SerialInfo(
PRODUCTNO NUMBER(22) NOT NULL ,
PRODUCTID NUMBER(22) NOT NULL ,
SERIALTEXT VARCHAR2(64) NOT NULL,
OPERATERID NUMBER(22) NOT NULL ,
REGISTERTIMES NUMBER(22) NOT NULL,
CHECKEDOUT CHAR(1) ,
USERID NUMBER(22),
MAPGROUPNO NUMBER(32) ,
TYPE CHAR(1),
BASE NUMBER(22)
)TABLESPACE NAVI_REGISTER
/CREATE TABLE REGISTERLOG(
INSTALLCODE VARCHAR2(64) NOT NULL,
SURECODE VARCHAR2(32) NOT NULL,
SERIALTEXT VARCHAR2(64) NOT NULL,
PRODUCTID NUMBER(22) NOT NULL,
MAPGROUPID NUMBER(22) NOT NULL,
AUTHORIZETIME DATE ,
ORIPRODUCTID VARCHAR2(64) NOT NULL,
BAK VARCHAR2(32)
)TABLESPACE NAVI_REGISTER
/
初始信息
INSERT INTO SerialInfo VALUES(204,1,'QR96B-3JW3F-H2XGK-YW9BF-DCW4C',9,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(205,1,'KX7B8-QDBXW-63BVB-PX22G-XXDWK',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(206,1,'9GDWQ-YQJ46-PK4BC-4JHFY-6HXJV',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(207,1,'M4WJR-D33Y4-WGCD2-FGMBP-7TMXB',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(208,1,'W7KDJ-QWRRR-GCC62-WD6T9-HPX7X',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(209,1,'8FBGM-PGGY6-YQHP8-D34T8-V64XD',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(210,1,'BWP24-DRDVG-4QGVC-YX6T7-G6JGJ',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(211,1,'M6264-9Q94M-QMXHD-Y2WCK-KVDWJ',10,0,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(212,1,'8PMXG-G9BJV-G9CD2-V8HQR-TPXK3',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(213,1,'GBVPY-CV8YK-BQMWP-JJRPT-34DTB',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(214,1,'B49JX-BFGC3-PBHDY-2HQW8-FCXT3',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(1,2,'PGH67-JRBCH-3B2GG-KQQWG-7X84B',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(2,2,'THWQW-7XWR9-W9FTG-KCV3H-BV89B',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(3,2,'MRT3M-83WDX-R6KQD-6V89Q-BH79J',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(4,2,'WGGHD-4MMVD-GQJ6M-8P2HW-P8T4K',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(5,2,'4RX3X-KKP2G-B2B82-QK4XQ-36PKG',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(7,3,'VHTQH-997F9-MD8GH-P8346-W2X2Q',19,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(8,3,'B4H4G-TGFQ4-FKHVB-PPPVT-986H3',19,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(9,3,'8KWRM-VV9F4-T7BQC-YHHYQ-J429D',19,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(6,2,'H62F9-36XBM-2JQGG-YR27K-WJ6TC',20,1,0,2,1,0, '');INSERT INTO registerlog VALUES('34Q2D4BWWBB3WCH2KB8D','T8TGJTB6B2TPM4CY','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-4-17 10:24:32','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('34X2CWB4WB4DBB4BTBQM','GW4QY69369QJF8B2','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-3-15 13:44:13','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('3BHFBG493BQ2C4CDC8DF','PTFBG9X78T6YW3BD','BMKCH-KQ279-XHGHP-2J96R-6H43B',1,1,to_date('2010-3-20 17:04:31','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('3BKB2C4P3BXBC43TDVQM','PFBMXGWJ797Y8328','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-4-7 17:18:16','yyyy-mm-dd hh24:mi:ss'),'' ,'' );
INSERT INTO registerlog VALUES('424BBWCQWBB3TB6B6BHH','498MWPGKV4TXBXCJ','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-3-12 16:54:03','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('62W4JTD4W3BDVBQ2PBFV','77KFFD7WGPQHXJYQ','G72V2-8C97X-6YHP8-QXJYT-46YTP',1,1,to_date('2010-4-15 16:09:08','yyyy-mm-dd hh24:mi:ss'),'D7XJR-T7QTJ-48BVB-HT9BP-PC4TD' ,'');
INSERT INTO registerlog VALUES('6BFTB4BWPC4DTDQBTCWH','RDTT6JXYV93JJGG3','R3699-XTHHH-6MJP2-GWD29-8PC9F',1,2,to_date('2010-4-15 14:45:42','yyyy-mm-dd hh24:mi:ss'),'D7XJR-T7QTJ-48BVB-HT9BP-PC4TD' ,'');
INSERT INTO registerlog VALUES('6BW2B2B4TBVBQ2GTGB28','TQPT6VP7T2469WXC','C9YX9-FFP4R-JK7VH-7XTTT-9B2BQ',1,1,to_date('2010-4-14 18:03:49','yyyy-mm-dd hh24:mi:ss'),'2V33R-9YJQ2-4TQVH-P6HJ9-9GPBK' ,'');
INSERT INTO registerlog VALUES('6BW2BBBXTBVBQ2BTJB8D','TDM4PMBW4B942DCM','QGKM7-XDXMJ-C6HDY-WXQXM-96HP7',1,1,to_date('2010-4-14 16:57:31','yyyy-mm-dd hh24:mi:ss'),'4PKVD-3YM8K-XCH68-4CFVQ-HWPRC' ,'');
INSERT INTO registerlog VALUES('6BW4BVB8TBVBPBDTJBCV','YWXMK3Q62VBR7BRP','TD8JX-WHCYP-B86GJ-HX67H-R496H',1,1,to_date('2010-4-14 18:13:08','yyyy-mm-dd hh24:mi:ss'),'Q8HVP-6W36C-2364W-4DMH2-BVJ83' ,'');
初始数据 不知道是不是都匹配的,随即复制了一些出来大家帮忙想一下SQL 怎么写 谢谢
一张注册信息表: registerlog 用于记录序列号的使用情况假如现在序列号表serialinfo 中有 10个序列号,同一批次的 条件(productid = 1 and mapgroupid = 1)现在,有5个已经使用过,所以registerlog 记录这5个使用过的序列号信息,并保存安装号和激活码SQL:查处这一批的序列号中,那些使用过,那些没使用过,使用过的显示安装号和激活码
CREATE TABLE SerialInfo(
PRODUCTNO NUMBER(22) NOT NULL ,
PRODUCTID NUMBER(22) NOT NULL ,
SERIALTEXT VARCHAR2(64) NOT NULL,
OPERATERID NUMBER(22) NOT NULL ,
REGISTERTIMES NUMBER(22) NOT NULL,
CHECKEDOUT CHAR(1) ,
USERID NUMBER(22),
MAPGROUPNO NUMBER(32) ,
TYPE CHAR(1),
BASE NUMBER(22)
)TABLESPACE NAVI_REGISTER
/CREATE TABLE REGISTERLOG(
INSTALLCODE VARCHAR2(64) NOT NULL,
SURECODE VARCHAR2(32) NOT NULL,
SERIALTEXT VARCHAR2(64) NOT NULL,
PRODUCTID NUMBER(22) NOT NULL,
MAPGROUPID NUMBER(22) NOT NULL,
AUTHORIZETIME DATE ,
ORIPRODUCTID VARCHAR2(64) NOT NULL,
BAK VARCHAR2(32)
)TABLESPACE NAVI_REGISTER
/
初始信息
INSERT INTO SerialInfo VALUES(204,1,'QR96B-3JW3F-H2XGK-YW9BF-DCW4C',9,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(205,1,'KX7B8-QDBXW-63BVB-PX22G-XXDWK',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(206,1,'9GDWQ-YQJ46-PK4BC-4JHFY-6HXJV',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(207,1,'M4WJR-D33Y4-WGCD2-FGMBP-7TMXB',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(208,1,'W7KDJ-QWRRR-GCC62-WD6T9-HPX7X',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(209,1,'8FBGM-PGGY6-YQHP8-D34T8-V64XD',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(210,1,'BWP24-DRDVG-4QGVC-YX6T7-G6JGJ',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(211,1,'M6264-9Q94M-QMXHD-Y2WCK-KVDWJ',10,0,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(212,1,'8PMXG-G9BJV-G9CD2-V8HQR-TPXK3',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(213,1,'GBVPY-CV8YK-BQMWP-JJRPT-34DTB',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(214,1,'B49JX-BFGC3-PBHDY-2HQW8-FCXT3',10,1,0,0,1,0,'');
INSERT INTO SerialInfo VALUES(1,2,'PGH67-JRBCH-3B2GG-KQQWG-7X84B',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(2,2,'THWQW-7XWR9-W9FTG-KCV3H-BV89B',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(3,2,'MRT3M-83WDX-R6KQD-6V89Q-BH79J',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(4,2,'WGGHD-4MMVD-GQJ6M-8P2HW-P8T4K',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(5,2,'4RX3X-KKP2G-B2B82-QK4XQ-36PKG',11,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(7,3,'VHTQH-997F9-MD8GH-P8346-W2X2Q',19,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(8,3,'B4H4G-TGFQ4-FKHVB-PPPVT-986H3',19,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(9,3,'8KWRM-VV9F4-T7BQC-YHHYQ-J429D',19,1,0,0,1,0, '');
INSERT INTO SerialInfo VALUES(6,2,'H62F9-36XBM-2JQGG-YR27K-WJ6TC',20,1,0,2,1,0, '');INSERT INTO registerlog VALUES('34Q2D4BWWBB3WCH2KB8D','T8TGJTB6B2TPM4CY','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-4-17 10:24:32','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('34X2CWB4WB4DBB4BTBQM','GW4QY69369QJF8B2','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-3-15 13:44:13','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('3BHFBG493BQ2C4CDC8DF','PTFBG9X78T6YW3BD','BMKCH-KQ279-XHGHP-2J96R-6H43B',1,1,to_date('2010-3-20 17:04:31','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('3BKB2C4P3BXBC43TDVQM','PFBMXGWJ797Y8328','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-4-7 17:18:16','yyyy-mm-dd hh24:mi:ss'),'' ,'' );
INSERT INTO registerlog VALUES('424BBWCQWBB3TB6B6BHH','498MWPGKV4TXBXCJ','6GW6J-3GM78-G4382-FTJPF-8QQDC',1,1,to_date('2010-3-12 16:54:03','yyyy-mm-dd hh24:mi:ss'),'','' );
INSERT INTO registerlog VALUES('62W4JTD4W3BDVBQ2PBFV','77KFFD7WGPQHXJYQ','G72V2-8C97X-6YHP8-QXJYT-46YTP',1,1,to_date('2010-4-15 16:09:08','yyyy-mm-dd hh24:mi:ss'),'D7XJR-T7QTJ-48BVB-HT9BP-PC4TD' ,'');
INSERT INTO registerlog VALUES('6BFTB4BWPC4DTDQBTCWH','RDTT6JXYV93JJGG3','R3699-XTHHH-6MJP2-GWD29-8PC9F',1,2,to_date('2010-4-15 14:45:42','yyyy-mm-dd hh24:mi:ss'),'D7XJR-T7QTJ-48BVB-HT9BP-PC4TD' ,'');
INSERT INTO registerlog VALUES('6BW2B2B4TBVBQ2GTGB28','TQPT6VP7T2469WXC','C9YX9-FFP4R-JK7VH-7XTTT-9B2BQ',1,1,to_date('2010-4-14 18:03:49','yyyy-mm-dd hh24:mi:ss'),'2V33R-9YJQ2-4TQVH-P6HJ9-9GPBK' ,'');
INSERT INTO registerlog VALUES('6BW2BBBXTBVBQ2BTJB8D','TDM4PMBW4B942DCM','QGKM7-XDXMJ-C6HDY-WXQXM-96HP7',1,1,to_date('2010-4-14 16:57:31','yyyy-mm-dd hh24:mi:ss'),'4PKVD-3YM8K-XCH68-4CFVQ-HWPRC' ,'');
INSERT INTO registerlog VALUES('6BW4BVB8TBVBPBDTJBCV','YWXMK3Q62VBR7BRP','TD8JX-WHCYP-B86GJ-HX67H-R496H',1,1,to_date('2010-4-14 18:13:08','yyyy-mm-dd hh24:mi:ss'),'Q8HVP-6W36C-2364W-4DMH2-BVJ83' ,'');
初始数据 不知道是不是都匹配的,随即复制了一些出来大家帮忙想一下SQL 怎么写 谢谢
解决方案 »
- 如何才算恰当的退出循环
- 服务器端 用username/password登录不成功 但用username/password@alias登录成功 是什么问题?
- 为什么我装完ORACLE817后,内存占用达到1G多呢?我该怎么办呢?好急
- oracle定时任务不执行
- 如何在oracle90中查出数据量在1000以上的所有表id
- Oracle和C#数据类型问题
- 关于impdp与expdp的一点疑问?
- 要用Group By的话,在Select语句中就要用分组函数吗(例如sum(*) ¦max(*))!
- 排序。
- oracle8.1.7在2000pro下的安装问题
- 存储过程编译通不过!
- 如何返回rowid
结果就是 把 一个批次的 序列号都现实出来 (serialInfo ) 【product =1 and mapgroupid=1 表示一个批次】
显示结果:显示序列号中那些是注册过的,那些是没注册过的,注册过的显示安装号和激活码类似这个样子
序列号 安装号 激活码
aaaaa
bbbbb 12345 67890
ccccc 12121 23232
ddddd a 和 d 是没使用过的序列号 所以在registerlog中美有安装号和激活码
b 和 c 是已经用过的序列号,所以在registerlog表中有安装号和激活码两张表以serialtext序列号 做关联有办法吗?
NVL2(R.SERIALTEXT,R.INSTALLCODE,'') AS 安装号,
NVL2(R.SERIALTEXT,R.SURECODE,'') as 激活码
from serialinfo s,registerlog r
where s.serialtext=r.serialtext(+)
试试,可以?
从效果上看 是达到了,我在看看数据?
不是很明白这句SQL 的意思能讲解下吗?r.serialtext(+) 、NVL2(R.SERIALTEXT,R.SURECODE,'') as 激活码
NVL2(R.SERIALTEXT,R.SURECODE,'') 就是当R.SERIALTEXT为空则返回'',不为空则返回R.SURECODE