如何从两个表中为每个用户选择选定的记录数 [英] how to select selected number of records for each user from two tables

问题描述

我有两个表，用户"和用户等级".在用户"表中，我有用户名和其他详细信息，用户ID"是主键.在"user_grading"表中，我对所有用户进行了分级，分级的年份是"gradingid"是主键，"userid"是外键.现在，我需要获取每个用户最近3年的分级，其名称存储在其他表中.请帮助我如何在sql server 2005中执行此操作.表详细信息如下...

用户:
userid int PK
用户名文本

user_grading:
gradingid int PK
userid int FK
分级文字
年int

......
我想要的是这样的东西

用户名年份分级
sam 2009 good
sam 2010 avg
萨姆2011好
约翰(John)2007良好
约翰2009年平均水平
约翰2010年好
...等等
请告诉我该怎么做

i have two tables, ''users'' and ''user_grading''. in ''users'' tables i had user name and other details and ''userid'' is the primary key. in ''user_grading'' table i had grading of all users and the year of which the grading is, ''gradingid'' is the primary key and ''userid'' is the foreign key. now i need to get the last 3 year grading of each user with its name which is stored in other table. please help me how to do this in sql server 2005. the table details are as follows...

users:
userid int PK
User_Name text

user_grading:
gradingid int PK
userid int FK
grading text
year int

...........
and what i want is some what like this

UserName Year Grading
sam 2009 good
sam 2010 avg
sam 2011 good
john 2007 good
john 2009 avg
john 2010 good
... and so on
please tell me what to do

推荐答案

如果我正确理解的话，最后3个等级不一定来自最近3年.在您的示例中，您有

If I understood correctly, the last 3 gradings are not necessarily from last 3 years. In your example you have

john 2007 good
john 2009 avg
john 2010 good

因此，约翰对2008年没有任何评分.

如果这是问题所在，您可以尝试执行以下操作:

So John doesn''t have any grading for the year 2008.

If this is the problem, you can try something like:

SELECT *
FROM users u,
     user_grading ug
where u.userid = ug.userid
and   3 <= (select count(*)
            from user_grading ug2
            where ug2.userid = ug.userid
            and ug2.year > ug.year)

无论如何我都不是SQL专家，但这可能行得通...

I''m not a sql expert by any means, but this might work...

select top 3 
       u.user_name
       ,g.year       
       ,g.name
from [users] u
inner join user_grading g 
on u.userid = g.userid
order by g.year desc

编辑==================

好的，请尝试以下操作:

EDIT ==================

Okay then, try this:

select u.user_name
       ,g.year
       ,g.name
from [users] u
inner join user_grading g
on u.userid = g.userid
where g.year >= DATEPART(year, GETDATE()) - 2
order by u.user_name, g.year desc

您是否曾经考虑过要自己尝试填充东西?

Have you ever considered actually trying stuff yourself?

尝试:

SELECT u.User_name AS UserName, g.year AS Year, g.grading As Grading 
   FROM users AS u 
   JOIN user_grading AS g 
      ON u.userid=g.userid 
   WHERE g.Year>=DATEPART(year, GETDATE()) - 2

这篇关于如何从两个表中为每个用户选择选定的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！