在SQL Server 2008中自行加入表

时间:2022-02-11 14:50:23

I would like to use INNER JOIN on one table. But I get this error message:

我想在一个表上使用INNER JOIN。但我收到此错误消息:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'a'.

消息208,级别16,状态1,行1无效的对象名称“a”。

My query is :

我的查询是:

select * 
from 
    (select 
         *,
         ROW_NUMBER() OVER (ORDER BY GoodMainCode) as row 
     from [SarMem].[dbo].[Book_Data1]  
     where GoodName like '%A%' and GroupCode = 115) a 
  inner join a b on b.GoodMainCode = a.GoodMainCode 
where a.row > 0 and a.row <= 100  

updated

更新

在SQL Server 2008中自行加入表

3 个解决方案

#1


3  

Do it with cte:

用cte做:

;with a as(select *, ROW_NUMBER() OVER (ORDER BY GoodMainCode) as row 
           from [SarMem].[dbo].[Book_Data1]  
           where GoodName like '%A%' and GroupCode = 115) 
 select * from a
 join a b on b.GoodMainCode = a.GoodMainCode
 where a.row > 0 and a.row <= 100  

#2


0  

Maybe something like that?

也许是这样的?

WITH List AS (
SELECT 
    ROW_NUMBER() OVER (ORDER BY GoodMainCode) AS Row, *  
FROM
    [SarMem].[dbo].[Book_Data1]
WHERE
    ( GoodName like '%A%' )
    AND ( GroupCode = 115 )
) 
SELECT *  FROM List WHERE Row between 1 and 100 

#3


0  

I think here a is an alias name not the table name. So SQL SERVER won't allow a to create one more alias b.

我认为这是一个别名而不是表名。因此SQL SERVER不允许创建一个别名b。

So if you wanna use same table as a, then you have to rewrite subquery for b also.

因此,如果您想使用相同的表作为a,那么您还必须为b重写子查询。

Like,

喜欢,

select * from (
select *,ROW_NUMBER() OVER (ORDER BY GoodMainCode) as row from [SarMem].[dbo].[Book_Data1]  where GoodName like '%A%'  and GroupCode = 115  ) a 
 INNER  JOIN (
select *,ROW_NUMBER() OVER (ORDER BY GoodMainCode) as row from [SarMem].[dbo].[Book_Data1]  where GoodName like '%A%'  and GroupCode = 115  ) b on b.GoodMainCode = a.GoodMainCode where  a.row > 0 and a.row <= 100  

#1


3  

Do it with cte:

用cte做:

;with a as(select *, ROW_NUMBER() OVER (ORDER BY GoodMainCode) as row 
           from [SarMem].[dbo].[Book_Data1]  
           where GoodName like '%A%' and GroupCode = 115) 
 select * from a
 join a b on b.GoodMainCode = a.GoodMainCode
 where a.row > 0 and a.row <= 100  

#2


0  

Maybe something like that?

也许是这样的?

WITH List AS (
SELECT 
    ROW_NUMBER() OVER (ORDER BY GoodMainCode) AS Row, *  
FROM
    [SarMem].[dbo].[Book_Data1]
WHERE
    ( GoodName like '%A%' )
    AND ( GroupCode = 115 )
) 
SELECT *  FROM List WHERE Row between 1 and 100 

#3


0  

I think here a is an alias name not the table name. So SQL SERVER won't allow a to create one more alias b.

我认为这是一个别名而不是表名。因此SQL SERVER不允许创建一个别名b。

So if you wanna use same table as a, then you have to rewrite subquery for b also.

因此,如果您想使用相同的表作为a,那么您还必须为b重写子查询。

Like,

喜欢,

select * from (
select *,ROW_NUMBER() OVER (ORDER BY GoodMainCode) as row from [SarMem].[dbo].[Book_Data1]  where GoodName like '%A%'  and GroupCode = 115  ) a 
 INNER  JOIN (
select *,ROW_NUMBER() OVER (ORDER BY GoodMainCode) as row from [SarMem].[dbo].[Book_Data1]  where GoodName like '%A%'  and GroupCode = 115  ) b on b.GoodMainCode = a.GoodMainCode where  a.row > 0 and a.row <= 100