34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-11 13:10:18
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([UserID] varchar(2),[CompanyID] varchar(2))
insert [a]
select 'u1','c1' union all
select 'u2','c1' union all
select 'u3','c2' union all
select 'u4','c2'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ConsignID] varchar(2),[DealID] varchar(2))
insert [b]
select 'c1','c2'
--------------开始查询--------------------------
select
a1.UserID, a2.UserID
from
b
join a a1 on
a1.CompanyID=b.ConsignID
join a a2 on
a2.CompanyID=b.DealID
----------------结果----------------------------
/* UserID UserID
------ ------
u1 u3
u1 u4
u2 u3
u2 u4
(4 行受影响)
*/
create view c (ConsignUserID, DealUserID)
as
select a1.UserID, a2.UserID
from b join a a1 on a1.CompanyID=b.ConsignID
join a a2 on a2.CompanyID=b.DealID
select ConsignUserID=k.userid,DealUserID=p.userid
from (select distinct userid from a join b on a.companyid=b.Consignid) k
,(select distinct userid from a join b on a.companyid=b.DealID) p