根据一列选择行 [英] Selecting a row based on one column

问题描述

我有一张名为Result的表



用户名Test1Mark Test2Mark Test3Mark Totalmark

abc 23 23 23 69

xyz 21 21 21 63



我想选择最大总标记的行怎么做





我试过这个



从结果中选择用户名，最大值（总标记）;

显示错误：（

I have a table called Result

Username Test1Mark Test2Mark Test3Mark Totalmark
abc 23 23 23 69
xyz 21 21 21 63

I want to select the row which has max totalmark how to do this


I tried this

Select Username,Max(Totalmark) from result;
its showing error :(

推荐答案

尝试

Try

select top 1 * from Result order by Totalmark desc

为了满足具有相同Totalmark的多条记录，然后引入其他标准只需通过子句向订单添加额外的位，例如



To cater for more than one record having the same Totalmark, and then introducing other criteria just add extra bits to the order by clause e.g.

select top 1 * from Result order by Totalmark desc, Test1Mark1 desc

添加e根据需要xtra标准...

adding extra criteria as required...

select top 1 * from Result order by Totalmark desc, Test1Mark1, Test1Mark2, Test3Mark desc

这里'这样做的一种方式......

Here''s one way of doing it ...

declare @max3 int
set @max3 = (select max(Testmark3) from Result)
select * from Result where Testmark3 = @max3 order by Totalmark desc

{编辑3 - OP评论的进一步答案 - 根据要求从解决方案2移到此处]

[解决方案不适用于原始问题但后续评论]

抱歉，如果我过于简单，但我会假设你什么都不知道，而不是错过一些东西:-)



嗯，第一点是锻炼如何限制我们要提取的客户......提示为您提供了这个。我们需要计算成本并将其与固定值进行比较......

{Edit 3 - further answers to OP comments - moved here from solution 2 as requested]
[Solution is not for original question but subsequent comment]
Apologies if I get too simplistic but I''m going to assume you know nothing rather than miss something out :-)

Well, the first bit is to work out how to limit the customers we are going to extract ... the hint gives you this. We need to calculate the "cost" and compare it to a fixed value...

WHERE (item_price * quantity_sold) > 200

请注意，列名在各个表中是唯一的 - 但是我仍然更愿意指出我在列出时正在查看哪个表列，所以最终的解决方案（下面）将添加一些细节。



接下来找出如何将表连接在一起... id字段有足够好的名字要解决这个问题 - 销售表将通过item_id链接到customer表的customer表，并通过item_id链接到item表..所以我们会得到东西...

Note that the column names are unique across the tables - however I still prefer to point out which table I''m looking at when listing columns so the final solution (below) will add some detail.

Next work out how to "join" the tables together... the id fields have nice enough names to work that out - the sales table will link to the customer table with customer_id and to the item table via item_id .. so we''ll be getting stuff ...

FROM sales S
INNER JOIN customer C ON S.customer_id=C.customer_id
INNER JOIN item I ON S.item_id=I.item_id

最后，我们将把所有内容与一些适当的东西一起检索所有表中的ved ...

Finally we''ll put it all together with some appropriate things being retrieved from all of the tables ...

SELECT C.customer_name, S.bill_no, I.item_name, I.item_price, S.quantity_sold, (I.item_price * S.quantity_sold) AS cost
FROM sales S 
INNER JOIN customer C ON S.customer_id=C.customer_id
INNER JOIN item I ON S.item_id=I.item_id
WHERE (I.item_price * S.quantity_sold) > 200

请注意，我已经通过SELECT，FROM和WHERE子句使用了表别名 - 它这是个人偏好但节省了大量不必要的打字，并且当这些查询变得越来越大时有助于保持清晰！

注意我选择做内部加入，因为我知道销售必须同时涉及客户和项目（否则它不会是销售，对吧！）

此链接说明了您可以加入这些表格的其他方式

http：//www.codinghorror。 com / blog / 2007/10 / a-visual-explanation-of-sql-joins.html [ ^ ]

这篇关于根据一列选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！