금액 열과 차변 대변 열만 있는 테이블에서 평가판 잔액 표시를 위한 전표 선택

금액 열과 차변 대변 열만 있는 테이블에서 평가판 잔액 표시를 위한 전표 선택

MySQL/MariaDB의 SQL 쿼리를 사용하여 데이터 테이블을 채워 보고서에 대한 평가판으로 표시하려고 합니다.

제 DB 엔진은 MariaDB 10.4.19입니다.

이 경우..2개의 테이블이 있습니다.

1. 저널(트랜잭션 포함) 및

| id | date       | debit | credit| amount    |
|----|------------|-------|-------|-----------|
| 1  | 2021-09-01 | 8     | 2     | 5000.000  |
| 6  | 2021-09-22 | 22    | 17    | 4750.000  |
| 8  | 2021-09-05 | 8     | 3     | 1485.000  |
| 9  | 2021-08-10 | 8     | 6     | 108.000   |
| 10 | 2021-07-07 | 8     | 23    | 98756.000 |

2. 레지스터 계정 이름

| id | name                              | desc                              | status |
|----|-----------------------------------|-----------------------------------|--------|
| 1  | Assets                            | Current Assets                    | 1      |
| 2  | Stockholders equity               | Stockholders or Owners equity     | 1      |
| 3  | Liability                         | Liabilities related accounts      | 1      |
| 4  | Operating Revenues                | Operating Revenues                | 1      |
| 5  | Operating Expenses                | Operating Expenses                | 1      |
| 6  | Non-operating revenues and gains  | Non-operating revenues and gains  | 1      |
| 7  | Non-operating expenses and losses | Non-operating expenses and losses | 1      |
| 8  | Cash                              | For cash transaction              | 1      |

이제 프로그램에서 계정(레지스터에서 입력)을 선택하고 제출하면 해당 계정의 ID가 전송됩니다. 해당 계정 ID를 사용하여 해당 계정 ID에 대한 모든 트랜잭션을 채웁니다.

예를 들어, 만약 내가 현금 계좌를 선택했다면, 그것은 8을 ID로 보낼 것입니다.

지금이다

1. SQL 쿼리는 저널의 날짜 및 금액과 원장의 계정 이름(거래에서 ID 8에 해당하는 계정 이름)을 입력해야 합니다.예를 들어 2일 경우 "주주 지분"을 이름으로 반환합니다.
2. 주어진 ID 8이 차변에 있는 경우 금액은 신용 열에 있어야 하며, 주어진 ID 8이 신용에 있는 경우 금액은 차변 열에 있어야 합니다.

위와 같이 프로그램에서 계정 ID가 8(즉, "현금")로 전달되면 SQL SELECT 쿼리 결과의 원하는 출력은 다음과 같아야 합니다.

| Date       | Account                          |    Debit |    Credit |
|------------|----------------------------------|---------:|----------:|
| 2021-09-01 | Stockholders equity              |     0.00 |   5000.00 |
| 2021-09-05 | Liability                        |     0.00 |   1485.00 |
| 2021-08-10 | Non-operating revenues and gains |     0.00 |    108.00 |
| 2021-07-07 | Land                             |     0.00 |  98756.00 |
| 2021-02-25 | Land                             | 21564.00 |      0.00 |
| 2021-01-19 | Vehicles                         |     0.00 |  23132.00 |
| 2020-05-19 | Buildings Asset                  |   465.00 |      0.00 |
| 2019-09-01 | Non-operating revenues and gains |   315.00 |      0.00 |
| 2019-05-23 | Land                             |   346.00 |      0.00 |
| 2018-04-12 | Cash                             |     0.00 |    697.00 |
| 2017-05-15 | Non-operating revenues and gains |     0.00 |    999.00 |
| 2018-06-18 | Operating Revenues               |     0.00 |    496.00 |
| 2018-06-23 | Liability                        |     0.00 |    426.00 |
| 2019-12-12 | Assets                           |     0.00 |   1684.00 |
| 2018-07-15 | Land                             |     0.00 |   1649.00 |
| 2018-07-22 | Land                             |  3666.00 |      0.00 |
| 2018-05-14 | Non-operating revenues and gains |     0.00 |    489.00 |
| 2018-09-16 | Equipment                        |   692.00 |      0.00 |
| 2021-04-18 | Non-operating revenues and gains |  4986.00 |      0.00 |
| 2020-04-19 | Land                             |  4956.00 |      0.00 |
| 2019-03-15 | Buildings Asset                  |     0.00 |   4988.00 |
| 2019-12-04 | Inventory                        |     0.00 |   7946.00 |
| 2019-08-25 | Stockholders equity              |     0.00 |  19449.00 |
|            |                                  |          |           |
|            | Total                            | 36990.00 | 167304.00 |
|            | Balance                          |          | 130314.00 |

여기 SQLFiddle http://sqlfiddle.com/ #!9/fe2a00/2의 샘플 데이터가 있습니다.

조인 및/또는 유니언을 사용하여 이 결과 테이블을 SQL 쿼리로 채우는 방법에 대한 아이디어를 주시겠습니까? 아니면 다른 방법을 사용해야 합니까?

답변을 도와주세요!

없이.Total:

SELECT journal.date `Date`,
       ledger.name Account,
       CASE WHEN journal.dracc = 8 
            THEN 0.000
            ELSE amount
            END Debit,
       CASE WHEN journal.dracc = 8
            THEN amount
            ELSE 0.000
            END Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc));

와 함께Total:

SELECT CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(journal.date) 
            END `Date`,
       CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(ledger.name) 
            ELSE 'Total'
            END Account,
       SUM(CASE WHEN journal.dracc = 8 
                THEN 0
                ELSE amount
                END) Debit,
       SUM(CASE WHEN journal.dracc = 8
                THEN amount
                ELSE 0
                END) Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc))
GROUP BY journal.id WITH ROLLUP;

와 함께Total그리고.Balance:

WITH 
cte AS (
SELECT CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(journal.date) 
            END `Date`,
       CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(ledger.name) 
            ELSE 'Total'
            END Account,
       SUM(CASE WHEN journal.dracc = 8 
                THEN 0
                ELSE amount
                END) Debit,
       SUM(CASE WHEN journal.dracc = 8
                THEN amount
                ELSE 0
                END) Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc))
GROUP BY journal.id WITH ROLLUP
)
SELECT * 
FROM cte 
UNION ALL
SELECT NULL, 'Balance', NULL, Credit - Debit
FROM cte
WHERE `Date` IS NULL;

https://dbfiddle.uk/ ?rdbms=dll_8.0&dll=794f87b054848b1584a6c8dd2dd5d47c

MySQL 버전이 5.x인 경우:

• 교체하다WHEN NOT GROUPING(journal.id)와 함께WHEN journal.id IS NOT NULL
• CTE를 하위 쿼리로 변환

언급URL : https://stackoverflow.com/questions/71341380/select-statement-for-trial-balance-display-from-table-with-only-one-amount-colum