表AUID name
1 小红
2 小刚
3 小明
4 小兰表BID groupname UID1 UID2
1 无敌二人组 2 3
2 无视二人组 1 4我要得表B所有数据,但是uid1,uid2要对应表a的真名,如:groupname name1 name2
无敌二人组 小刚 小明
无视二人组 小红 小兰
1 小红
2 小刚
3 小明
4 小兰表BID groupname UID1 UID2
1 无敌二人组 2 3
2 无视二人组 1 4我要得表B所有数据,但是uid1,uid2要对应表a的真名,如:groupname name1 name2
无敌二人组 小刚 小明
无视二人组 小红 小兰
from A A1,A A2,B
where A1.uid=B.uid1 and A2.uid2=B.uid2
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([UID] int,[name] varchar(4))
insert [ta]
select 1,'小红' union all
select 2,'小刚' union all
select 3,'小明' union all
select 4,'小兰'
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[groupname] varchar(10),[UID1] int,[UID2] int)
insert [tb]
select 1,'无敌二人组',2,3 union all
select 2,'无视二人组',1,4--------------------------------查询开始------------------------------select t.[groupname],a.[name],b.[name] from [tb] t
left join [ta] a on t.[UID1]=a.[UID]
left join [ta] b on t.[UID2]=b.[UID]/*
groupname name name
---------- ---- ----
无敌二人组 小刚 小明
无视二人组 小红 小兰(2 行受影响)*/
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.a') is not null drop table a
-- 创建数据表
create table a
(
UID int,
name varchar(7)
)
go
--插入测试数据
insert into a select 1,'小红'
union all select 2,'小刚'
union all select 3,'小明'
union all select 4,'小兰'
go
if object_id('test.dbo.b') is not null drop table b
-- 创建数据表
create table b
(
ID int,
groupname char(11),
UID1 varchar(7),
UID2 varchar(7)
)
go
--插入测试数据
insert into b select 1,'无敌二人组',2,3
union all select 2,'无视二人组',1,4
go
--代码实现update b set UID1=x.name,UID2=y.name from a x,a y,b where b.UID1=x.UID and b.UID2=y.UIDselect * from b/*测试结果ID groupname UID1 UID2
---------------------------------------
1 无敌二人组 小刚 小明
2 无视二人组 小红 小兰(2 行受影响)
*/
FROM 表B INNER JOIN 表A a1 ON 表B.UID1 = a1.UID
INNER JOIN 表A a2 ON 表B.UID2 = a2.UID
go
if object_id('test.dbo.a') is not null drop table a
-- 创建数据表
create table a
(
UID int,
name varchar(7)
)
go
--插入测试数据
insert into a select 1,'小红'
union all select 2,'小刚'
union all select 3,'小明'
union all select 4,'小兰'
go
if object_id('test.dbo.b') is not null drop table b
-- 创建数据表
create table b
(
ID int,
groupname char(11),
UID1 varchar(7),
UID2 varchar(7)
)
go
--插入测试数据
insert into b select 1,'无敌二人组',2,3
union all select 2,'无视二人组',1,4
go
--代码实现select b.ID,b.groupname,UID1=x.name,UID2=y.name from a x,a y,b where b.UID1=x.UID and b.UID2=y.UID/*测试结果ID groupname UID1 UID2
--------------------------------------
1 无敌二人组 小刚 小明
2 无视二人组 小红 小兰(2 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-10 10:48:59
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([UID] [int],[name] [nvarchar](10))
INSERT INTO [A]
SELECT '1','小红' UNION ALL
SELECT '2','小刚' UNION ALL
SELECT '3','小明' UNION ALL
SELECT '4','小兰'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([ID] [int],[groupname] [nvarchar](10),[UID1] [int],[UID2] [int])
INSERT INTO [B]
SELECT '1','无敌二人组','2','3' UNION ALL
SELECT '2','无视二人组','1','4'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test]
(@Uid int)
RETURNS VARCHAR(20)
AS
BEGIN
RETURN(SELECT name FROM A WHERE [UID]=@Uid)
END
GO
SELECT groupname ,dbo.fn_test([UID1]) name1 ,dbo.fn_test([UID2])name2
FROM B
/*
groupname name1 name2
---------- -------------------- --------------------
无敌二人组 小刚 小明
无视二人组 小红 小兰(2 行受影响)
*/