Table a and table b, table c by keyword JID related data will be subject to a table, want to query by the month or year sum, JNo format is SX2001-001, 20 years, 01 is in the last 3 as serial number

Table a:
JID JNo
1 SX2001-001

2 SX2001-002.3 SX2001-003
4 SX2002-001

Table K:
JID Kaizhi
1
10.002, 15.00
2, 7.00
4 30.00

Table S:
JID Shouru
1
502 50
2 10
3 50
4 70

The SELECT a. No, the SUM (ISNULL (SR, 0)) AS income, the SUM (ISNULL (CB, 0)) AS costs, gross margin=SUM (ISNULL (SR, 0)) - the SUM (ISNULL (CB, 0)) FROM a
LEFT the JOIN (SELECT JID, CB=SUM (Kaizhi) FROM K GROUP BY JID) b
ON a. ID=b.J ID
LEFT the JOIN (SELECT JID, SR=SUM (Shouru) FROM S GROUP BY JID) c
ON SAN Antonio ID=a. ID
WHERE a. No LIKE 'SX2001 - %' GROUP BY a. No, CB, SR

According to each number query no problem, how to realize the query by the month or year sum? Is also a condition of a. No LIKE 'SX2001 - %' instead of a. No LIKE 'SX__01 - %' or a. No LIKE 'SX20__ - %'

CodePudding user response:

The substring (a. NO, 3, 2)='20'
The substring (a. NO, 5, 2)='01'
Like this?

CodePudding user response:

No, or the original results, one at a show

CodePudding user response:

GROUP BY also need change

CodePudding user response:

 
The CREATE TABLE # A (
JID INT, 
JNO VARCHAR (20) 
) 
The CREATE TABLE # K (
JID INT, 
KAIZHI INT 
) 
The CREATE TABLE # S (
JID INT, 
SHOURU INT 
) 
# INSERT INTO A VALUES (1, 'SX2001-001), (2,' SX2001-002), (3, 'SX2001-003), (4,' SX2002-001 ') 
INSERT INTO # K VALUES (1, 10), (2, 2), (2, 7), (4, 30) 
INSERT INTO # S VALUES (1, 50), (2, 50), (2, 10), (3, 50), (4); seven 

The SELECT a. No, the SUM (ISNULL (SR, 0)) AS income, the SUM (ISNULL (CB, 0)) AS costs, gross margin=SUM (ISNULL (SR, 0)) - the SUM (ISNULL (CB, 0)) FROM # aa 
LEFT the JOIN (SELECT JID, CB=SUM (Kaizhi) FROM # K K GROUP BY JID) b 
ON a. ID=b.J ID LEFT JOIN (SELECT JID, SR=SUM (Shouru) FROM # S S GROUP BY JID) c 
ON SAN Antonio ID=a. ID WHERE a. No LIKE 'SX2001 - %' GROUP BY a. No, CB, SR 

SELECT substring (a. NO, 3, 2), SUM (ISNULL (SR, 0)) AS income, the SUM (ISNULL (CB, 0)) AS costs, gross margin=SUM (ISNULL (SR, 0)) - the SUM (ISNULL (CB, 0)) FROM # aa 
LEFT the JOIN (SELECT JID, CB=SUM (Kaizhi) FROM # K K GROUP BY JID) b 
ON a. ID=b.J ID LEFT JOIN (SELECT JID, SR=SUM (Shouru) FROM # S S GROUP BY JID) c 
ON SAN Antonio ID=a. ID WHERE the substring (a. NO, 3, 2)='20' GROUP BY the substring (a. NO, 3, 2) 

SELECT substring (a. NO, 5, 2), SUM (ISNULL (SR, 0)) AS income, the SUM (ISNULL (CB, 0)) AS costs, gross margin=SUM (ISNULL (SR, 0)) - the SUM (ISNULL (CB, 0)) FROM # aa 
LEFT the JOIN (SELECT JID, CB=SUM (Kaizhi) FROM # K K GROUP BY JID) b 
ON a. ID=b.J ID LEFT JOIN (SELECT JID, SR=SUM (Shouru) FROM # S S GROUP BY JID) c 
ON SAN Antonio ID=a. ID WHERE the substring (a. NO, 5, 2)='01' GROUP BY the substring (a. NO, 5, 2) 

DROP TABLE # A, # # K, S 

Don't know whether you want the effect

CodePudding user response:

The select a. id number, the sum (k.k aizhi) spending in total, the sum (s.s houru) combined the from tablea a left join tablek k on a. id=k.j id left join tables on a. s id=s.j id
Group by a. id