下面这段PROC只能抓工厂,动态的合同还是不能抓出。(表结构不同,上面建表的字段我改过了,方便大家JOIN) 大家说下思路吧,提点意见吧……
CREATE PROCEDURE PROC_SALES_POSITION_GETMILL (
companyid integer,
ctrbookid integer)
returns (
contractbookid integer,
regionid integer,
regionname varchar(100),
millno integer,
firstivlocno integer,
millname1 varchar(100),
millname2 varchar(100),
millname3 varchar(100),
millid1 integer,
millid2 integer,
millid3 integer)
as
BEGIN contractbookid = :ctrbookid;
/* regionid = :region; */
FOR
SELECT
DISTINCT(JR.regionname),
JR.regionid
FROM JCIVLOC JL
JOIN JCREGION JR ON (JR.regionid = JL.regionid)
JOIN CTRBOOK CB ON (CB.mbrcompany = JL.mbrid)
WHERE CB.ctrbookid = :ctrbookid
AND JL.mbrid = :companyid
ORDER BY 1,2
INTO :regionname,
:regionid
DO
BEGIN
millname1 = null; /*Get Count of IVLOC*/
SELECT COUNT(JL.ivlocid)
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
INTO :millno; /*Get First Ivloc No*/
SELECT FIRST 1 JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
ORDER BY JL.longname
INTO :firstivlocno; WHILE(:millno > 0 )
DO
BEGIN
if(:millname1 IS NOT NULL)
then
begin
/*Get Mill Param1*/
/*Not first time into circle*/
SELECT
FIRST 1 JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname3
ORDER BY JL.longname
INTO
:millname1,
:millid1;
end
else
begin
/*First time into circle*/
SELECT
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
WHERE JL.ivlocid = :firstivlocno
INTO
:millname1,
:millid1;
end /*Get Mill Param2*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname1
ORDER BY JL.longname
INTO
:millname2,
:millid2; /*Get Mill Param3*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname2
ORDER BY JL.longname
INTO
:millname3,
:millid3; /*Chekc Part*/
if(:millno -3 < 0)
then
begin
millname3 = null;
end
if(:millno -2 < 0)
then
begin
millname2 = null;
end
if(:millno -1 < 0)
then
begin
millname1 = null;
end MILLNO = :millno - 3;
SUSPEND;
END
END
end
CREATE PROCEDURE PROC_SALES_POSITION_GETMILL (
companyid integer,
ctrbookid integer)
returns (
contractbookid integer,
regionid integer,
regionname varchar(100),
millno integer,
firstivlocno integer,
millname1 varchar(100),
millname2 varchar(100),
millname3 varchar(100),
millid1 integer,
millid2 integer,
millid3 integer)
as
BEGIN contractbookid = :ctrbookid;
/* regionid = :region; */
FOR
SELECT
DISTINCT(JR.regionname),
JR.regionid
FROM JCIVLOC JL
JOIN JCREGION JR ON (JR.regionid = JL.regionid)
JOIN CTRBOOK CB ON (CB.mbrcompany = JL.mbrid)
WHERE CB.ctrbookid = :ctrbookid
AND JL.mbrid = :companyid
ORDER BY 1,2
INTO :regionname,
:regionid
DO
BEGIN
millname1 = null; /*Get Count of IVLOC*/
SELECT COUNT(JL.ivlocid)
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
INTO :millno; /*Get First Ivloc No*/
SELECT FIRST 1 JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
ORDER BY JL.longname
INTO :firstivlocno; WHILE(:millno > 0 )
DO
BEGIN
if(:millname1 IS NOT NULL)
then
begin
/*Get Mill Param1*/
/*Not first time into circle*/
SELECT
FIRST 1 JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname3
ORDER BY JL.longname
INTO
:millname1,
:millid1;
end
else
begin
/*First time into circle*/
SELECT
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
WHERE JL.ivlocid = :firstivlocno
INTO
:millname1,
:millid1;
end /*Get Mill Param2*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid
AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname1
ORDER BY JL.longname
INTO
:millname2,
:millid2; /*Get Mill Param3*/
SELECT
FIRST 1
JL.longname,
JL.ivlocid
FROM JCIVLOC JL
JOIN ctrbook CB ON (CB.mbrcompany = JL.mbrid)
WHERE JL.mbrid = :companyid AND CB.ctrbookid = :ctrbookid
AND JL.regionid = :regionid
AND JL.longname > :millname2
ORDER BY JL.longname
INTO
:millname3,
:millid3; /*Chekc Part*/
if(:millno -3 < 0)
then
begin
millname3 = null;
end
if(:millno -2 < 0)
then
begin
millname2 = null;
end
if(:millno -1 < 0)
then
begin
millname1 = null;
end MILLNO = :millno - 3;
SUSPEND;
END
END
end
2 TEST2
3 TEST3
4 TEST4
5 TEST5
6 TEST6
7 TEST7
8 TEST8
9 TEST9
10 TEST10 TABLE MILLTEST: CTRID MILLID REGIONID MILLNAME 1 1 1 MILL1
2 1 1 MILL1
3 2 1 MILL2
4 2 1 MILL2
5 2 1 MILL2
6 2 1 MILL2
7 3 1 MILL3
8 3 1 MILL3
9 3 1 MILL3
10 4 1 MILL4 TABLE REGIONTEST: REGIONID REGIONNAME 1 REGION
希望得到的效果: COMMODITYID REGIONNAME MILL1 MILL2 MILL3 CTRREF1 CTRREF2 CTRREF3 COMMODITY REGION1 MILL1 MILL2 MILL3 CTR1 CTR2 CTR3
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR4 CTR5 CTR6
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR7 CTR8
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR9
COMMODITY REGION1 MILL4 MILL5 MILL6 CTR10 CTR11
COMMODITY REGION1 MILL4 MILL5 MILL6 CTR12
谢谢~
mill1,mill2,mill3都不是固定的,每一行出现3个工厂。2.
CTRREF1,CTRREF2,CTRREF3
这三个列放分别属于MILL1,MILL2,MILL3的合同。即
SELECT
FROM CTRTEST C AS CTRREF1
JOIN MILLTEST MT ON (C.CTRID = MT.CTRID)
WHERE MT.MILLNAME = 'MILL1'
作为第一个CTRREF1
CTRREF2,CTRREF3同理。
循环条件为三个工厂中合同最多的那个,
比如MILL2有10个合同,M1,M3分别7个,8个。
则MILL1,MILL2,MILL3都有10行,不过CTRREF1,CTRREF3只有7行与8行,最后为空。
CTRREF2出现10行数据。
然后同理出现MILL4,MILL5,MILL5,在新的一行。
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR4 CTR5 CTR6
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR7 CTR8
COMMODITY REGION1 MILL1 MILL2 MILL3 CTR9
COMMODITY REGION1 MILL4 MILL5 MILL6 CTR10 CTR11
COMMODITY REGION1 MILL4 MILL5 MILL6 CTR12 我问的意思是红色部分从何而来?你的CTRTEST表和MILLTEST表中都不存在这样的数据
即CTR10,CTR12属于MILL4,CTR11属于MILL6,
MILL5没有合同,所以两行为空。
但是MILL4,MILL5,MILL6一样出现2行……
1.表结构与测试数据:
create table tb(...)
insert tb ...
2.相关算法:
主要描述一下各表间的关系,以及是怎么得到你想要的结果的.
3.最终得到的结果:
贴出最终得到的结果.