Re: SQL

Mon, 24 Apr 2006 01:34:01 -0700 

Hello, Konstantin!
You wrote  on Mon, 24 Apr 2006 11:24:45 +0400:


 KRB> îÅ ÐÏÄÅÌÉÔÓÑ ÌÉ ÍÎÏÇÏÕ×ÁÖÁÅÍÙÊ ALL Ó×ÏÉÍÉ ÚÁÄÁÞËÁÍÉ ÎÁ ÔÅÍÕ: "ÞÔÏ
 KRB> ÄÏÌÖÅÎ ÚÎÁÔØ ËÁÎÄÉÄÁÔ, ÓÞÉÔÁÀÝÉÊ ÓÅÂÑ ÓÐÅÃÉÁÌÉÓÔÏÍ ÐÏ SQL".

CREATE TABLE AGENT (
    ID        INTEGER NOT NULL,
    NAME1     VARCHAR(60)
);

CREATE TABLE GRUPS (
    ID      INTEGER NOT NULL,
    NAME    VARCHAR(100) COLLATE PXW_CYRL
);



CREATE TABLE TOVAR (
    ID          INTEGER NOT NULL,
    IDGRUP     integer;
    NAME1       VARCHAR(150) default,
    COST_ZAK    NUMERIC(15,4) default 0,   /*ÃÅÎÁ ÚÁËÕÐËÉ*/
    REAL_SKLAD  NUMERIC(15,4)              /*ËÏÌ×Ï ÎÁ ÓËÌÁÄÅ*/
);

CREATE TABLE DOCS_TYPE (
    ID           INTEGER NOT NULL,
    NAME         VARCHAR(80) COLLATE PXW_CYRL,
    OPER         INTEGER /*-1, 0, 1*/
);

insert into  DOCS_TYPE ...
1 ðÒÉÈÏÄ 1
2 òÁÓÈÏÄ -1
3 òÁÓÈÏÄ ÄÌÑ ÄÒÕÚÅÊ -1
4 ïÔÄÁÔØ ÎÁ ÒÅÁÌÉÚÁÃÉÀ -1Ü
5 ðÒÉÈÏÄ ÄÅÎÅÇ 1
6 óÞÅÔÁ-ÆÁËÔÕÒÙ 0


CREATE TABLE DOCS (
    ID            INTEGER NOT NULL,
    IDAGENTS      INTEGER NOT NULL,
    IDTIP         INTEGER NOT NULL,
    SUM_ZAK       NUMERIC(15,4),      /* äÅÎÏÒÍÁÌÉÚÁÃÉÑ */
    DATABEG       DATE default "now" NOT NULL
);


CREATE TABLE SPECDOCS (
    IDDOCS         INTEGER NOT NULL,
    IDTOVAR        INTEGER NOT NULL,
    IDGRUOP        integer;          /* äÅÎÏÒÍÁÌÉÚÁÃÉÑ */
    COST_ZAK       NUMERIC(15,4),
    KOL            NUMERIC(15,4) default  0
);

áÇÅÎÔÏ× - 300;

÷ÓÅÇÏ ÄÏËÕÍÅÎÔÏ× - 50 000 (ÚÁ ÔÒÉ ÇÏÄÁ)
óÐÅÃÉÆÉËÁÃÉÑ     - 150 000
çÒÕÐÐ - 20
ôÏ×ÁÒ - 5000  (ÐÏ 250 × ÇÒÕÐÐÅ)
ïÔÞÅÔÙ ÏÂÙÞÎÏ ÚÁ ÐÏÓÌÅÄÎÉÊ ÍÅÓÑà - Ë×ÁÒÔÁÌ


1. ðÏ ÄÁÎÎÙÍ ÇÒÕÐÐÁÍ ×ÙÄÁÔØ ÐÒÏÄÁÖÉ ÚÁ ÐÅÒÉÏÄ
=================================
îÁÚÁ×ÎÉÅ ÇÒÕÐÐÙ
  ôÏ×ÁÒ1      ËÏÌ-×Ï    ÓÕÍÍÁ    ÓÅÊÞÁÓ_ÎÁ_ÓËÌÁÄÅ
=================================


2. óÕÍÍÙ ÐÏ ×ÓÅÍ ÄÏËÕÍÅÎÔÁÍ ÚÁ ÐÅÒÉÏÄ
=================================
îÁÚ×ÁÎÉÅ ÔÉÐÁ ÄÏËÕÍÅÎÔÁ ÓÕÍÍÁ
ðÒÉÈÏÄ                  40000
òÁÓÈÏÄ                  35355
òÁÓÈÏÄ ÄÌÑ ÄÒÕÚÅÊ       345345
ïÔÄÁÔØ ÎÁ ÒÅÁÌÉÚÁÃÉÀ    345345
ðÒÉÈÏÄ ÄÅÎÅÇ            345345
óÞÅÔÁ-ÆÁËÔÕÒÙ           3453450
=================================



