现在需要把数据抽成如下形式
如果同一商品编号的记录 读取少于等于三条
不同商品编号的数据抽取小于等于1000条例如 商品销售明细表;
1 001 20100520 300
2 001 20100521 500
3 001 20100522 400
8 001 20100528 400
4 002 20100523 800
5 002 20100524 300 6 003 20100525 500 7 004 20100526 400读取数据是:
1 001 20100520 300 第四条舍去
2 001 20100521 500
3 001 20100522 400 4 002 20100523 800
5 002 20100524 300 6 003 20100525 500 7 004 20100526 400
from (select id,
no,
time,
value,
row_number() over(partition by no order by no) rn
from tab)
where rn < 4 --第四条舍去
and rownum <= 1000 --不同商品编号的数据抽取小于等于1000条
id NUMBER(18,0), -- ID 主键,递增
itemcode VARCHAR2(10), -- 物料代码(随机生成)
cdate VARCHAR(10), -- 创建日期
amount NUMBER(18,0) -- 物料销售量
);-- 添加主键约束
ALTER TABLE item_tb add constraints pk_item_tb primary key (id); -- 创建序列
CREATE sequence item_tb_seq start with 1 increment by 1 nocycle nomaxvalue;-- 创建递增触发器
CREATE OR REPLACE TRIGGER item_tb_trig_autoinc
BEFORE INSERT ON item_tb
FOR EACH ROW
BEGIN
IF (:new.ID IS NULL) THEN
SELECT item_tb_seq.nextval INTO :new.ID FROM DUAL;
END IF;
END;
/SET SERVEROUTPUT ON;
DECLARE
v_randItemNum NUMBER(18,0); -- 每种物料将要插入的记录行数(随机的,可以自己控制在一个限度内)
v_fromDate VARCHAR2(10); -- 起始日期
v_randAddDay NUMBER(18,0); -- 在起始日期的基础上随机加N天
v_randDate VARCHAR2(10); -- 每条记录随机生成的日期
v_randItem NUMBER(18,0); -- 将要插入的物料总种数
v_randNum NUMBER(18,0); -- 随机生成第三个字段的数值
v_randPerDay NUMBER(18,4); -- 下一条记录比上一条记录要大多少天BEGIN
DELETE FROM item_tb; -- 删除原表数据行
COMMIT; v_fromDate := '1978-12-14';
SELECT dbms_random.value(1200,2000) into v_randItem from dual; -- 将要插入的物料种数 FOR i IN 1.. v_randItem LOOP
SELECT dbms_random.value(0,10000) into v_randAddDay from dual; -- 随机天数 --482
v_randDate := to_char(to_date(v_fromDate,'YYYY-MM-DD')+v_randAddDay,'YYYYMMDD'); -- 将要插入的第一条记录的日期 SELECT dbms_random.value(2,6) into v_randItemNum from dual; -- 每种物料将要插入的记录行数
WHILE v_randItemNum >0
LOOP
SELECT dbms_random.value(0,482) into v_randPerDay from dual;
SELECT dbms_random.value(100,999) into v_randNum from dual;
v_randDate := to_char(to_date(v_randDate,'YYYY-MM-DD')+v_randPerDay,'YYYYMMDD');
INSERT INTO item_tb(itemcode, cdate, amount) VALUES(lpad(to_char(i),4,'0'),v_randDate,v_randNum);
v_randItemNum := v_randItemNum - 1;
END LOOP;
END LOOP;
COMMIT;
END;
/-- 删除序列
DROP TRIGGER item_tb_trig_autoinc;-- 删除递增触发器
DROP SEQUENCE item_tb_seq;-- 查看生成记录行总数
SELECT count(*) FROM item_tb; COUNT(*)
----------
6977-- 查看生成物料种数
SELECT count(distinct itemcode) item_ctg FROM item_tb;
ITEM_CTG
----------
1729-------------------------------------------------------------------------------------------------------------------------
-- 楼主的需求:SELECT itemcode, cdate, amount
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY t1.itemcode ORDER BY t1.itemcode) rcn, t1.itemcode, t1.cdate, t1.amount
FROM item_tb t1
WHERE EXISTS ( SELECT 1 FROM (
SELECT itemcode FROM
(SELECT distinct itemcode FROM item_tb ORDER BY sys_guid()) t2
WHERE rownum<=1000 ) t3
WHERE t3.itemcode=t1.itemcode )
) t4
WHERE t4.rcn <= 3;
-- 楼主的需求:SELECT ROW_NUMBER() OVER (ORDER BY t4.itemcode) rcn, t4.itemcode, t4.cdate, t4.amount
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY t1.itemcode ORDER BY t1.itemcode) rcn, t1.itemcode, t1.cdate, t1.amount
FROM item_tb t1
WHERE EXISTS ( SELECT 1 FROM (
SELECT itemcode FROM
(SELECT distinct itemcode FROM item_tb ORDER BY sys_guid()) t2
WHERE rownum<=1000 ) t3
WHERE t3.itemcode=t1.itemcode )
) t4
WHERE t4.rcn <= 3;
-- 楼主的需求:(呵呵:每一种物料记录行都随机)
-- 你想怎么随机就可以怎么随机------我随你怎么招!SELECT ROW_NUMBER() OVER (ORDER BY t4.itemcode) rcn, t4.itemcode, t4.cdate, t4.amount
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY t1.itemcode ORDER BY sys_guid()) rcn, t1.itemcode, t1.cdate, t1.amount
FROM item_tb t1
WHERE EXISTS ( SELECT 1 FROM (
SELECT itemcode FROM
(SELECT distinct itemcode FROM item_tb ORDER BY sys_guid()) t2
WHERE rownum<=1000 ) t3
WHERE t3.itemcode=t1.itemcode )
) t4
WHERE t4.rcn <= 3;
-- 要“随机”的话,不用数据库的专用函数几乎是不可能的!
-- 不过你可以在所有数据库中创建一个名称相同的属于你的“随机函数”,然后你用这个SQL语句的时候,调用这个你自己创建的通用的“随机函数”就OK了!-- 还有:不过,ROW NUMBER在SQL Server和Oracle上面基本相同,在其他数据库上就不知道啦...-- 呵呵:自己想办法吧......
建议你再搞个sql server,my sql,要不还有db2
估计也就这么几种常用的数据库了
不用SQL 99 啦SQL SERVER 2005和ORACLE和DB2都支持 Row_number和DENSE_RANK函数SELECT
*
FROM
(
select
HANR002019 --商品编号
,HANR002001--日期
,HANR002003--自动编号
,HANR002006--行编号
,
(
Row_number()
over
(
partition by HANR002019
ORDER BY
HANR002019
,HANR002001 desc
,HANR002003 desc
,HANR002006 desc
)
) RN3 --同一商品的编号
,
(
DENSE_RANK()
over
(
ORDER BY
HANR002019
)
) RN1000 --不同商品的编号
from
HAN07R002TORIHIKIM
) A
WHERE
RN3<=3
AND
RN1000<=10
大家觉得怎么样?
select * from tab a
where (select count(1) from tab b where a.type=b.type and a.id>=b.id)<=3