三个表的关联查询

[sdmc01]

T1 表1中的code值在表２的code值含有的情况下，并且pak重复结果只取1条,才查询出T2.pak,T3.sale

code aa
001 8
002 6
003 9

T2

pak  code
A1 001
A2 002
A2 003
A3 004
A4 003
A4 006

T3

code sale
001 8
002 6
003 9
A1 160
A2 180
A3 170
A4 190

查询结果
A1 160
A2 170
A4 190
-----谢谢!

[zhouyuehai1978]

--测试数据
IF NOT OBJECT_ID(N'Tempdb..#T1') IS NULL
    DROP TABLE #T1
IF NOT OBJECT_ID(N'Tempdb..#T2') IS NULL
    DROP TABLE #T2
IF NOT OBJECT_ID(N'Tempdb..#T3') IS NULL
    DROP TABLE #T3        
GO
CREATE TABLE #T1
(
	code  VARCHAR(20),
	aa     VARCHAR(20)
)
INSERT #T1
SELECT '001','8'UNION ALL
SELECT '002','6'UNION ALL
SELECT '003','9'
CREATE TABLE #T2
(
	pak  VARCHAR(20),
	code     VARCHAR(20)
)
INSERT #T2
SELECT 'A1','001'UNION ALL
SELECT 'A2','002'UNION ALL
SELECT 'A2','003'UNION ALL
SELECT 'A3','004'UNION ALL
SELECT 'A4','003'UNION ALL
SELECT 'A4','006'
CREATE TABLE #T3
(
	Code  VARCHAR(20),
	sale     VARCHAR(20)
)
INSERT #T3
SELECT '001','8' UNION ALL
SELECT '002','6' UNION ALL
SELECT '003','9' UNION ALL
SELECT 'A1','160' UNION ALL
SELECT 'A2','180' UNION ALL
SELECT 'A3','170' UNION ALL
SELECT 'A4','190' 
GO
--测试数据结束
SELECT * FROM   #T1 AS t1
SELECT * FROM   #T2 AS t2
SELECT * FROM   #T3 AS t3;

SELECT t3.*
FROM   (
           SELECT DISTINCT t2.pak
           FROM   #T1       AS t
                  JOIN #T2  AS t2
                       ON  t.code = t2.code
       ) a
       JOIN #T3 AS t3
            ON  t3.Code = a.pak

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([code] nvarchar(23),[aa] int)
Insert #T1
select N'001',8 union all
select N'002',6 union all
select N'003',9
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([pak] nvarchar(22),[code] nvarchar(23))
Insert #T2
select N'A1',N'001' union all
select N'A2',N'002' union all
select N'A2',N'003' union all
select N'A3',N'004' union all
select N'A4',N'003' union all
select N'A4',N'006'
GO
if not object_id(N'Tempdb..#T3') is null
	drop table #T3
Go
Create table #T3([code] nvarchar(23),[sale] int)
Insert #T3
select N'001',8 union all
select N'002',6 union all
select N'003',9 union all
select N'A1',160 union all
select N'A2',180 union all
select N'A3',170 union all
select N'A4',190
Go
--测试数据结束
SELECT  c.*
FROM    #T3 c
        JOIN ( SELECT   a.code ,
                        MAX(pak) AS pak
               FROM     #T1 a
                        JOIN #T2 b ON b.code = a.code
               GROUP BY a.code
             ) t ON t.pak = c.code

[顺势而为1]

if not object_id(N'Tempdb..#T1') is null
    drop table #T1
Go

Create table #T1(
        code varchar(10),
        aa int)
GO         

Insert #T1
Select '001',8 union all
Select '002', 6 union all
Select '003', 9


if not object_id(N'Tempdb..#T2') is null
    drop table #T2
Go

Create table #T2(
        Pak varchar(10),
        code varchar(10))
GO         

Insert #T2
Select 'A1','001' union all
Select 'A2', '002' union all
Select 'A2', '003' union all
Select 'A3', '004' union all
Select 'A4', '003' union all
Select 'A4', '006' 

if not object_id(N'Tempdb..#T3') is null
    drop table #T3
Go

Create table #T3(
        code varchar(10),
        sale int)
GO         

Insert #T3
Select '001',8 union all
Select '002', 6 union all
Select '003', 9 union all
Select 'A1', 160 union all
Select 'A2', 180 union all
Select 'A3', 170 union all
Select 'A4', 190 

--T1  表1中的code值在表２的code值含有的情况下，并且pak重复结果只取1条,才查询出T2.pak,T3.sale


Select x.Pak,sale
From (Select * From #T2 a Where code=(Select MIN(code) From #t2 b where b.pak=a.pak) AND code in (Select code From #T1)) x
Join (Select A.code,sale,aa From #T3 a Left Join #T1 b on a.code=b.code) y on x.Pak=y.code

[sinat_38835885]

declare @A table(code varchar(10),aa varchar(10))
insert into @A
select '001','8' union all
select '002','9' union all
select '003','10'

declare @B table(pak varchar(10),code varchar(10))
insert into @B
select 'A1','001' union all
select 'A2','002' union all
select 'A2','003' union all
select 'A3','004' union all
select 'A4','003' union all
select 'A4','006'

declare @C table(code varchar(10),sale varchar(10))
insert into @C
select '001','8' union all
select '002','6' union all
select '003','9' union all
select 'A1','160' union all
select 'A2','180' union all
select 'A3','170' union all
select 'A4','190'

select * from @C c where code in (select distinct b.pak from @A a,@B b where a.code=b.code)

[ohyeah_16888]

[疯狂宝贝]

您好，请问“sql2005数据库可疑,按小F的处理方法,有不允许对系统目录进行即席更新'的问题” 这个问题您解决了吗？我也遇到同样的问题