代码之家 › 专栏 › 技术社区 › Kvvaradha

MySQL查询简化

aggregate-functions mysql

Kvvaradha · 技术社区 · 7 年前

我需要借助sql查询获得每个津贴的总和。现在我使用两个查询来获得结果。但我想通过运行单个查询来获得结果,以最小化时间和代码。这是我的第一个问题。

SELECT DISTINCT master.account_code, master.account_name FROM 0_chart_master AS master WHERE master.account_type IN (60,61,233)

从上面的查询中,它将显示账户代码的结果,如下所示。

1700  Payroll Expenses
1710  Rent
1720  Travel Expenses
4000  Miscellaneous

从我用来查询下面的查询的每个账户代码中。

SELECT SUM(amount) FROM 0_gl_trans WHERE account='1700' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
SELECT SUM(amount) FROM 0_gl_trans WHERE account='1710' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
SELECT SUM(amount) FROM 0_gl_trans WHERE account='1720' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
SELECT SUM(amount) FROM 0_gl_trans WHERE account='4000' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'

我需要一个简化的解决方案来解决这个困难的情况。

4 回复 | 直到 7 年前

trincot Jakube 7 年前

您可以在第一个结果的附加列中获得总计:

SELECT    master.account_code, master.account_name, COALESCE(SUM(trans.amount), 0) AS total 
FROM      0_chart_master AS master
LEFT JOIN 0_gl_trans AS trans 
       ON trans.account = master.account_code
      AND trans.tran_date BETWEEN '2017-10-31' AND '2017-11-30'
WHERE     master.account_type IN (60,61,233)
GROUP BY  master.account_code, master.account_name

Gurwinder Singh 7 年前

您只需过滤帐户并聚合:

SELECT account, sum(amount)
FROM 0_gl_trans 
WHERE account in (
    select account
    from 0_chart_master 
    where account_type IN (60,61,233)
    )
    AND tran_date >= '2017-10-31' 
    AND tran_date <= '2017-11-30'
group by account;

ScaisEdge 7 年前

你可以通过这种方式进行单选

  select master.account_code, master.account_name,
    sum( case when account='1700' then amount else 0 end ) tot_1700_payroll_Expenses,
        sum( case when account='1710' then amount else 0 end ) tot_1710_Rent,
        sum( case when account='1720' then amount else 0 end ) tot_1720_Travel_Expenses,
        sum( case when account='4000' then amount else 0 end ) tot_4000_Miscellaneous
  FROM 0_gl_trans WHERE  tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
  INNER JOIN _chart_master AS master ON master.account_code = 0_gl_trans.account 
          and  master.account_type IN (60,61,233)
  group by master.account_code, master.account_name

Harshil Doshi 7 年前

account code 你可以使用 Between...AND 日期条件。尝试以下查询:

SELECT COALESCE(SUM(amount), 0), 
       account 
FROM 0_gl_trans 
WHERE account in (SELECT DISTINCT master.account_code
                  FROM 0_chart_master AS master 
                  WHERE master.account_type IN (60,61,233)
                  )
AND tran_date between '2017-10-31' AND '2017-11-30' 
group by account;

希望有帮助!