我需要在mysql中查询

本文介绍了我需要在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