3. ïÓÔÁÔËÉ ÐÏ ÔÏ×ÁÒÁÍ ÎÁ ÌÀÂÏÊ ÄÅÎØ
=================================
îÁÚÁ×ÎÉÅ ÇÒÕÐÐÙ 1
    ôÏ×ÁÒ1      ËÏÌ-×Ï
    ôÏ×ÁÒ2      ËÏÌ-×Ï
    ôÏ×ÁÒ3      ËÏÌ-×Ï
îÁÚÁ×ÎÉÅ ÇÒÕÐÐÙ 3
    ôÏ×ÁÒ14      ËÏÌ-×Ï
    ôÏ×ÁÒ24      ËÏÌ-×Ï
    ôÏ×ÁÒ34      ËÏÌ-×Ï
=================================


4. ðÏ ×ÙÂÒÁÎÎÙÍ ÁÇÅÎÔÁÍ ×ÙÄÁÔØ ×ÓÅ ÄÏËÕÍÅÎÔÙ ÚÁ ÐÅÒÉÏÄ
=================================
áÇÅÎÔ1
  ðÒÉÈÏÄ 1                  ÓÕÍÍÁ
  ðÒÉÈÏÄ 2                  ÓÕÍÍÁ
  ïÔÄÁÔØ ÎÁ ÒÅÁÌÉÚÙÃÉÀ      ÓÕÍÍÁ
áÇÅÎÔ2
  ðÒÉÈÏÄ 1                  ÓÕÍÍÁ
  ðÒÉÈÏÄ 2                  ÓÕÍÍÁ
  ïÔÄÁÔØ ÎÁ ÒÅÁÌÉÚÙÃÉÀ      ÓÕÍÍÁ
=================================

5. ðÏ ×ÙÂÒÁÎÎÙÍ ÁÇÅÎÔÁÍ ×ÙÄÁÔØ ÐÒÏÄÁÖÉ ÔÏ×ÁÒÏ× ÚÁ ÐÅÒÉÏÄ
=================================
áÇÅÎÔ1
  ôÏ×ÁÒ1     ËÏÌ-×Ï  ÓÕÍÍÁ
  ôÏ×ÁÒ2     ËÏÌ-×Ï  ÓÕÍÍÁ
  ôÏ×ÁÒ3     ËÏÌ-×Ï  ÓÕÍÍÁ
áÇÅÎÔ2
  ôÏ×ÁÒ1     ËÏÌ-×Ï  ÓÕÍÍÁ
  ôÏ×ÁÒ2     ËÏÌ-×Ï  ÓÕÍÍÁ
  ôÏ×ÁÒ3     ËÏÌ-×Ï  ÓÕÍÍÁ



=================================
äÌÑ ÄÕÛÉ:
===========================================

åÓÔØ ÔÁÂÌÉÃÙ
CREATE TABLE GROUP(
    GROUP_CODE INTEGER NOT NULL,
... ÍÎÏÇÏ ÞÅÇÏ
)

CREATE TABLE GROUP_MEMBER (
    GROUP_CODE INTEGER NOT NULL,
    MEMBERS_NO SMALLINT NOT NULL,
... ÅÝÅ ÍÎÏÇÏ..
)
ÔÏ ÅÓÔØ - ÔÁÂÌÉÃÁ ÇÒÕÐÐ - ÏÄÎÏ ÚÁÐÉÓØ ÎÁ ÇÒÕÐÐÕ É ÔÁÂÌÉÃÁ ÕÞÁÓÔÎÉËÏ× ÇÒÕÐÐ -
ÏÄÎÁ ÚÁÐÉÓØ ÎÁ ÕÞÁÓÔÎÉËÁ. ëÁË ÏÄÎÉÍ ÚÁÐÒÏÓÏÍ ÎÁ IB (ÚÎÁÀ, ËÁË ÞÅÒÅÚ View É
Ô.Ä.)
ÐÏÌÕÞÉÔØ ËÏÌÉÞÅÓÔ×Ï ÇÒÕÐÐ Ó 1, 2 É Ô.Ä. ÕÞÁÓÔÎÉËÁÍÉ?



===========================================

äÌÑ ÄÕÛÉ 2:
===========================================
ÔÁÂÌÉÃÁ
CREATE TABLE AAA(
    í INTEGER NOT NULL
)
insert into AAA values(1)
insert into AAA values(2)
insert into AAA values(4)
insert into AAA values(5)
insert into AAA values(8)
insert into AAA values(9)
insert into AAA values(11)
insert into AAA values(14)
insert into AAA values(15)

ÐÏÌÕÞÉÔØ ÏÔÓÕÔÓÔ×ÕÀÝÉÅ ÚÎÁÞÅÎÉÑ (3 6 7 10 12 13)




With best regards, Sergey Nikolaenko.  E-mail: serg (ÔÕÔ ÆÉÇÎÑ) armax.ru

Ответить