none
求解一个视图,SQL 2005的,高手请进【非常急】 RRS feed

  • 问题

  • 表userinfo 是记录用户资料

    表work 是记录该用户的工作经历,和userinfo是一对多的关系,一个用户可能会有很多个工作经历

    表edu是记录用户的教育经历的,和work表一样

     

    基本结构

    userinfo 

    用户ID 姓名

    edu
    教育ID 用户ID 学历ID(越大越高)  学校名称

    work

    工作ID 用户ID 工作岗位 工作结束时间   

     

    现在想得到这个三个表的查询结果。所有字段都必须出来,其中EDU表只取学历ID最大的一条,WORK表只取最大工作结束时间(时间格式是201001这样,如果出现重复,那么取第一条没学历也通常存在这样的情况),也几是说每个用户只出现一条。

     

    求高手指点,谢谢。




    • 已编辑 cnGrasp 2011年6月13日 5:35 1
    2011年6月13日 5:31

全部回复

  • 你的难点在于如何查询学历ID最大的一条和WORK表只取最大工作结束时间

    知道如何查询这个,其他的就简单了。

    学历ID最大的一条SQL:

    select t1.* from edu t1,(

    select userid,max(eduid) as eduid

    from edu

    group by userid) t2

    where t1.eduid=t2.eduid

    WORK表只取最大工作结束时间的记录SQL(和上面类似):

    select t1.* from work t1,(

    select userid,max(工作结束时间) as 工作结束时间

    from work

    group by userid) t2

    where t1.userid=t2.userid and t1.工作结束时间=t2.工作结束时间

    你只需要将这个结果和用户表一起就能查到你要的结果。

     

     


    family as water
    2011年6月13日 6:42
  • 你的难点在于如何查询学历ID最大的一条和WORK表只取最大工作结束时间

    知道如何查询这个,其他的就简单了。

    学历ID最大的一条SQL:

    select t1.* from edu t1,(

    select userid,max(eduid) as eduid

    from edu

    group by userid) t2

    where t1.eduid=t2.eduid

    WORK表只取最大工作结束时间的记录SQL(和上面类似):

    select t1.* from work t1,(

    select userid,max(工作结束时间) as 工作结束时间

    from work

    group by userid) t2

    where t1.userid=t2.userid and t1.工作结束时间=t2.工作结束时间

    你只需要将这个结果和用户表一起就能查到你要的结果。

     

     


    family as water
    我想得到的是如果EDU或者WORK为空,也就是用户没填写,那么就得到空值,按照上面的语法如果为空应该出不来了,在第二个select里面是否应该加入top 1呢?如果是两条以上的话一个用户,出来的是2条以上的数据,我测试过了。
    2011年6月13日 9:46
  • 你好cnGrasp

     

    根据你的描述,我们现在的目的是为每一个用户,找出其学历最高并且工作结束时间最大的记录。其实Stone已经将这个查询的难点都已经解决了,现在我们只需要将userinfo表和stone给出的进行关联即可得出您想要的数据,这里应该使用LEFT OUTER JOIN关键字,我的测试代码如下:
    创建userinfo表:

    CREATE TABLE [dbo].[Userinfo](
    	[userid] [int] IDENTITY(1,1) NOT NULL,
    	[name] [nvarchar](200) NULL,
     CONSTRAINT [PK_Userinfo] PRIMARY KEY CLUSTERED 
    (
    	[userid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    


    创建edu表:

    CREATE TABLE [dbo].[EDU](
    	[eduid] [int] NOT NULL,
    	[userid] [int] NULL,
    	[edubacid] [int] NULL,
    	[schoolname] [nvarchar](200) NULL,
     CONSTRAINT [PK_EDU] PRIMARY KEY CLUSTERED 
    (
    	[eduid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[EDU] WITH CHECK ADD CONSTRAINT [FK_EDU_Userinfo] FOREIGN KEY([userid])
    REFERENCES [dbo].[Userinfo] ([userid])
    GO
    
    ALTER TABLE [dbo].[EDU] CHECK CONSTRAINT [FK_EDU_Userinfo]
    GO
    


    创建work表:

    CREATE TABLE [dbo].[Work](
    	[workid] [int] NOT NULL,
    	[userid] [int] NULL,
    	[post] [nvarchar](200) NULL,
    	[endtime] [date] NULL,
     CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED 
    (
    	[workid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Work] WITH CHECK ADD CONSTRAINT [FK_Work_Userinfo] FOREIGN KEY([userid])
    REFERENCES [dbo].[Userinfo] ([userid])
    GO
    
    ALTER TABLE [dbo].[Work] CHECK CONSTRAINT [FK_Work_Userinfo]
    GO
    


    实现要求的查询语句:

    SELECT UE.USERID,UE.NAME,UE.EDUID,UE.EDUBACKID,UE.SCHOOLID,W.WORKID,W.POST,W.WORKENDTIME 
    FROM 
    (
    SELECT U.userid AS USERID,U.name AS NAME,E.eduid AS EDUID,E.edubacid AS EDUBACKID,E.schoolname AS SCHOOLID
    FROM USERINFO AS U 
       LEFT OUTER JOIN 
      (SELECT E1.* from EDU E1 
       INNER JOIN 
       ( SELECT userid,max(eduid) AS eduid 
        FROM edu
        GROUP BY userid) E2 
        ON E1.eduid=E2.eduid)
        AS E 
       ON U.userid=E.userid ) 
    AS UE LEFT OUTER JOIN 
       (SELECT W1.userid,W1.workid as WORKID,W1.post AS POST,W1.endtime AS WORKENDTIME
       FROM work W1 INNER JOIN
       ( SELECT userid,MAX(endtime) AS workendtime
        FROM work 
        GROUP BY userid) AS W2 
        ON W1.userid=W2.userid and W1.endtime=W2.workendtime) 
    AS W 
    ON UE.userid = W.userid
    GO
    
    

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    2011年6月15日 7:59
    版主
  • 对工作经历和学历经历取Row_Number() =1最方便,性能也可取
    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2011年6月16日 3:08
  • 你好cnGrasp

     

    根据你的描述,我们现在的目的是为每一个用户,找出其学历最高并且工作结束时间最大的记录。其实Stone已经将这个查询的难点都已经解决了,现在我们只需要将userinfo表和stone给出的进行关联即可得出您想要的数据,这里应该使用LEFT OUTER JOIN关键字,我的测试代码如下:
    创建userinfo表:

     

    CREATE TABLE [dbo].[Userinfo](
    	[userid] [int] IDENTITY(1,1) NOT NULL,
    	[name] [nvarchar](200) NULL,
     CONSTRAINT [PK_Userinfo] PRIMARY KEY CLUSTERED 
    (
    	[userid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    

     


    创建edu表:

    CREATE TABLE [dbo].[EDU](
    	[eduid] [int] NOT NULL,
    	[userid] [int] NULL,
    	[edubacid] [int] NULL,
    	[schoolname] [nvarchar](200) NULL,
     CONSTRAINT [PK_EDU] PRIMARY KEY CLUSTERED 
    (
    	[eduid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[EDU] WITH CHECK ADD CONSTRAINT [FK_EDU_Userinfo] FOREIGN KEY([userid])
    REFERENCES [dbo].[Userinfo] ([userid])
    GO
    
    ALTER TABLE [dbo].[EDU] CHECK CONSTRAINT [FK_EDU_Userinfo]
    GO
    


    创建work表:

    CREATE TABLE [dbo].[Work](
    	[workid] [int] NOT NULL,
    	[userid] [int] NULL,
    	[post] [nvarchar](200) NULL,
    	[endtime] [date] NULL,
     CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED 
    (
    	[workid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Work] WITH CHECK ADD CONSTRAINT [FK_Work_Userinfo] FOREIGN KEY([userid])
    REFERENCES [dbo].[Userinfo] ([userid])
    GO
    
    ALTER TABLE [dbo].[Work] CHECK CONSTRAINT [FK_Work_Userinfo]
    GO
    


    实现要求的查询语句:

     

    SELECT UE.USERID,UE.NAME,UE.EDUID,UE.EDUBACKID,UE.SCHOOLID,W.WORKID,W.POST,W.WORKENDTIME 
    FROM 
    (
    SELECT U.userid AS USERID,U.name AS NAME,E.eduid AS EDUID,E.edubacid AS EDUBACKID,E.schoolname AS SCHOOLID
    FROM USERINFO AS U 
      LEFT OUTER JOIN 
     (SELECT E1.* from EDU E1 
      INNER JOIN 
      ( SELECT userid,max(eduid) AS eduid 
      FROM edu
      GROUP BY userid) E2 
      ON E1.eduid=E2.eduid)
      AS E 
      ON U.userid=E.userid ) 
    AS UE LEFT OUTER JOIN 
      (SELECT W1.userid,W1.workid as WORKID,W1.post AS POST,W1.endtime AS WORKENDTIME
      FROM work W1 INNER JOIN
      ( SELECT userid,MAX(endtime) AS workendtime
      FROM work 
      GROUP BY userid) AS W2 
      ON W1.userid=W2.userid and W1.endtime=W2.workendtime) 
    AS W 
    ON UE.userid = W.userid
    GO
    
    

    Thanks,
    Weilin Qiao

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    首先非常感谢,您这个方法也尝试过了,但是我允许在同一个时间出现两个工作,也允许同一个学历程度出现两个。那么用MAX应该是取出两条来的。。Row_Number()不会写啊,应该用
    Row_Number()就可以搞定,刚接触2005,我用的也是2005。。。
    2011年6月16日 5:45