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
更新
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