本文介绍了我需要在mysql中查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张桌子
I am having a table ba
date baname bal 2013-12-23 ib 10000 2013-12-23 ib 9500 2013-12-23 ib 9000 2013-12-23 hb 6726 2013-12-23 hb 8354.40 2013-12-23 ib 9500现在我需要为每个baname获得最后一帧的价值 ie,输出就像是
Now i need to get the value of last bal for each baname i.e., Output is like
date baname bal 2013-12-23 hb 8354.40 2013-12-23 ib 9500建议解决方案
Suggest a solution
推荐答案请先阅读我对该问题的评论。 你无法删除你的问题是否存在一个或多个答案。请阅读 CP QA规则 [ ^ ]。 MS SQL服务器有一些有趣的解决方案,其中使用了 ROW_NUMBER 函数。不幸的是,MySQL不提供 ROW_NUMBER 函数,但有希望;) - 使用变量。 试试这个: Please, read my comment to the question first. You can't remove your question if there exists one or more answer. Please, read CP QA rules[^]. There were interesting solutions for MS SQL server where ROW_NUMBER function was used. Unfortunately, MySQL does not provides ROW_NUMBER function, but there is a hope ;) - using variables. Try this: SELECT date, ( CASE baname WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 AND @curType := baname END) +1 AS RowNo, baname, bal FROM tmp t2, (SELECT @curRow := 0, @curType := '') t1 WHERE `date` = '2013-12-23' ORDER BY baname
以上查询返回:
Above query returns:
| DATE | ROWNO | BANAME | BAL | |---------------------------------|-------|--------|--------| | December, 23 2013 00:00:00+0000 | 1 | hb | 6726 | | December, 23 2013 00:00:00+0000 | 2 | hb | 8354.4 | | December, 23 2013 00:00:00+0000 | 1 | ib | 10000 | | December, 23 2013 00:00:00+0000 | 2 | ib | 9500 | | December, 23 2013 00:00:00+0000 | 3 | ib | 9000 | | December, 23 2013 00:00:00+0000 | 4 | ib | 9500 |您现在需要做的就是选择 baname 最高 RowNo 。提示:使用 MAX()功能;) SQLFiddle示例 [ ^ ] 它应该产生预期的结果集:
All what you need to do now, is to select baname with the highest RowNo. Tip: use MAX() function ;) SQLFiddle example[^] It should produce expected result set:
SELECT date, MAX(RowNo), baname, bal FROM ( SELECT date, ( CASE baname WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 AND @curType := baname END) +1 AS RowNo, baname, bal FROM tmp t2, (SELECT @curRow := 0, @curType := '') t1 WHERE `date` = '2013-12-23' ORDER BY baname ) T GROUP BY T.baname[/ EDIT]
[/EDIT]
HI Siva, 这可能效率不高解决方案。 但是这会给你预期的结果.. HI Siva, this may not be the efficient solution. but this will gives you the expected result.. declare @tbloutput table ( datee varchar(20), baname varchar(15) , bal int , ignore int ) declare @baname varchar(15) declare cur cursor for select distinct baname from test open cur fetch next from cur into @baname while @@FETCH_STATUS =0 begin insert into @tbloutput select top 1 datee, baname, bal, ROW_NUMBER() over ( order by datee) as Number from test where baname = @baname order by Number desc fetch next from cur into @baname end close cur deallocate cur select datee,baname,bal from @tbloutput
选择a.bname,(从银行表b中选择b.bal,其中a.baname = b.baname的顺序由b.date desc limit 1)从banktable a group by a.baname Select a.bname, (select b.bal from banktable b where a.baname=b.baname order by b.date desc limit 1) from banktable a group by a.baname
更多推荐
我需要在mysql中查询
发布评论