---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-12-25 17:18:55 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([regname] varchar(2)) insert [A] select 'A1' union all select 'A2' if object_id('[B]') is not null drop table [B] go create table [B]([dept] varchar(2)) insert [B] select 'B1' union all select 'B2' --------------开始查询--------------------------select * from a cross join b ORDER BY regname ----------------结果---------------------------- /* regname dept ------- ---- A1 B1 A1 B2 A2 B1 A2 B2 */
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-25 17:18:55
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([regname] varchar(2))
insert [A]
select 'A1' union all
select 'A2'
if object_id('[B]') is not null drop table [B]
go
create table [B]([dept] varchar(2))
insert [B]
select 'B1' union all
select 'B2'
--------------开始查询--------------------------select *
from a cross join b
ORDER BY regname
----------------结果----------------------------
/*
regname dept
------- ----
A1 B1
A1 B2
A2 B1
A2 B2
*/
create table #A (
Aname varchar(50)
)
insert #A
select 'A1' union
select 'A2'create table #B(
Bdepart varchar(50)
)
insert #B
select 'B1' union
select 'B2'
select *
from #A , #B
ORDER BY Aname结果
A1 B1
A1 B2
A2 B1
A2 B2
create table #A (
Aname varchar(50)
)
insert #A
select 'A1' union
select 'A2'create table #B(
Bdepart varchar(50)
)
insert #B
select 'B1' union
select 'B2'
select *
from #A
inner join #B
on 1=1
ORDER BY Aname
/*
Aname Bdepart
A1 B1
A1 B2
A2 B1
A2 B2
*/
这个cross join 特指进行笛卡尔积,就是交叉连接,能满足你的需求,对你这种特定的需求来说是没有什么缺陷的。只是不要在一般的sql 语句中用这个cross join
select * from member right join (select * from a cross join b) c on a.id=c.reg_id