select tb1.*,tb2.conb from tb2 left join tb1 on tb2.id=tb1.id
select tb1.*,tb2.conb from tb2 inner join tb1 on tb2.id=tb1.id
ks_reny 查询的结果第二第三条里还是显示了1和2 而不是空
select isnull(a.id,b.id),a.numb,b.conb from table1 a full join table2 b on a.id=b.id
select tb1.*,tb2.conb from tb2 right join tb1 on tb2.id=tb1.id and tb1.numb =tb2.conb
if object_id('table1') is not null drop table table1 go create table table1(id int,numb int) insert into table1 values(1,2) insert into table1 values(2,3)if object_id('table2') is not null drop table table2 go create table table2(id int,conb int) insert into table2 values(1,2) insert into table2 values(1,3) insert into table2 values(1,4)select a.id,a.numb,b.conb from table1 a,table2 b where a.id=b.id/* id numb conb ----------- ----------- ----------- 1 2 2 1 2 3 1 2 4(3 行受影响) */
create table table1(id int,numb int) create table table2(conb int, id int) insert table1 select 1,2 insert table1 select 2,3 insert table2 select 2,1 insert table2 select 4,1 insert table2 select 3,1 select * from table1 select * from table2 goselect isnull(a.id,b.id)id ,a.numb,b.conb from table1 a full join table2 b on a.id=b.id go drop table table1,table2 /* numb conb ----------- ----------- ----------- 1 2 2 1 2 4 1 2 3 2 3 NULL */
if object_id('tb1') is not null drop table tb1 go create table tb1(id int,numb int) insert into tb1 values(1,2) insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2 go create table tb2(id int,conb int) insert into tb2 values(1,2) insert into tb2 values(1,3) insert into tb2 values(1,4)select tb1.*,tb2.conb from tb1 right join tb2 on tb2.id=tb1.id and tb1.numb =tb2.conbid numb conb ----------- ----------- ----------- 1 2 2 NULL NULL 3 NULL NULL 4(3 行受影响)
if object_id('tb1') is not null drop table tb1 go create table tb1(id varchar(10),numb varchar(10)) insert into tb1 values(1,2) insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2 go create table tb2(id int,conb int) insert into tb2 values(1,2) insert into tb2 values(1,3) insert into tb2 values(1,4)select isnull(a.id,'') id,isnull(a.numb,'') numb,b.conb from tb1 a right join tb2 b on a.id=b.id and a.numb =b.conbid numb conb ---------- ---------- ----------- 1 2 2 3 4
---测试数据--- if object_id('[table1]') is not null drop table [table1] go create table [table1]([id] int,[numb] int) insert [table1] select 1,2 union all select 2,3 if object_id('[table2]') is not null drop table [table2] go create table [table2]([conb] int,[id] int) insert [table2] select 2,1 union all select 4,1 union all select 3,1
---查询--- select case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id, case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb, b.conb from table1 a,table2 b where a.id=b.id order by conb---结果--- id numb conb ------------ ------------ ----------- 1 2 2 3 4(所影响的行数为 3 行)
if object_id('table1') is not null drop table table1 go create table table1(id int,numb int) insert into table1 values(1,2) insert into table1 values(2,3)if object_id('table2') is not null drop table table2 go create table table2(id int,conb int) insert into table2 values(1,2) insert into table2 values(1,3) insert into table2 values(1,4)select case when rn = 1 then cast(id as varchar(10)) else '' end as id, case when rn = 1 then cast(numb as varchar(10)) else '' end as numb, conb from(select a.id,a.numb,b.conb,rn=row_number()over(partition by a.id order by b.conb) from table1 a,table2 b where a.id=b.id)tdrop table table1 drop table table2/* 1 2 2 3 4 */
select case when aid is null then '' else ltrim(aid) end as aid,aname,acount from (select a.*,pxa=a.aid from tb a union all (select aid=null,bname,bcount,pxb=aid from tb1))a order by pxa asc,aid desc
if object_id('table1') is not null drop table table1 go create table table1(id int,numb int) insert into table1 values(1,2) insert into table1 values(2,3)if object_id('table2') is not null drop table table2 go create table table2(id int,conb int) insert into table2 values(1,2) insert into table2 values(1,3) insert into table2 values(1,4) (select case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id, case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb, ltrim(b.conb) as conb from table1 a,table2 b where a.id=b.id) union all (select ltrim(a.id),ltrim(a.numb),'' from table1 a where id not in(select distinct id from table2))/* id numb conb ------------ ------------ ------------ 1 2 2 3 4 2 3 (4 行受影响)*/
table1 table2 id numb conb id 1 2 2 1 2 3 4 1 3 1 查询的结果为 id numb conb 1 2 2 4 3select table1.id ,table1.numb,conb from table2 left table1 where table1.id=1
select table1.id ,table1.numb,conb from table2 left join table1 on table1.id=table2.id where table1.id=1
if object_id('[table1]') is not null drop table [table1] go create table [table1]([id] int,[numb] int) insert [table1] select 1,2 union all select 2,3 if object_id('[table2]') is not null drop table [table2] go create table [table2]([conb] int,[id] int) insert [table2] select 2,1 union all select 4,1 union all select 3,1
---查询--- select case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id, case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb, b.conb from table1 a,table2 b where a.id=b.id order by conb---结果--- id numb conb ------------ ------------ ----------- 1 2 2 3 4(所影响的行数为 3 行)
declare @table1 table (id int,numb int) insert into @table1 select 1,2 union all select 2,3--select * from @table1declare @table2 table (conb int,id int) insert into @table2 select 2,1 union all select 4,1 union all select 3,1--select * from @table2 select aa.id,aa.numb,bb.conb from ( select row_number() over (order by a.id) as row, a.id,a.numb from @table1 a inner join @table2 b on a.id=b.id group by a.id,a.numb ) aa right join ( select row_number() over (order by b.id) as row,b.id, b.conb from @table1 a inner join @table2 b on a.id=b.id ) bb on aa.row=bb.row /* id numb conb ----------- ----------- ----------- 1 2 2 NULL NULL 4 NULL NULL 3 */
select tb1.*,tb2.conb from tb2 left join tb1 on tb2.id=tb1.id
create table table1 (id int,numb int) create table table2 ( conb int,id int) insert into table1 select 1,2 union select 2 , 3 insert into table2 select 2,1 union select 4 , 1 union select 3 ,1 select * from table1 select * from table2 go select table1.id,table1.numb,table2.conb from table1 right join table2 on table1.numb=table2.conb and table1.id=1 godrop table table1 drop table table2
right!! select tb1.*,tb2.conb from tb2 right join tb1 on tb2.id=tb1.id
declare @i int set @i=0 while(@i<256) begin print char(@i) set @i=@i+1 end [[email protected]][/email]
slect table1.id,table1.numb,table2.cond from table1,table2 where table1.id = table2.id
连个题都没写清,还推荐,CSDN素质真是越来越低了
if object_id('tb1') is not null drop table tb1 go create table tb1(id varchar(10),numb varchar(10)) insert into tb1 values(1,2) insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2 go create table tb2(id int,conb int) insert into tb2 values(1,2) insert into tb2 values(1,3) insert into tb2 values(1,4)select isnull(a.id,'') id,isnull(a.numb,'') numb,b.conb from tb1 a right join tb2 b on a.id=b.id and a.numb =b.conbid numb conb ---------- ---------- ----------- 1 2 2 3 4
select isnull(a.id,b.id),a.numb,b.conb from table1 a full join table2 b on a.id=b.id
我觉得直接用select的简单查询就可以了啊。select table1.id,table1.numb,table2.conb from table1,table2 where table1.id=table2.id
select tb1.*,tb2.conb from tb2 inner join tb1 on tb2.id=tb1.id
select tb1.*,tb2.conb from tb2 inner join tb1 on tb2.id=tb1.id
select tb1.*,tb2.conb from tb2 right join tb1 on tb2.id=tb1.id and tb1.numb =tb2.conb
create table #a(id int,numb float) insert into #a(id,numb) select '1',2 union all select '2',3create table #b(id int,conb float) insert into #b(id,conb) select '1',2 union all select '1',4 union all select '1',3 select a.id,a.numb,b.conb from #a a right outer join #b b on a.id=b.id and a.numb=b.conb
select tb1.*,tb2.conb from tb2 left join tb1 on tb2.id=tb1.id
create table tb1 ( id int primary key, numb int ) go create table tb2 ( conb int primary key, id int foreign key (id) references tb1(id) )insert into tb1 values(1,2) insert into tb1 values(2,3) insert into tb2 values(2,1) insert into tb2 values(3,1)insert into tb2 values(4,1)select tb1.*,tb2.conb from tb1 right join tb2 on tb2.id=tb1.id and tb2.conb=tb1.numb
SET NOCOUNT ON DECLARE @tb1 TABLE([id] int,[numb] int) insert @tb1 select 1,2 union all select 2,3DECLARE @tb2 TABLE([conb] int,[id] int) insert @tb2 select 2,1 union all select 4,1 union all select 3,1 union ALL select 1,2 union ALL select 5,2SELECT case when (select count(1) from @tb2 c where c.conb < b.conb AND c.id = b.id)=0 then ltrim(a.id) else '' end as id1, case when (select count(1) from @tb2 c where c.conb < b.conb AND c.id = b.id)=0 then ltrim(a.numb) else '' end as numb, b.conb FROM @tb1 a,@tb2 b WHERE a.id = b.id ORDER BY a.id Asc,b.conb ASCSET NOCOUNT OFF /*1 2 2 3 4 2 3 1 5应该是需要这样的效果 */
select tb1.*,tb2.conb from tb2 inner join tb1 on tb2.id=tb1.id
select isnull(a.id,b.id),a.numb,b.conb from table1 a full join table2 b on a.id=b.id
create table #table1(id int,numb int) insert into #table1 values(1,2) insert into #table1 values(2,3) create table #table2(id int,conb int) insert into #table2 values(1,2) insert into #table2 values(1,3) insert into #table2 values(1,4) (select case when (select count(1) from #table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id, case when (select count(1) from #table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb, ltrim(b.conb) as conb from #table1 a,#table2 b where a.id=b.id) union all (select ltrim(a.id),ltrim(a.numb),'' from #table1 a where id not in(select distinct id from #table2)) drop table #table1 drop table #table2结果:(所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)id numb conb ------------ ------------ ------------ 1 2 2 3 4 2 3 (所影响的行数为 4 行)
if object_id('tb1') is not null drop table tb1 go create table tb1(id varchar(10),numb varchar(10)) insert into tb1 values(1,2) insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2 go create table tb2(id int,conb int) insert into tb2 values(1,2) insert into tb2 values(1,3) insert into tb2 values(1,4)select isnull(a.id,'') id,isnull(a.numb,'') numb,b.conb from tb1 a right join tb2 b on a.id=b.id and a.numb =b.conbid numb conb ---------- ---------- ----------- 1 2 2 3 4
select tb1.*,tb2.conb from tb2 left join tb1 on tb2.id=tb1.id
查询的结果第二第三条里还是显示了1和2
而不是空
go
create table table1(id int,numb int)
insert into table1 values(1,2)
insert into table1 values(2,3)if object_id('table2') is not null drop table table2
go
create table table2(id int,conb int)
insert into table2 values(1,2)
insert into table2 values(1,3)
insert into table2 values(1,4)select a.id,a.numb,b.conb
from table1 a,table2 b
where a.id=b.id/*
id numb conb
----------- ----------- -----------
1 2 2
1 2 3
1 2 4(3 行受影响)
*/
create table table2(conb int, id int)
insert table1 select 1,2
insert table1 select 2,3
insert table2 select 2,1
insert table2 select 4,1
insert table2 select 3,1
select * from table1
select * from table2
goselect isnull(a.id,b.id)id ,a.numb,b.conb from table1 a full join table2 b on a.id=b.id
go
drop table table1,table2
/*
numb conb
----------- ----------- -----------
1 2 2
1 2 4
1 2 3
2 3 NULL
*/
go
create table tb1(id int,numb int)
insert into tb1 values(1,2)
insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2
go
create table tb2(id int,conb int)
insert into tb2 values(1,2)
insert into tb2 values(1,3)
insert into tb2 values(1,4)select tb1.*,tb2.conb from tb1 right join tb2 on tb2.id=tb1.id and tb1.numb =tb2.conbid numb conb
----------- ----------- -----------
1 2 2
NULL NULL 3
NULL NULL 4(3 行受影响)
go
create table tb1(id varchar(10),numb varchar(10))
insert into tb1 values(1,2)
insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2
go
create table tb2(id int,conb int)
insert into tb2 values(1,2)
insert into tb2 values(1,3)
insert into tb2 values(1,4)select isnull(a.id,'') id,isnull(a.numb,'') numb,b.conb from tb1 a right join tb2 b on a.id=b.id and a.numb =b.conbid numb conb
---------- ---------- -----------
1 2 2
3
4
---测试数据---
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int,[numb] int)
insert [table1]
select 1,2 union all
select 2,3
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([conb] int,[id] int)
insert [table2]
select 2,1 union all
select 4,1 union all
select 3,1
---查询---
select
case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id,
case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb,
b.conb
from table1 a,table2 b
where a.id=b.id
order by conb---结果---
id numb conb
------------ ------------ -----------
1 2 2
3
4(所影响的行数为 3 行)
and tb1.numb =tb2.conb
这句话什么用,这两个并没有关联啊
numb conb
----------- ----------- -----------
1 2 2
4
3
2 3
最好可以这样显示
go
create table table1(id int,numb int)
insert into table1 values(1,2)
insert into table1 values(2,3)if object_id('table2') is not null drop table table2
go
create table table2(id int,conb int)
insert into table2 values(1,2)
insert into table2 values(1,3)
insert into table2 values(1,4)select
case when rn = 1 then cast(id as varchar(10)) else '' end as id,
case when rn = 1 then cast(numb as varchar(10)) else '' end as numb,
conb
from(select a.id,a.numb,b.conb,rn=row_number()over(partition by a.id order by b.conb)
from table1 a,table2 b
where a.id=b.id)tdrop table table1
drop table table2/*
1 2 2
3
4
*/
aid aname acount bid bname bcount aid
1 name1 count1 4 name3 count3 1
2 name2 count2 5 name4 count4 1
6 name5 count5 2就是查询出如下结果,因为第二条跟第一条的name和count都是重复的
所以不用显示 aname acount bname bcount
1 name1 count1 name3 count3
2 name4 count4
3 name2 count2 name5 count5
楼上的兄弟,那个row_number()函数不被识别啊
(select a.*,pxa=a.aid from tb a union all (select aid=null,bname,bcount,pxb=aid from tb1))a order by pxa asc,aid desc
if object_id('table1') is not null drop table table1
go
create table table1(id int,numb int)
insert into table1 values(1,2)
insert into table1 values(2,3)if object_id('table2') is not null drop table table2
go
create table table2(id int,conb int)
insert into table2 values(1,2)
insert into table2 values(1,3)
insert into table2 values(1,4)
(select
case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id,
case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb,
ltrim(b.conb) as conb
from table1 a,table2 b
where a.id=b.id)
union all
(select ltrim(a.id),ltrim(a.numb),''
from table1 a
where id not in(select distinct id from table2))/*
id numb conb
------------ ------------ ------------
1 2 2
3
4
2 3 (4 行受影响)*/
id numb conb id
1 2 2 1
2 3 4 1
3 1
查询的结果为
id numb conb
1 2 2
4
3select table1.id ,table1.numb,conb from table2 left table1 where table1.id=1
go
create table [table1]([id] int,[numb] int)
insert [table1]
select 1,2 union all
select 2,3
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([conb] int,[id] int)
insert [table2]
select 2,1 union all
select 4,1 union all
select 3,1
---查询---
select
case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id,
case when (select count(1) from table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb,
b.conb
from table1 a,table2 b
where a.id=b.id
order by conb---结果---
id numb conb
------------ ------------ -----------
1 2 2
3
4(所影响的行数为 3 行)
insert into @table1
select 1,2 union all
select 2,3--select * from @table1declare @table2 table (conb int,id int)
insert into @table2
select 2,1 union all
select 4,1 union all
select 3,1--select * from @table2
select aa.id,aa.numb,bb.conb from
(
select row_number() over (order by a.id) as row, a.id,a.numb from @table1 a inner join @table2 b on a.id=b.id group by a.id,a.numb
)
aa right join
(
select row_number() over (order by b.id) as row,b.id, b.conb from @table1 a inner join @table2 b on a.id=b.id
) bb
on aa.row=bb.row
/*
id numb conb
----------- ----------- -----------
1 2 2
NULL NULL 4
NULL NULL 3
*/
create table table1 (id int,numb int)
create table table2 ( conb int,id int)
insert into table1 select 1,2 union select 2 , 3
insert into table2 select 2,1 union select 4 , 1 union select 3 ,1
select * from table1
select * from table2
go
select table1.id,table1.numb,table2.conb
from table1 right join table2 on table1.numb=table2.conb and table1.id=1
godrop table table1
drop table table2
select tb1.*,tb2.conb from tb2 right join tb1 on tb2.id=tb1.id
set @i=0
while(@i<256)
begin
print char(@i)
set @i=@i+1
end
[[email protected]][/email]
go
create table tb1(id varchar(10),numb varchar(10))
insert into tb1 values(1,2)
insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2
go
create table tb2(id int,conb int)
insert into tb2 values(1,2)
insert into tb2 values(1,3)
insert into tb2 values(1,4)select isnull(a.id,'') id,isnull(a.numb,'') numb,b.conb from tb1 a right join tb2 b on a.id=b.id and a.numb =b.conbid numb conb
---------- ---------- -----------
1 2 2
3
4
create table #a(id int,numb float)
insert into #a(id,numb)
select '1',2 union all
select '2',3create table #b(id int,conb float)
insert into #b(id,conb)
select '1',2 union all
select '1',4 union all
select '1',3
select a.id,a.numb,b.conb from #a a right outer join #b b on a.id=b.id and a.numb=b.conb
(
id int primary key,
numb int
)
go
create table tb2
(
conb int primary key,
id int foreign key (id) references tb1(id)
)insert into tb1 values(1,2)
insert into tb1 values(2,3)
insert into tb2 values(2,1)
insert into tb2 values(3,1)insert into tb2 values(4,1)select tb1.*,tb2.conb from tb1 right join tb2 on tb2.id=tb1.id and tb2.conb=tb1.numb
insert @tb1
select 1,2 union all
select 2,3DECLARE @tb2 TABLE([conb] int,[id] int)
insert @tb2
select 2,1 union all
select 4,1 union all
select 3,1 union ALL
select 1,2 union ALL
select 5,2SELECT
case when (select count(1) from @tb2 c where c.conb < b.conb AND c.id = b.id)=0 then ltrim(a.id) else '' end as id1,
case when (select count(1) from @tb2 c where c.conb < b.conb AND c.id = b.id)=0 then ltrim(a.numb) else '' end as numb,
b.conb
FROM @tb1 a,@tb2 b
WHERE a.id = b.id
ORDER BY a.id Asc,b.conb ASCSET NOCOUNT OFF
/*1 2 2
3
4
2 3 1
5应该是需要这样的效果
*/
insert into #table1 values(1,2)
insert into #table1 values(2,3)
create table #table2(id int,conb int)
insert into #table2 values(1,2)
insert into #table2 values(1,3)
insert into #table2 values(1,4)
(select
case when (select count(1) from #table2 where conb<b.conb)=0 then ltrim(a.id) else '' end as id,
case when (select count(1) from #table2 where conb<b.conb)=0 then ltrim(a.numb) else '' end as numb,
ltrim(b.conb) as conb
from #table1 a,#table2 b
where a.id=b.id)
union all
(select ltrim(a.id),ltrim(a.numb),''
from #table1 a
where id not in(select distinct id from #table2))
drop table #table1
drop table #table2结果:(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id numb conb
------------ ------------ ------------
1 2 2
3
4
2 3 (所影响的行数为 4 行)
go
create table tb1(id varchar(10),numb varchar(10))
insert into tb1 values(1,2)
insert into tb1 values(2,3)if object_id('tb2') is not null drop table tb2
go
create table tb2(id int,conb int)
insert into tb2 values(1,2)
insert into tb2 values(1,3)
insert into tb2 values(1,4)select isnull(a.id,'') id,isnull(a.numb,'') numb,b.conb from tb1 a right join tb2 b on a.id=b.id and a.numb =b.conbid numb conb
---------- ---------- -----------
1 2 2
3
4