如果是做企业级应用开发的话,后端不会写 SQL 几乎无法干活。互联网应用情况可能稍微好一点。比如很普通的财务总账 SQL:
SELECT AB.subject_code code, AB.subject_name name
, IFNULL(AB.debit_amount_init, 0) debit_amount_init
, IFNULL(AB.credit_amount_init, 0) credit_amount_init
, IFNULL(AB.debit_amount_curr, 0) debit_amount_curr
, IFNULL(AB.credit_amount_curr, 0) credit_amount_curr
, IFNULL(C.debit_amount_end, 0) debit_amount_end
, IFNULL(C.credit_amount_end, 0) credit_amount_end
, IFNULL(D.debit_amount_total, 0) debit_amount_total
, IFNULL(D.credit_amount_total, 0) credit_amount_total
FROM
(SELECT B.subject_code, B.subject_name
, A.debit_amount_init, A.credit_amount_init
, B.debit_amount_curr, B.credit_amount_curr
FROM
(SELECT subject_code
, subject_name
, SUM(debit_amount) debit_amount_init
, SUM(credit_amount) credit_amount_init
FROM accounting_document_item adi, accounting_document ad
WHERE adi.doc_id =
ad.id AND date_format(accounting_date, '%Y%m') < CONCAT(@12-YEAR@, @
12-MONTH@)
GROUP BY subject_code, subject_name) A
RIGHT JOIN
(SELECT subject_code
, subject_name
, SUM(debit_amount) debit_amount_curr
, SUM(credit_amount) credit_amount_curr
FROM accounting_document_item adi, accounting_document ad
WHERE adi.doc_id =
ad.id AND date_format(accounting_date, '%Y%m') = CONCAT(@12-YEAR@, @
12-MONTH@)
GROUP BY subject_code, subject_name) B
ON A.subject_code = B.subject_code) AB
LEFT JOIN
(SELECT subject_code
, subject_name
, SUM(debit_amount) debit_amount_end
, SUM(credit_amount) credit_amount_end
FROM accounting_document_item adi, accounting_document ad
WHERE adi.doc_id =
ad.id AND date_format(accounting_date, '%Y%m') <= CONCAT(@12-YEAR@, @
12-MONTH@)
GROUP BY subject_code, subject_name) C
ON AB.subject_code = C.subject_code
LEFT JOIN
(SELECT subject_code
, subject_name
, SUM(debit_amount) debit_amount_total
, SUM(credit_amount) credit_amount_total
FROM accounting_document_item adi, accounting_document ad
WHERE adi.doc_id =
ad.id AND date_format(accounting_date, '%Y') = @
12-YEAR@
GROUP BY subject_code, subject_name) D
ON AB.subject_code = D.subject_code