SQL 多表查询问题

Question

AccessOry：
AccessOryID    AccessOryName
20110001       服务
20110002       效率
20110003       质量Complain：
ComplainID     ComplainName
20110010       订单投诉
20110011       其他投诉AccessOryDetail：
AccessOryID    ComplainID
20110001       20110010
20110002       20110010
20110003       20110011我想根据这三张表获取一个数据列表：
ComplainID     ComplainName    AccessOryName
20110010       订单投诉        服务,效率
20110011       其他投诉        质量

Answer 1

select * from AccessOry as a,Complain as c,AccessOryDetail as aod where a.AccessOryID = aod.AccessOryID and c.ComplainID = ComplainID

Answer 2

create table AccessOry(AccessOryID varchar(10),AccessOryName varchar(10))
insert into AccessOry select '20110001','服务'
insert into AccessOry select '20110002','效率'
insert into AccessOry select '20110003','质量'
create table Complain(ComplainID varchar(10),ComplainName varchar(10))
insert into Complain select '20110010','订单投诉'
insert into Complain select '20110011','其他投诉'
create table AccessOryDetail(AccessOryID varchar(10),ComplainID varchar(10))
insert into AccessOryDetail select '20110001','20110010'
insert into AccessOryDetail select '20110002','20110010'
insert into AccessOryDetail select '20110003','20110011'
go
select a.ComplainID,b.ComplainName,
stuff((select ','+t1.AccessOryName from AccessOry t1 inner join AccessOryDetail t2 on t1.AccessOryId=t2.AccessOryId where t2.ComplainID=a.ComplainID for xml path('')),1,1,'')AccessOryName
from AccessOryDetail a inner join Complain b on a.ComplainID=b.ComplainID 
group by a.ComplainID,b.ComplainName
/*
ComplainID ComplainName AccessOryName
---------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20110010   订单投诉         服务,效率
20110011   其他投诉         质量(2 行受影响)*/
go
drop table AccessOry,Complain,AccessOryDetail

Answer 3

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-15 14:57:48
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
-- Apr 22 2011 11:57:00 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[AccessOry]
if object_id('[AccessOry]') is not null drop table [AccessOry]
go 
create table [AccessOry]([AccessOryID] int,[AccessOryName] varchar(4))
insert [AccessOry]
select 20110001,'服务' union all
select 20110002,'效率' union all
select 20110003,'质量'
--> 测试数据：[Complain]
if object_id('[Complain]') is not null drop table [Complain]
go 
create table [Complain]([ComplainID] int,[ComplainName] varchar(8))
insert [Complain]
select 20110010,'订单投诉' union all
select 20110011,'其他投诉'
--> 测试数据：[AccessOryDetail]
if object_id('[AccessOryDetail]') is not null drop table [AccessOryDetail]
go 
create table [AccessOryDetail]([AccessOryID] int,[ComplainID] int)
insert [AccessOryDetail]
select 20110001,20110010 union all
select 20110002,20110010 union all
select 20110003,20110011
--------------开始查询--------------------------
;with f as
(
select
  a.*,c.ComplainID,b.ComplainName
from
  AccessOry a join AccessOryDetail c
on
  a.AccessOryID=c.AccessOryID
join
  Complain b
on
  b.ComplainID=c.ComplainID
)
select
 ComplainID,ComplainName,[AccessOryName ]=stuff((select ','+[AccessOryName ] from f where ComplainID=t.ComplainID for xml path('')), 1, 1, '') 
from
 f t
group by
 ComplainID,ComplainName 
----------------结果----------------------------
/*ComplainID  ComplainName AccessOryName 
----------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20110010    订单投诉         服务,效率
20110011    其他投诉         质量(2 行受影响)
 
*/

Answer 4

if object_id('AccessOry','U') is not null
   drop table AccessOry
go
create table AccessOry
(
 AccessOryID varchar(10),
 AccessOryName varchar(10)
)
go
insert into AccessOry
select '20110001','服务' union all
select '20110002','效率' union all
select '20110003','质量'
go
if object_id('Complain','U') is not null
   drop table Complain
go
create table Complain
(
 ComplainID varchar(10),
 ComplainName varchar(10)
)
go
insert into Complain
select '20110010','订单投诉' union all
select '20110011','其他投诉'
go
if object_id('AccessOryDetail','U') is not null
   drop table AccessOryDetail
go
create table AccessOryDetail
(
 AccessOryID varchar(10),
 ComplainID varchar(10)
)
go
insert into AccessOryDetail
select '20110001','20110010' union all
select '20110002','20110010' union all
select '20110003','20110011'
go
with cte as
(
select a.ComplainID,ComplainName,AccessOryName
from AccessOryDetail a
     inner join AccessOry b on a.AccessOryID=b.AccessOryID
     inner join Complain c on a.ComplainID=c.ComplainID
)
select ComplainID,ComplainName,
       AccessOryName=stuff((select ','+AccessOryName from cte where ComplainID=t1.ComplainID for xml path('')),1,1,'')
from cte t1 group by ComplainID,ComplainName
/*
ComplainID ComplainName AccessOryName
---------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20110010   订单投诉         服务,效率
20110011   其他投诉         质量(2 行受影响)
*/

Answer 5

2005及以上版本请使用STUFF函数。