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
|
-- Script a exécuter via HeidiSQL pour créer les tables
CREATE TABLE `tablea` (
`Aid` int(11) NOT NULL AUTO_INCREMENT,
`A1` varchar(100) DEFAULT NULL,
`A2` int(11) DEFAULT NULL,
PRIMARY KEY (`Aid`)
) ENGINE=InnoDB ;
CREATE TABLE `tableb` (
`Bid` int(11) NOT NULL AUTO_INCREMENT,
`Aid` int(11) DEFAULT NULL,
`B1` varchar(100) DEFAULT NULL,
`B2` int(11) DEFAULT NULL,
PRIMARY KEY (`Bid`)
) ENGINE=InnoDB ;
INSERT INTO tablea (Aid, A1, A2) VALUES(1, 'RA1', 1);
INSERT INTO tablea (Aid, A1, A2) VALUES(2, 'RA2', 2);
INSERT INTO tablea (Aid, A1, A2) VALUES(3, 'RA3', 3);
INSERT INTO tablea (Aid, A1, A2) VALUES(4, 'RA4', 4);
INSERT INTO tablea (Aid, A1, A2) VALUES(5, 'RA5', 5);
INSERT INTO tablea (Aid, A1, A2) VALUES(6, 'RA6', 6);
INSERT INTO tablea (Aid, A1, A2) VALUES(7, 'RA7', 7);
INSERT INTO tablea (Aid, A1, A2) VALUES(8, 'RA8', 8);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(1, 1, 'RB1', 1);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(2, 1, 'RB1', 2);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(3, 1, 'RB3', 3);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(4, 2, 'RB4', 4);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(5, 4, 'RB5', 5);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(6, 4, 'RB5', 6);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(7, 4, 'RB5', 7);
-- A placer dans un TFDQuery
WITH t_result as (
SELECT
B2,
COALESCE((
SELECT SUM(tablea.A2 * tablea.A2)
FROM tablea
WHERE tablea.Aid = tableb.Aid
),0) AS col1
FROM tableb
)
SELECT *,
(CASE WHEN (B2 - col1)>=0 THEN (B2 - col1) ELSE 0 END) AS col5
FROM t_result ; |
Partager