表AName
myname1
myname2
myname3表B
School
myschool1
myschool2
myschool3希望得到结果
Name School
myname1 myschool1
myname2 myschool2
myname3 myschool3
谢谢
myname1
myname2
myname3表B
School
myschool1
myschool2
myschool3希望得到结果
Name School
myname1 myschool1
myname2 myschool2
myname3 myschool3
谢谢
然后id相关联
School
from(
select name,id=row_number()over(order by getdate()) from ta)a
,
select School,id=row_number()over(order by getdate()) from tb)b
where a.id=b.id
(select name, px=(select count(1) from ta where name<=a.name) from ta a) b
left join
(select School, px=(select count(1) from tb where name<=a.name) from tb a) c
on b.px=c.px
然后关联上就OK了
select id=identity(int,1,1),* into #2 from bselect
a.Name,
b.School
from
#1 a
left join #2 b on a.id=b.id
select [name],
School
from(
select [name],id=row_number()over(order by getdate()) from ta)a,
(select School,id=row_number()over(order by getdate()) from tb)b
where a.id=b.id
A.NAME,B.School
FROM A,B
WHERE RIGHT(A.NAME,1)=RIGHT(B.SCHOOL,1)
1 myname1
2 myname2
3 myname3
表B
ID School
1 myschool1
2 myschool2
3 myschool3
表A与B通过ID关联 ,希望得到结果
ID Name School
1 myname1 myschool1
2 myname2 myschool2
3 myname3 myschool3
表A 修改为AID Name
1 myname1
2 myname2
3 myname3
表B
BID AID School
1 1 myschool1
2 2 myschool2
3 3 myschool3然后
select Name,School from 表A inner join 表B
on 表A.AID=表B.AID就可以得出你的结果,3Q!
from a left join b
on a.id=b.id
select a.ID,Name,School from a,b where a.id=b.id
create table tablea(id int identity(1,1),name nvarchar(10))
insert tablea(name)
select 'myname1'
union all select 'myname2'
union all select 'myname3'
if object_id('tableb') is not null drop table tableb
create table tableb(id int identity(1,1),School nvarchar(10))
insert tableb(School)
select 'myschool1'
union all select 'myschool2'
union all select 'myschool3'School
myschool1
myschool2
myschool3
select a.name,b.school from tablea a,tableb b where a.id=b.id
if object_id('a') is not null
drop table a
go
create table a(id int identity(1,1),name nvarchar(10))
insert a(name)
select 'myname1'
union all select 'myname2'
union all select 'myname3'
go
if object_id('b') is not null
drop table b
go
create table b(id int identity(1,1),School nvarchar(10))
insert b(School)
select 'myschool1'
union all select 'myschool2'
union all select 'myschool3'
go
select a.id,name,school
from a left join b
on a.id=b.id
/*
id name school
----------- ---------- ----------
1 myname1 myschool1
2 myname2 myschool2
3 myname3 myschool3*/数据用用
create table tablea(id int identity(1,1),name nvarchar(10))
insert tablea(name)
select 'myname1'
union all select 'myname2'
union all select 'myname3'
if object_id('tableb') is not null drop table tableb
create table tableb(id int identity(1,1),School nvarchar(10))
insert tableb(School)
select 'myschool1'
union all select 'myschool2'
union all select 'myschool3'
--sql server 2000方法
select a.name,b.school from tablea a,tableb b where a.id=b.id
--SQL SERVER 2005/2008方法
select [name],
School
from(
select [name],id=row_number()over(order by getdate()) from tablea)a,
(select School,id=row_number()over(order by getdate()) from tableb)b
where a.id=b.id
select [name],
School
from(
select [name],id=rank()over(order by name) from tablea)a,
(select School,id=rank()over(order by school) from tableb)b
where a.id=b.id
School id
myschool1 1
myschool2 2
myschool3 3
drop table a
go
create table a ([name] varchar(20))
insert into a values('myname1')
insert into a values('myname2')
insert into a values('myname3')
if object_id('b') is not null
drop table b
go
create table b (School varchar(20))
insert into b values('myschool1')
insert into b values('myschool2')
insert into b values('myschool3')
-----------------------------
--2000
select c.[name],d.School from
(select [name],id=(select count(1) from a where [name]<=m.[name]) from a m) as c
join
(select School,id=(select count(1) from b where School<=n.School) from b n) as d
on c.id=d.id--2005 row_number()
select c.[name],d.School from
(select [name],id=row_number() over(order by [name]) from a) as c
join
(select School,id=row_number() over(order by School) from b) as d
on c.id=d.id
/*name School
-------------------- --------------------
myname1 myschool1
myname2 myschool2
myname3 myschool3(3 行受影响)*/
语法:RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注:如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。
例如,如果两位顶尖销售员具有同样的 SalesYTD 值,他们将并列第一。
由于已有两行排名在前,所以具有下一个最大 SalesYTD 的销售人员将排名第三。
因此,RANK 函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
参数:< partition_by_clause > :将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >:确定将 RANK 值应用于分区中的行时所基于的顺序。
返回类型:bigint示例:
/*以下示例按照数量对清单中的产品进行了排名。行集按 LocationID 分区,按 Quantity 排序。
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO
/*
ProductID Name LocationID Quantity RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1 Adjustable Race 6 324 71
1 Adjustable Race 1 408 78
1 Adjustable Race 50 353 117
2 Bearing Ball 6 318 67
2 Bearing Ball 1 427 85
2 Bearing Ball 50 364 122
3 BB Ball Bearing 50 324 106
3 BB Ball Bearing 1 585 110
3 BB Ball Bearing 6 443 115
4 Headset Ball Bearings 1 512 99
4 Headset Ball Bearings 6 422 108
4 Headset Ball Bearings 50 388 140
316 Blade 10 388 33
......
(1069 行受影响)
*/
drop table a
go
create table a ([name] varchar(20))
insert into a values('myname1')
insert into a values('myname2')
insert into a values('myname3')
if object_id('b') is not null
drop table b
go
create table b (School varchar(20))
insert into b values('myschool1')
insert into b values('myschool2')
insert into b values('myschool3')
-----------------------------
--或者在原来的表上进行修改
--增加自增列并设置为主键
alter table a add id int identity(1,1) primary key
alter table b add id int identity(1,1) primary keyselect [name],School from a join b on a.id=b.id
/*name School
-------------------- --------------------
myname1 myschool1
myname2 myschool2
myname3 myschool3(3 行受影响)*/
insert #a
select 'myname1','myschool1' union all
select 'myname2','myschool2' union all
select 'myname3','myschool3'select * from #a