select a.* from table as a inner join (Select 科目类型,max(借方) as 借方 from table group by 科目类型) as b on a.科目类型=b.科目类型 and a.借方=b.借方
select * from 表 a where not exists (select * from 表 where 科目类型=a.科目类型 and 借方>a.借方)
select b.* from table as b inner join (select 科目类型,max(借方) from table group by 科目类型 ) as a on b.科目类型 = a.科目类型 and a.借方=b.借方
以上重复 200401 经费支出—会议费 100 200401 经费支出—差旅费 120 200401 经费支出—会议费 300 (可以重复)一样的select * from 表 a where not exists (select * from 表 where 科目类型=a.科目类型 and 借方>a.借方)
select 月份,max(月合计金额) from tablename group by 月份
那么如何把求(月合计金额)的SQL语句与 select 月份,max(月合计金额) from tablename group by 月份 嵌套起来呢?
select 科目类型,月份,月合计金额 from your_table a where 月合计金额=(select Max(月合计金额) from your_table where 科目类型=a.科目类型 group by 科目类型)
select a.month(日期),a.科目类型,a.借方 from TableName a where a.借方 > (select b.借方 from TableName b) group by 科目类型
刚才有误! 特此更正!! select a.month(日期),a.科目类型,a.借方 from TableName a where a.借方 > (select b.借方 from TableName b where a.科目类型 = b.科目类型) group by 科目类型
更正: select 科目类型,月份,max(月合计金额) from tablename group by 科目类型,月份 你要是想看到合计的话,可以再用下面的语句: select 科目类型,月份,sum(max(月合计金额)) from tablename group by 科目类型,月份 我记得还有一个关键字能够直接起到第二个语句的作用,但是,我忘记应该怎么写了!不好意思! 你可以自己建立一张表,用上面的语句归集到的数据插入进去,然后,在用第二个语句将总计更新上去!这样就麻烦点了!
select * from (SELECT 科目 as km, right(日期,2) as rq, sum(借方) as hj FROM 凭证 WHERE 科目1='经费支出' GROUP BY 科目, right(日期,2)) tbTemp where not exists ( select 1 from (SELECT 科目 as km, right(日期,2) as rq, sum(借方) as hj FROM 凭证 WHERE 科目1='经费支出' GROUP BY 科目, right(日期,2)) tbTemp1 where tbTemp.km=tbTemp1.km and tbTemp.hj<tbTemp1.hj)
日期 科目类型 借方 select max(日期),b.科目类型,b.借方 from table a inner join (select 科目类型,max(借方) from table group by 科目类型 ) b on a.科目类型=b.科目类型 and a.借方=b.借方 group by b.科目类型,b.借方
inner join
(Select 科目类型,max(借方) as 借方
from table
group by 科目类型) as b
on a.科目类型=b.科目类型 and a.借方=b.借方
from table as b
inner join
(select 科目类型,max(借方) from table group by 科目类型 ) as a
on b.科目类型 = a.科目类型 and a.借方=b.借方
200401 经费支出—会议费 100
200401 经费支出—差旅费 120
200401 经费支出—会议费 300 (可以重复)一样的select * from 表 a where not exists (select * from 表 where 科目类型=a.科目类型 and 借方>a.借方)
select 月份,max(月合计金额) from tablename group by 月份
嵌套起来呢?
from your_table a
where 月合计金额=(select Max(月合计金额) from your_table where 科目类型=a.科目类型 group by 科目类型)
from TableName a
where a.借方 > (select b.借方
from TableName b)
group by 科目类型
特此更正!!
select a.month(日期),a.科目类型,a.借方
from TableName a
where a.借方 > (select b.借方
from TableName b
where a.科目类型 = b.科目类型)
group by 科目类型
select 科目类型,月份,max(月合计金额) from tablename group by 科目类型,月份
你要是想看到合计的话,可以再用下面的语句:
select 科目类型,月份,sum(max(月合计金额)) from tablename group by 科目类型,月份
我记得还有一个关键字能够直接起到第二个语句的作用,但是,我忘记应该怎么写了!不好意思!
你可以自己建立一张表,用上面的语句归集到的数据插入进去,然后,在用第二个语句将总计更新上去!这样就麻烦点了!
from
(SELECT 科目 as km, right(日期,2) as rq, sum(借方) as hj
FROM 凭证
WHERE 科目1='经费支出'
GROUP BY 科目, right(日期,2)) tbTemp
where not exists (
select 1 from
(SELECT 科目 as km, right(日期,2) as rq, sum(借方) as hj
FROM 凭证
WHERE 科目1='经费支出'
GROUP BY 科目, right(日期,2)) tbTemp1
where tbTemp.km=tbTemp1.km
and tbTemp.hj<tbTemp1.hj)
select max(日期),b.科目类型,b.借方
from table a
inner join (select 科目类型,max(借方)
from table
group by 科目类型 ) b
on a.科目类型=b.科目类型 and a.借方=b.借方
group by b.科目类型,b.借方