with textTable as
(
SELECT id,text as test1 FROM syscomments s WHERE id=OBJECT_ID('wage')--这个只有两行记录
),nameTable as(
Select id,name From SysColumns Where id=Object_Id('wage')--这个有N行记录
)select * from textTable inner join nameTable on textTable.id=nameTable.id
这样查询什么会有N行记录出来.我只想写成只有两行记录
我用left join也是一样
这样就行了:
select a.id,a.text,b.name from syscomments a inner join syscolumns b on a.id=b.id where a.id=OBJECT_ID('wage')
nameTable as(
Select id,name From SysColumns Where id=Object_Id('wage')--这个有N行记录
)
改为
nameTable as(
Select id,name From SysColumns Where id=Object_Id('wage') group by id,name--这个有N行记录
)
nameTable as(
Select distinct id,name From SysColumns Where id=Object_Id('wage')--这个有N行记录
)
要这样
nameTable as(
Select id,name From SysColumns Where id=Object_Id('wage')--这个有N行记录
)
改为
nameTable as(
Select id,max(name) From SysColumns Where id=Object_Id('wage') group by id
)
Msg 8155, Level 16, State 2, Line 1
没有为 'nameTable' 的列 2 指定任何列。
它们没有关联,除了是在同一个表中外.
go
create database test1
go
use test1
go
create table tb(id int identity(1,1) primary key,col1 int,col2 int,col3 int,col4 int,
constraint checkcol3 check(col3=0 or col3=1)
)
go
create trigger setcol3
on tb
INSTEAD OF insert
as
begin
insert into tb select col1,col2,col3,(case when col3=0 then col1+col2 else col1-col2 end)as col4 from inserted
end
go
select b.name,a.text from syscomments a inner join syscolumns b on charindex(b.name,a.text)>0
/*
name text
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
col3 ([col3]=(0) OR [col3]=(1))
col1 create trigger setcol3
on tb
INSTEAD OF insert
as
begin
insert into tb select col1,col2,col3,(case when col3=0 then col1+col2 else col1-col2 end)as col4 from inserted
end
col2 create trigger setcol3
on tb
INSTEAD OF insert
as
begin
insert into tb select col1,col2,col3,(case when col3=0 then col1+col2 else col1-col2 end)as col4 from inserted
end
col3 create trigger setcol3
on tb
INSTEAD OF insert
as
begin
insert into tb select col1,col2,col3,(case when col3=0 then col1+col2 else col1-col2 end)as col4 from inserted
end
col4 create trigger setcol3
on tb
INSTEAD OF insert
as
begin
insert into tb select col1,col2,col3,(case when col3=0 then col1+col2 else col1-col2 end)as col4 from inserted
end(5 行受影响)*/
go
use master
go
drop database test1
Select id,max(name) as name From SysColumns Where id=Object_Id('wage') group by id
)