SELECT A.工号,B.名称,B.规格 FROM tb1 AS A CROSS JOIN tb2 AS B
---2005还可以这样 ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-09-27 20:50:41 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[T1] if object_id('[T1]') is not null drop table [T1] go create table [T1]([工号] int,[姓名] varchar(2)) insert [T1] select 1001,'张' union all select 1002,'李' --> 测试数据:[T2] if object_id('[T2]') is not null drop table [T2] go create table [T2]([名称] varchar(2),[规格] varchar(3)) insert [T2] select '茶','1杯' union all select '酒','1瓶' --------------开始查询-------------------------- select a.工号,b.名称,b.规格 from t1 a cross apply t2 b ----------------结果---------------------------- /* 工号 名称 规格 ----------- ---- ---- 1001 茶 1杯 1002 茶 1杯 1001 酒 1瓶 1002 酒 1瓶(4 行受影响) */
--排个序 ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-09-27 20:50:41 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[T1] if object_id('[T1]') is not null drop table [T1] go create table [T1]([工号] int,[姓名] varchar(2)) insert [T1] select 1001,'张' union all select 1002,'李' --> 测试数据:[T2] if object_id('[T2]') is not null drop table [T2] go create table [T2]([名称] varchar(2),[规格] varchar(3)) insert [T2] select '茶','1杯' union all select '酒','1瓶' --------------开始查询-------------------------- select a.工号,b.名称,b.规格 from t1 a cross apply t2 b order by 1 ----------------结果---------------------------- /*工号 名称 规格 ----------- ---- ---- 1001 茶 1杯 1001 酒 1瓶 1002 茶 1杯 1002 酒 1瓶(4 行受影响)*/
select a.工号,b.名称,b.规格 from t1 a full join t2 b on 1 =1
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-27 20:50:41
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([工号] int,[姓名] varchar(2))
insert [T1]
select 1001,'张' union all
select 1002,'李'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([名称] varchar(2),[规格] varchar(3))
insert [T2]
select '茶','1杯' union all
select '酒','1瓶'
--------------开始查询--------------------------
select a.工号,b.名称,b.规格 from t1 a cross apply t2 b
----------------结果----------------------------
/* 工号 名称 规格
----------- ---- ----
1001 茶 1杯
1002 茶 1杯
1001 酒 1瓶
1002 酒 1瓶(4 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-27 20:50:41
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([工号] int,[姓名] varchar(2))
insert [T1]
select 1001,'张' union all
select 1002,'李'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([名称] varchar(2),[规格] varchar(3))
insert [T2]
select '茶','1杯' union all
select '酒','1瓶'
--------------开始查询--------------------------
select a.工号,b.名称,b.规格 from t1 a cross apply t2 b order by 1
----------------结果----------------------------
/*工号 名称 规格
----------- ---- ----
1001 茶 1杯
1001 酒 1瓶
1002 茶 1杯
1002 酒 1瓶(4 行受影响)*/
这样也行