T
tseug
Unregistered / Unconfirmed
GUEST, unregistred user!
我用了一个中间表完成, 大家还有什么高见
CREATE TABLE "TEST1"
(
"A" INTEGER,
"B" INTEGER
);
INSERT INTO "TEST1" VALUES (1 , 10);
INSERT INTO "TEST1" VALUES (2 , 20);
INSERT INTO "TEST1" VALUES (3 , 30);
INSERT INTO "TEST1" VALUES (4 , 40);
INSERT INTO "TEST1" VALUES (5 , 50);
CREATE TABLE "TEST2"
(
"A" INTEGER,
"B1" INTEGER,
"B2" INTEGER,
"B3" INTEGER,
"B4" INTEGER,
"B5" INTEGER
);
INSERT INTO "TEST2" VALUES (1, 1 , 0 , 0 , 0 , 0);
INSERT INTO "TEST2" VALUES (2, 0 , 1 , 0 , 0 , 0);
INSERT INTO "TEST2" VALUES (3, 0 , 0 , 1 , 0 , 0);
INSERT INTO "TEST2" VALUES (4, 0 , 0 , 0 , 1 , 0);
INSERT INTO "TEST2" VALUES (5, 0 , 0 , 0 , 0 , 1);
SELECT SUM(TEST1.B*TEST2.B1),SUM(TEST1.B*TEST2.B2),SUM(TEST1.B*TEST2.B3),SUM(TEST1.B*TEST2.B4),SUM(TEST1.B*TEST2.B5)
FROM TEST1, TEST2
WHERE TEST1.A=TEST2.A;
CREATE TABLE "TEST1"
(
"A" INTEGER,
"B" INTEGER
);
INSERT INTO "TEST1" VALUES (1 , 10);
INSERT INTO "TEST1" VALUES (2 , 20);
INSERT INTO "TEST1" VALUES (3 , 30);
INSERT INTO "TEST1" VALUES (4 , 40);
INSERT INTO "TEST1" VALUES (5 , 50);
CREATE TABLE "TEST2"
(
"A" INTEGER,
"B1" INTEGER,
"B2" INTEGER,
"B3" INTEGER,
"B4" INTEGER,
"B5" INTEGER
);
INSERT INTO "TEST2" VALUES (1, 1 , 0 , 0 , 0 , 0);
INSERT INTO "TEST2" VALUES (2, 0 , 1 , 0 , 0 , 0);
INSERT INTO "TEST2" VALUES (3, 0 , 0 , 1 , 0 , 0);
INSERT INTO "TEST2" VALUES (4, 0 , 0 , 0 , 1 , 0);
INSERT INTO "TEST2" VALUES (5, 0 , 0 , 0 , 0 , 1);
SELECT SUM(TEST1.B*TEST2.B1),SUM(TEST1.B*TEST2.B2),SUM(TEST1.B*TEST2.B3),SUM(TEST1.B*TEST2.B4),SUM(TEST1.B*TEST2.B5)
FROM TEST1, TEST2
WHERE TEST1.A=TEST2.A;