There are three tables as follows, I how to write SQL to query to the second floor of the results

CodePudding user response:

CodePudding user response:

What to watch? Did not say

CodePudding user response:

Where is the second floor?

CodePudding user response:

In case of I
1: field1:='materials';
2: field1:='mechanical fee;
3: field1:='labor;
end;

Sql1. Text:='select date,' + field1 + ' ' 'the from table1';
Sql1. Open;

CodePudding user response:

I want to achieve the effect of this?


Because you are not listed the field name, hope that is two fields ADate and Afee, as for the cost of raw materials, labor, etc., is a category, I think it should be define fields for AType
The query of the first table should be

 SELECT ADate, AFee FROM Table1 WHERE AType='materials'; 
The SELECT ADate, AFee FROM Table1 WHERE AType='machine fee; 
The SELECT ADate, AFee FROM Table1 WHERE AType='labor'; 

Then on this basis to achieve the result of the second table, have to be combined,

 SELECT ADate, SUM (AFee1) AS materials, the SUM (AFee2) AS machinery, the SUM (AFee3) AS labor FROM 
(
SELECT ADate AFee AS AFee1, 0 AS AFee2, 0 AS AFee3 FROM Table1 WHERE AType='materials' 
UNION ALL 
The SELECT ADate, 0 AS AFee1, AFee AS AFee2, 0 AS AFee3 FROM Table1 WHERE AType='machine fee' 
UNION ALL 
The SELECT ADate, 0 AS AFee1, 0 AS AFee2, AFee AS AFee3 FROM Table1 WHERE AType='labor' 
) A 
GROUP BY ADate 

CodePudding user response:

As the date for the index table combination is good,

CodePudding user response:

Date, the create view t5 as select a. NVL (a. materials, 0) c1, NVL (b. machine fee) c2 from t1 a full join t2 on a. b date=b. date;
The create view t6 as select a. date, a.c 1, 2, a.c NVL (b. labor) c3 from t5 a full join t3 on a. b date=b. date;

Select date, sum (c1) + sum (c2) + sum (c3) t7 has the from t6 group by date;

CodePudding user response:

Two table joins, can be detected

CodePudding user response:

Post has not yet been closed two years ago?
Where is the problem is on the second floor?

CodePudding user response:

SELECT dbo. A. date, dbo. A. materials, dbo. B. machine fee, dbo. C. labor
The FROM dbo. A INNER JOIN
Dbo. B ON dbo. A. date date=dbo. B. INNER JOIN
Dbo. C ON dbo. A. date=dbo. C. date

CodePudding user response:

 
SELECT a date, 
ISNULL (b.s CLF, 0) AS CLF2, 
ISNULL (c.s. JXF, 0) AS JXF2, 
ISNULL (d.s RGF, 0) AS RGF2 
The FROM (
SELECT date FROM tbl_CLF UNION 
SELECT date FROM tbl_JXF UNION 
SELECT date FROM tbl_RGF 
) a 
LEFT the JOIN (
SELECT date, SUM (materials) AS sCLF FROM tbl_CLF GROUP BY date 
B) ON a. 
 date=b.LEFT the JOIN (
SELECT date, SUM (mechanical) AS sJXF FROM tbl_JXF GROUP BY date 
Date date) ON a. c=c. 
LEFT the JOIN (
SELECT date, SUM (labor) AS sRGF FROM tbl_RGF GROUP BY date 
) d ON a. date=d. date 
Date of the ORDER BY a. 

CodePudding user response:

Three years ago to the post the

CodePudding user response:

Post has not yet been closed two years ago?
Where is the problem is on the second floor?