mysql - 从查询中获取所有数据 select in select union mysql

Question

我真的很困惑如何从查询中获取完整记录。

我已经尽力了，但还是得不到我想要的。

我正在使用mysql 5.5，我真的不知道该怎么办。

我将分享我的database.sql，因为我的查询连接超过5个表。

database_schema.sql

这是我之前尝试过的查询。

select id_akun, kode_akun, nama_akun, SUM(saldo_awal) as 'SaldoAwal', SUM(debet) as 'Debet', SUM(kredit) as 'Kredit', (SUM(saldo_awal) + SUM(debet) - SUM(kredit)) as 'SaldoAkhir' from (
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) as saldo_awal, b.amount as 'debet', 0 as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
    AND dt='D' AND c.trx_type IN ('1', '2')
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
    AND dt='K' AND c.trx_type IN ('1', '2')
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c, kas d
where a.id=b.account_id AND b.transaksi_kas_id=c.id AND c.kas_id=d.id
    AND dt='D' AND c.trx_type IN ('3', '4')  AND d.jenis != '3'
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c, kas d
where a.id=b.account_id AND b.transaksi_kas_id=c.id AND c.kas_id=d.id
    AND dt='K' AND c.trx_type IN ('3', '4') AND d.jenis != '3'
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
    AND dt='D' AND c.trx_type IN ('5', '6')
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
    AND dt='K' AND c.trx_type IN ('5', '6')
) fff WHERE group_account_id = '15' 
GROUP BY id_akun, kode_akun, nama_akun
ORDER BY kode_akun asc

在我运行此查询后..

我会得到这个结果。

我只得到2条记录，我需要通过ORDER BY kode_akun从表account获取所有数据

如果表帐户有 10 行，那么我的查询必须显示 10 行。

也许有人可以帮助我

例如像这样..

Answer 1

根据您的进一步描述，我认为您需要的是左连接 transaksi_kas_detail 和 transaksi_kas 表(以及使用时的 kas 表)。

请注意，您的示例数据已进一步分割详细信息，并且不确定您如何获取额外的数据。

但是像这样

SELECTid_akun, kode_akun, nama_akun, SUM(saldo_awal) as 'SaldoAwal', SUM(debet) as 'Debet', SUM(kredit) as 'Kredit', (SUM(saldo_awal) + SUM(debet) - SUM(kredit)) as 'SaldoAkhir' 
from 
(
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) as saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4')
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4')
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
) fff 
WHERE group_account_id = '15' 
GROUP BY id_akun, kode_akun, nama_akun
ORDER BY kode_akun asc

编辑 - 检查 ON 子句中的日期:-

SELECT id_akun, kode_akun, nama_akun, SUM(saldo_awal) as 'SaldoAwal', SUM(debet) as 'Debet', SUM(kredit) as 'Kredit', (SUM(saldo_awal) + SUM(debet) - SUM(kredit)) as 'SaldoAkhir' 
from 
(
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) as saldo_awal, IF(c.id IS NULL, 0, b.amount) as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', IF(c.id IS NULL, 0, b.amount) as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, IF(d.id IS NULL, 0, b.amount) as 'debet', 0 as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', IF(d.id IS NULL, 0, b.amount) as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, IF(c.id IS NULL, 0, b.amount) as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D' AND b.cek_due_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', IF(c.id IS NULL, 0, b.amount) as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K' AND b.cek_due_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
) fff 
WHERE group_account_id = '15' 
GROUP BY id_akun, kode_akun, nama_akun
ORDER BY kode_akun asc