一个连接查询语句

Question

--订单表
CREATE TABLE [T_Quote](
[QuoteID] [int] IDENTITY(1,1) PRIMARY KEY,
[QuoteNumber] [varchar](20)  -- 订单号
)
[QuoteID] [QuoteNumber]
   1        qn1    
   2        qn2
   3        qn3--订单详情表
CREATE TABLE [T_QuoteDetails](
[QuoteDetailsID] [int] IDENTITY(1,1)PRIMARY KEY,
[QuoteNumber] [varchar](20) , --订单号
[PartNumber] [varchar](25)    --产品名
)[QuoteDetailsID] [QuoteNumber] [PartNumber]
       1             qn1           pn1
       2             qn1           pn2
       3             qn2           pn1
       4             qn2           pn3
       5             qn3           pn4--产品表
CREATE TABLE [T_Product](
[ProductID] [int] IDENTITY(1,1) PRIMARY KEY,
[PartNumber] [varchar](25) UNIQUE  -- 产品名
)[ProductID] [PartNumber]
     1          pn1
     2          pn2
     3          pn3
     4          pn4
--产品文件关联表(产品和文件多对多的关系,Mark为1的记录表示,产品的默认文件;为0的记录表示,产品的一般文件)
CREATE TABLE [T_ProductFile_R](
[ID] [int] IDENTITY(1,1) PRIMARY KEY,
[ProductID] [int] ,
[AttachID] [int] ,
[Mark] [int] DEFAULT ('0') 
)
[ID] [ProductID] [AttachID] [Mark]
 1       1           1         0
 2       1           2         1
 3       3           1         0
--文件表
CREATE TABLE [T_ProductFileAttach](
[AttachID] [int] IDENTITY(1,1)PRIMARY KEY,
[FileName] [varchar](100)  -- 文件名
)[AttachID] [FileName]
    1          f1
    2          f2
    3          f3
/*当给出一个订单号QuoteNumber时,查询定单详情的同时,显示每个产品的默认文件名,如果没有则显示为空,输出的结果如下:*/QuoteNumber = 'qn1'
[QuoteDetailsID] [QuoteNumber] [PartNumber] [FileName]
       1             qn1           pn1        
       2             qn1           pn2          f2QuoteNumber = 'qn2'
[QuoteDetailsID] [QuoteNumber] [PartNumber] [FileName]
       3             qn2           pn1
       4             qn2           pn3          
QuoteNumber = 'qn3'
[QuoteDetailsID] [QuoteNumber] [PartNumber] [FileName]
       5             qn3           pn4这条语句怎么写呢?

Answer 1

select a.QuoteDetailsID,a.QuoteNumber,a.PartNumber,case when Mark = 1 then d.FileName else '' end
from [T_QuoteDetails] a
left join [T_Product] b on a.PartNumber = b.PartNumber
left join [T_ProductFile_R]c on b.ProductID = c.ProductID
left join [T_ProductFileAttach]d on c.AttachID = d.AttachID
where a.QuoteNumber = '??'

Answer 2

select a.QuoteDetailsID,a.QuoteNumber,a.PartNumber,case when Mark = 1 then d.FileName else '' end 
from [T_QuoteDetails] a 
left join [T_Product] b on a.PartNumber = b.PartNumber 
left join [T_ProductFile_R]c on b.ProductID = c.ProductID 
left join [T_ProductFileAttach]d on c.AttachID = d.AttachID 
where a.QuoteNumber = 'qn1' 

Answer 3

查出来会有重复的记录,而且用了distinct也只能去处一部分的重复数据.
那些1个产品下关联多个文件的(有默认也有一般),都会出现在结果里.

Answer 4

多个表的连接查询
select a.QuoteDetailsID,a.QuoteNumber,a.PartNumber,
case c.Mark when 1 then d.FileName else '' end as FileName
from T_QuoteDetails a 
left join T_Product b on a.PartNumber = b.PartNumber 
left join T_ProductFile_R c on b.ProductID = c.ProductID 
left join T_ProductFileAttach d on c.AttachID = d.AttachID 
where a.QuoteNumber = 'qn1'

Answer 5

select a.QuoteDetailsID,a.QuoteNumber,a.PartNumber,
case c.Mark when 1 then d.FileName else '' end as FileName
from T_QuoteDetails a 
left join T_Product b on a.PartNumber = b.PartNumber 
left join T_ProductFile_R c on b.ProductID = c.ProductID 
left join T_ProductFileAttach d on c.AttachID = d.AttachID 
where c.Mark=1   --加个条件
and a.QuoteNumber = 'qn1'

Answer 6

这是在我本机上查询的结果. 
PartNumber为'ADVC-110'的产品下有3个文件,但都只是一般文件(为0),查询结果的这条记录会显示3次
PartNumber为'VLPTWDVD'的产品下有2个文件,一个是默认文件,一个是一般文件,查询结果的这条记录会显示2次(其中一条有x1.xls是正确的记录)2623 MDW08103004 ADVC-110
2623 MDW08103004 ADVC-110
2623 MDW08103004 ADVC-110
2624 MDW08103004 DSKTP
2625 MDW08103004 T-38174
2626 MDW08103004 UPG500Gig
2627 MDW08103004 T-46872
2629 MDW08103004 LPTWDVD
2630 MDW08103004 VLPTWDVD  
2630 MDW08103004 VLPTWDVD      x1.xls
2631 MDW08103004 41C9170
2631 MDW08103004 41C9170       x2.xls
2632 MDW08103004 DSKTPCS --下面是我希望得到的结果.2623 MDW08103004 ADVC-110
2624 MDW08103004 DSKTP
2625 MDW08103004 T-38174
2626 MDW08103004 UPG500Gig
2627 MDW08103004 T-46872
2629 MDW08103004 LPTWDVD
2630 MDW08103004 VLPTWDVD      x1.xls
2631 MDW08103004 41C9170       x2.xls
2632 MDW08103004 DSKTPCS

Answer 7

create proc test_p
(
  @订单号  varchar(20)
)
as
set nocount on 
select a.quotedetailsid,a.quotenumber,b.partnumber,d.filename
 from t_quotedetails a left join t_product b on a.partnumber=b.partnumber
  left join t_productfile_r c on b.productid=c.productid
  left join t_productfileattact d on c.attachid=d.attachid
where a.quotenumber = @订单号 
set nocount off
go
exect text_p '订单号'