|
Как сложить поля в таблицах
#38176809
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
94, если Вам для работы это нужно, то вот:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
#define CRLF CHR(13)+CHR(10)
#define True .t.
#define False .t.
set talk off
set safety off
close data all
nTableCount=3
***************************
SELECT 0
create table t1 (c1 C (10),c2 C(10),c3 C(10),c4 C(10))
insert into t1 value([],[Иванов],[Петров],[Сидоров])
insert into t1 value([математика],[1],[2],[3])
insert into t1 value([русский],[10],[20],[30])
insert into t1 value([литература],[10],[20],[30])
insert into t1 value([химия],[2],[20],[30])
use
SELECT 0
create table t2 (c1 C (10),c2 C(10),c3 C(10),c4 C(10))
insert into t2 value([],[Петров],[Сидоров],[Иванов])
insert into t2 value([химия], [2],[20],[30])
insert into t2 value([литература], [10],[20],[30])
insert into t2 value([математика], [1],[2],[3])
insert into t2 value([русский], [10],[20],[30])
USE
SELECT 0
create table t3 (c1 C (10),c2 C(10),c3 C(10),c4 C(10))
insert into t3 value([],[Отличник],[Сидоров],[Казлов])
insert into t3 value([математика],[1],[2],[3])
insert into t3 value([немецкий],[10],[20],[30])
USE
*************************** пример t1-t3. заменить на реальные имена таблиц
FOR nCounter=1 TO nTableCount
USE [t]+TRANSFORM(nCounter) IN 0
next
f=FIELD(1)
com = ""
FOR nCounter=1 TO nTableCount
com = com + IIF(EMPTY(com),"","UNION ")+" SELECT DISTINCT &F Name FROM t"+TRANSFORM(nCounter)+IIF(EMPTY(com)," INTO CURSOR PREDMET","")+" WHERE !EMPTY(&F) "
NEXT
&com
SELECT t1
COPY STRUCTURE exte TO tmp
USE tmp IN 0
com=[]
for nCounter=1 to nTableCount
com=com+"go top in t"+TRANSFORM(nCounter)+CRLF
NEXT
for nCounter=1 to nTableCount
com = com + IIF(nCounter>1,[ UNION ],[])+" SELECT EVALUATE([t"+TRANSFORM(nCounter)+".]+field_name) as FIO FROM tmp "+IIF(nCounter=1,[ INTO cursor STUDENT ],[])
NEXT
EXECSCRIPT(com)
SELECT student
INDEX on FIO TAG FIO
SELECT PREDMET
INDEX on Name TAG Name
SELECT 0
CREATE TABLE rez (PREDMET C(10),STUDENT C(10),KOLVO N(10))
FOR nCounter=1 TO nTableCount
SELECT ([t]+TRANSFORM(nCounter))
GO top
SCATTER TO stud
SKIP
SCAN WHILE true
SCATTER TO pr
RowName=[p]+IIF(SEEK(pr(1),[PREDMET]),TRANSFORM(RECNO('predmet')),[0])
FOR nC = 2 TO fcount()
ColName = [s]+IIF(SEEK(stud(nC),[STUDENT]),TRANSFORM(RECNO([student])),[0])
INSERT INTO rez VALUES (RowName,ColName,VAL(pr(nC)))
NEXT
ENDSCAN
NEXT
SELECT rez
SELECT predmet,student,SUM(kolvo) as kolvo FROM rez INTO CURSOR rezult GROUP BY 1,2
SELECT rezult
FOR nCounter=1 TO nTableCount
USE IN ('t'+TRANSFORM(nCounter))
NEXT
USE IN Rez
SELECT Rezult
DO (_GENXTAB) WITH 'cross',,,,1,2,3,,,
SELECT tmp
GO top
SCATTER NAME rec
DO while RECCOUNT()<RECCOUNT([student])
APPEND BLANK
GATHER NAME rec
ENDDO
SCAN
replace field_name WITH IIF(RECNO()=1,[PREDM_FIO],[FIO]+TRANSFORM(RECNO()))
ENDSCAN
replace ALL field_len WITH 10,field_dec WITH 0,field_type WITH [C]
SELECT 0
CREATE rez FROM tmp
SELECT * FROM student INTO ARRAY st
SELECT rez
APPEND blank
gather FROM st
SELECT cross
SCAN
SCATTER TO pr
GO VAL(subs(pr(1),2)) IN predmet
pr(1)=predmet.name
FOR nCounter=2 TO ALEN(pr)
pr(nCounter)=STR(pr(nCounter))
NEXT
INSERT INTO rez FROM ARRAY pr
ENDSCAN
SELECT rez
GO top
BROWSE nowa
Если что-то учебное, то, конечно, фтопку, ибо бред. Зато не зависит от порядка предметов/ФИО в таблицах, лишь бы полей в них было менее 255 |
|
|
