二、实例应用
1、说明：复制表法一：select * into b from a where 1<>1（仅用于SQlServer）
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int)
insert [a]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select null
select * from a
/*
(5 行受影响)
ID
-----------
1
1
2
3
NULL
(5 行受影响)
*/
--只复制表结构
select * into b from a where 1<>1
select * from b
/*
ID
-----------
(0 行受影响)
*/
法二：select top 0 * into b from a
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int)
insert [a]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select null
select * from a
/*
(5 行受影响)
ID
-----------
1
1
2
3
NULL
(5 行受影响)
*/
--只复制表结构
select top 0 * into b from a
select * from b
/*
ID
-----------
(0 行受影响)
*/
2、说明：拷贝表(拷贝数据,源表名：
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int)
insert [a]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select null
select * from a
/*
(5 行受影响)
ID
-----------
1
1
2
3
NULL
(5 行受影响)
*/
--复制表数据
create table [b]([ID] int)
insert into b(id) select id from a
select * from b
/*
ID
-----------
1
1
2
3
NULL
(5 行受影响)
*/
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int)
insert [a]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select null
select * from a
/*
(5 行受影响)
ID
-----------
1
1
2
3
NULL
(5 行受影响)
*/
--复制表数据
create table [b]([ID] int)
insert into b(id) select id from cc_jz.dbo.a
select * from b
/*
ID
-----------
1
1
2
3
NULL
(5 行受影响)
*/
4、说明：子查询(表名1：a 表名2：b)
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int)
insert [a]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select null
select * from a
/*
(5 行受影响)
ID
-----------
1
1
2
3
NULL
(5 行受影响)
*/
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int)
insert [b]
select 1 union all
select 2 union all
select 2 union all
select 4 union all
select null
select * from b
/*
(5 行受影响)
ID
-----------
1
2
2
4
NULL
(5 行受影响)
*/
select * from a where id in (select id from b )
/*
ID
-----------
1
1
2
3
(4 行受影响)
*/
5、说明：显示品名、数量和最后入库时间
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert [a]
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
select a.[品名],a.[入库数量],b.[最后入库时间] from [test] a ,
(select [品名],max([入库时间]) as '最后入库时间' from [test] group by [品名]) b
where a.[品名]=b.[品名]
/*
品名 入库数量 最后入库时间
------ ----------- -----------------------
方便面 60 2013-01-05 00:00:00.000
方便面 50 2013-01-05 00:00:00.000
方便面 50 2013-01-05 00:00:00.000
矿泉水 100 2013-01-04 00:00:00.000
矿泉水 80 2013-01-04 00:00:00.000
(5 行受影响)
*/
6、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
--> 测试数据：[a]
if object_id('