the current SQL you have is fine you should not change this. If you need to split and have both entries separated you can do this:

WITH W_RESULT AS (
   -- your current query goes here....
)
SELECT acc_name, dr_sum, 0 AS cr_sum
FROM W_RESULT
WHERE dr_sum <> 0
UNION
SELECT acc_name, 0 AS dr_sum, cr_sum
FROM W_RESULT
WHERE cr_sum <> 0

<>根据最新解决办法,包括讨论表

WITH W_RESULT AS (
   -- your current query goes here....
)
SELECT acc_name, dr_sum, 0 AS cr_sum
FROM W_RESULT
WHERE dr_sum <> 0
UNION
SELECT acc_name, 0 AS dr_sum, cr_sum
FROM W_RESULT
WHERE cr_sum <> 0
UNION ALL
SELECT  Total  AS acc_name, SUM(dr_sum), SUM(cr_sum)
FROM W_RESULT

previous discussion...

您可在问询表中加上总金额:sum(j.dr_amt + j.cr_amt) 和t_sum;如果你需要,作为单独条目,则你可以利用额外栏和UNION栏扩大上述解决办法。 甚至可能更简单地这样做:

WITH W_RESULT AS (
   -- your current query goes here....
)
SELECT acc_name, dr_sum, 0 AS cr_sum, 0 AS t_sum
FROM W_RESULT
WHERE dr_sum <> 0
UNION
SELECT acc_name, 0 AS dr_sum, cr_sum, 0 AS t_sum
FROM W_RESULT
WHERE cr_sum <> 0
UNION
SELECT acc_name, 0 AS dr_sum, 0 AS cr_sum, dr_sum + cr_sum AS t_sum
FROM W_RESULT

进口<>

the current query from the question I m referring to is the first one.
The "final query" at the end of the question is a bit strange.

它与它一样。

• sum of "Cash in hand 3000" + "Vehicles 5000" = 8000 as dr_sum
• sum of "Cash in hand 5000" + "Share Capital 3000" = 8000 as cr_sum

我假设的是,有人要求一笔总款像这样的东西。

• sum of "Cash in hand 3000" + "Cash in hand 5000" = 8000 as t_sum
• sum of "Share Capital 3000" = 3000 as t_sum
• sum of "Vehicles 5000" = 5000 as t_sum

请补充一点意见,即正确。

;with cte as
(
  select
    a.acc_name,
    case n.n when 1 then sum(j.dr_amt) else 0 end as dr_sum,
    case n.n when 2 then sum(j.cr_amt) else 0 end as cr_sum
  from journal_voucher_details_mcg as j 
    inner join  acc_head_mcg as a
      on a.acc_code = j.acc_code 
    inner join journal_voucher_mcg as jv
      on jv.jv_no = j.jv_no
    cross join (select 1 union all select 2) as n(n)
  where jv.jv_date =  2011-04-08 
  group by a.acc_name, n.n   
)
select
  acc_name,
  dr_sum,
  cr_sum
from cte
where not (dr_sum = 0 and cr_sum = 0)
union all
select 
   Total ,
  sum(dr_sum),
  sum(cr_sum)
from cte

http://www.ohchr.org。

WITH groups AS (
  SELECT
    a.acc_name,
    dr_sum = sum(j.dr_amt),
    cr_sum = sum(j.cr_amt)
  FROM journal_voucher_details_mcg AS j
    INNER JOIN acc_head_mcg AS a ON a.acc_code = j.acc_code 
    INNER JOIN journal_voucher_mcg AS jv ON jv.jv_no = j.jv_no
  WHERE jv.jv_date =  2011-04-08 
  GROUP BY j.acc_code, CASE WHEN dr_amt > 0 THEN 1 ELSE 2 END
)

SELECT
  acc_name,
  dr_sum,
  cr_sum
FROM groups

UNION ALL

SELECT
   Total ,
  SUM(dr_sum),
  SUM(cr_sum)
FROM groups

在2008年服务器(2008年新特点)中,您可在<条码>上添加。 按

SELECT 
        a.acc_name, sum(j.dr_amt) AS dr_sum, sum(j.cr_amt) AS cr_sum 
FROM        
        journal_voucher_details_mcg AS j 
INNER JOIN  
        acc_head_mcg AS a ON a.acc_code = j.acc_code 
INNER JOIN  
        journal_voucher_mcg AS jv ON jv.jv_no = j.jv_no
WHERE       
        jv.jv_date =  2011-04-08 
GROUP BY    
        j.acc_code WITH ROLLUP

在此情况下,请你增加一行,其中<代码>acc_code为NUL,符合所有账户的总额。

因此,你们的新产出应当看一看:

acc_name        dr_sum  cr_sum  

Cash in hand    3000      0
Cash in hand    0         5000
Share Capital   0         3000
Vehicles        5000      0
NULL            8000      8000   <-- that s the line with the totals