22,209
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('#tempdb..#table1') is not null
drop table #table1
if OBJECT_ID('#tempdb..#table2') is not null
drop table #table2
select IDENTITY(int,1,1) id,* into #table1
from table1
select IDENTITY(int,1,1) id,* into #table2
from table2
select tcid,公司名称,a.性别,备注,listid,联系人姓名,单位,状态
from #table1 a
inner join #table2 b
on a.id = b.id
[/quote]
啊,对啊。我怎么就没有想到用这个!
要怎么给分呢?[/quote]我被无视了?if OBJECT_ID('#tempdb..#table1') is not null
drop table #table1
if OBJECT_ID('#tempdb..#table2') is not null
drop table #table2
select IDENTITY(int,1,1) id,* into #table1
from table1
select IDENTITY(int,1,1) id,* into #table2
from table2
select tcid,公司名称,a.性别,备注,listid,联系人姓名,单位,状态
from #table1 a
inner join #table2 b
on a.id = b.id
[/quote]
啊,对啊。我怎么就没有想到用这个!
要怎么给分呢?[/quote]
这样结贴,然后给分:
if OBJECT_ID('#tempdb..#table1') is not null
drop table #table1
if OBJECT_ID('#tempdb..#table2') is not null
drop table #table2
select IDENTITY(int,1,1) id,* into #table1
from table1
select IDENTITY(int,1,1) id,* into #table2
from table2
select tcid,公司名称,a.性别,备注,listid,联系人姓名,单位,状态
from #table1 a
inner join #table2 b
on a.id = b.id
if OBJECT_ID('#tempdb..#table1') is not null
drop table #table1
if OBJECT_ID('#tempdb..#table2') is not null
drop table #table2
select IDENTITY(int,1,1) id,* into #table1
from table1
select IDENTITY(int,1,1) id,* into #table2
from table2
select tcid,公司名称,a.性别,备注,listid,联系人姓名,单位,状态
from #table1 a
inner join #table2 b
on a.id = b.id
[/quote]
啊,对啊。我怎么就没有想到用这个!
要怎么给分呢?----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-03-03 07:43:59
-- 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: )
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([tcid] int,[公司名称] varchar(1),[性别] varchar(1),[备注] int)
insert [table1]
select 1,'a','n',123 union all
select 3,'b','f',256
--> 测试数据:[talbe2]
if object_id('[talbe2]') is not null drop table [talbe2]
go
create table [talbe2]([listid] int,[联系人姓名] varchar(1),[单位] varchar(1),[状态] int,[性别] varchar(1))
insert [talbe2]
select 5,'v','f',145,'n' union all
select 1,'d','h',178,'f'
--------------开始查询--------------------------
SELECT a.* ,
b.listid ,
b.联系人姓名 ,
b.单位 ,
b.状态
FROM [table1] a
INNER JOIN [talbe2] b ON a.性别 = b.性别
----------------结果----------------------------
/*
tcid 公司名称 性别 备注 listid 联系人姓名 单位 状态
----------- ---- ---- ----------- ----------- ----- ---- -----------
1 a n 123 5 v f 145
3 b f 256 1 d h 178
*/
select tcid,公司名称,a.性别,备注,listid,联系人姓名,单位,状态
from
(
select *,ROW_NUMBER() over(order by getdate()) rownum
from table1
)a
inner join
(
select *,ROW_NUMBER() over(order by getdate()) rownum
from table2
)b
on a.rownum = b.rownum