Two table merging SQL for help.-CodePudding

A table is as follows:
Income table (A)

Date quantity
2017.1.1 2
2017.1.2 3
2017.1.3 4
2017.1.4 1
2017.2.2 10
2017.2.3 15
2017.2.4 20
2017.2.5 2
2017.2.6 5
Spending table (B)

Date quantity
2017.1.11 2
2017.1.2 12
2017.1.3 4
2017.1.4 1
2017.2.2 10
2017.2.3 15
2017.2.4 20
2017.2.5 2
2017.2.16 11
Generation schedule of payments as follows:

The number of the date of revenue expenditure income date
12 2017.1.2 2017.1.1 2
3 4 2017.1.2 2017.1.3
4 1 2017.1.3 2017.1.4
2 2017.1.11 2017.1.4 1
Subtotal 10 19
2017.2.2 10 10 2017.2.2
15 15 2017.2.3 2017.2.3
2017.2.4 20 20 2017.2.4
2 2 2017.2.5 2017.2.5
5 11 2017.2.6 2017.2.16
Subtotal 52 58
I use the select don't know how to do?
Possible income and spending time also has much less, are not necessarily aligned, such as, income is five days in a month, and spending is 20 days,
My question is the merger of two tables and statistics, the schedule of schedule of revenue and expenditure and merged into one, and to show income time, number of clients (I didn't reflect in the table), as well as spending time, number of customers (I didn't reflect in the table), and subtotal once a month,
After the merger of form is the best structure like this:

Date of income amount customer spending date number
Consult everybody,

CodePudding user response:

Trouble master!

CodePudding user response:

The SUM total

CodePudding user response:

 SELECT 'income in January, a. * 
FROM a 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
FROM a 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT 'spending in January, b. * 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT 'spending on February, a. * 
FROM A 
WHERE the SUBSTRING (DATE, 6, 2)='02' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
FROM a 
WHERE the SUBSTRING (DATE, 6, 2)='02' 
UNION ALL 
SELECT 'spending on February, b. * 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='02' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='02'

So I don't know conform to the requirements? Transverse splicing really don't know how to do

CodePudding user response:

The

reference 3 floor u012066228 response:

 SELECT 'income in January, a. * 
FROM a 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
FROM a 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT 'spending in January, b. * 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='01' 
UNION ALL 
SELECT 'spending on February, a. * 
FROM A 
WHERE the SUBSTRING (DATE, 6, 2)='02' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
FROM a 
WHERE the SUBSTRING (DATE, 6, 2)='02' 
UNION ALL 
SELECT 'spending on February, b. * 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='02' 
UNION ALL 
SELECT the 'total', ' ', the SUM (num) 
The FROM b 
WHERE the SUBSTRING (DATE, 6, 2)='02'

So I don't know conform to the requirements? Transverse splicing really don't know how to do it

I need is transverse shows that income and expenditure, and the habit of books is consistent,