declare @a table (id int,name nvarchar(5))
insert into @a select 1,'a'
union all select 2,'b'
declare @b table (sex nvarchar(5))
insert into @b select '男'
union all select '女'
select a.id,a.name,b.sex from @a a join
(select row_number()over(order by sex) id,sex from @b
) b on a.id=b.id
---------------
select * ,identity(int,1,1) id into #a from @b
select a.id,a.name,b.sex from @a a join #a b
on a.id=b.id1 a 男
2 b 女
我是希望能够用一句查寻语句,不要用declare来做,不好意思哦~~
go
create table ta(id int, name varchar(10))
insert ta select 1 , 'a'
insert ta select 2, 'b'
if object_id('tb')is not null drop table tb
go
create table tb(sex varchar(10))
insert tb select '男'
insert tb select '女'
alter table tb
add id int identity
go
select ta.*,tb.sex from ta,tb where ta.id=tb.id
/*id name sex
----------- ---------- ----------
1 a 男
2 b 女(所影响的行数为 2 行)*/
SELECT a.id,a.name,b,sex FROM A a,B b WHERE a.id=b.id
ALTER table B drop column(id)
当一句话来用
没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。下面是 Transact-SQL 交叉联接示例:USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC结果集包含 184 行(authors 有 23 行,publishers 有 8 行;23 乘以 8 等于 184)。不过,如果添加一个 WHERE 子句,则交叉联接的作用将同内联接一样。例如,下面的 Transact-SQL 查询得到相同的结果集:USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
WHERE authors.city = publishers.city
ORDER BY au_lname DESC— 或
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors INNER JOIN publishers
ON authors.city = publishers.city
ORDER BY au_lname DESC