如何编写查询只获取一条记录？ [英] How to write a query to get only one record?

问题描述

我有一个名为advisor_ranks的表，在那个表中有三列，UserId，RankNo和AchievedDate，但没有PK或FK。在该表中，一个UserId包含几个记录，现在我想编写一个查询来获取像一个用户ID一个记录的特定记录。那么有人可以帮我查询吗？



我试过这个

 选择 * 来自 advisor_ranks  group   by  userid，rankno，completeddate 

但结果与< pre lang =SQL> 选择 * 来自 advisor_ranks

解决方案

基于有限的信息，我们无能为力。

但我认为你需要准确看看你想要回归的内容，并改变你正在进行查询的方式。



首先要做的是停止发出通配符选择：

  SELECT  *  FROM  MyTable ... 

部分原因是它效率低下，以后您可能不会想要所有列（如果添加Image列会发生什么 - 返回大小增长很大），部分是因为它可以帮助你专注于你想要实现的目标。



当你说：

  SELECT  *  FROM  advisor_ranks  GROUP   BY  userid，rankno，achieate 

这是公平的说法：

  SELECT  userid，rankno，achieate  FROM  advisor_ranks  GROUP   BY  userid，rankno，achieate 

这是完全相同：

  SELECT  userid，rankno，achieate  FROM  advisor_ranks 

因为它返回相同的行，因为GROUP BY子句指定哪些行相同以便将它们组合在一起。由于您在GROUP BY子句中指定了所有行，并且没有两行可以相同，因此它是多余的。



为了使用GROUP BY并返回单个行每个用户ID的行很容易开始：

  SELECT  userid  FROM  advisor_ranks  GROUP   BY  userid 

但你可能有注意到SQL Complains，如果你试图返回任何进一步的列，因为它们不是聚合函数或包含在GROUP BY语句中 - 这是我不知道如何组合不同的值的SQL对话。想想看：如果你有两行用户ID 6：

 userid rankno achiedate 
 6 4 2013-01-01 
 6 3 2013 -03-17 

那你想要哪些数据？哪个等级？哪个日期？ SQL无法为您组合它们，因此您必须指定它：

  SELECT  userid，SUM （rankno），MAX（已完成） FROM  advisor_ranks  GROUP   BY  userid 

可以工作 - 但只有你可以决定你真正需要返回什么。

I have a table called advisor_ranks, in that table three columns are there, UserId, RankNo and AchievedDate, but there is not PK or FK. In that table one UserId contains several records, Now I want to write a query to get perticular records like one userid one records. So can anyone help me to get this query?

I tried this

select * from advisor_ranks group by userid,rankno,achieveddate

but result is like same like

select * from advisor_ranks

解决方案

We can't help that much, based on that limited information.
But I think you need to look at exactly what you are trying to return, and change teh way you are doing queries.

The first thing to do is to stop issuing wildcard selects:

SELECT * FROM MyTable ...

Partly because it is inefficient in that you may not in future want all the columns (what happens if you add an "Image" column - the return size grows enormously), and partly because it helps you to focus on what you are trying to achieve.

When you say:

SELECT * FROM advisor_ranks GROUP BY userid, rankno, achievedate

It is the equivelant of saying:

SELECT userid, rankno, achievedate FROM advisor_ranks GROUP BY userid, rankno, achievedate

Which is exactly the same as saying:

SELECT userid, rankno, achievedate FROM advisor_ranks

because it returns the same rows, given that the GROUP BY clause specifies which rows mush be identical in order to group them together. Since you specify all your rows in the GROUP BY clause and no two rows can ever be identical, it is redundant.

In order to use GROUP BY and return a single row for each userid is simple to start with:

SELECT userid FROM advisor_ranks GROUP BY userid

But you have probably noticed that SQL Complains if you try to return any further columns because they are "not aggregate functions or included in the GROUP BY statement" - which is SQL talk for "I don't know how to combine the different values". Think about it: if you have two rows for userid 6:

userid   rankno  achieveddate
   6        4     2013-01-01
   6        3     2013-03-17

Then which data did you want? Which rank? Which date? SQL can't combine them for you, so you have to specify it:

SELECT userid, SUM(rankno), MAX(achievedate) FROM advisor_ranks GROUP BY userid

Will work - but only you can decide what you actually need to return.

这篇关于如何编写查询只获取一条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！