用一条sql语句解决里面的问题
表company
cID CompanyName
1 公司1
2 公司名称
表companyAdd
dID cID Add
1 1 add1
2 1 add2
3 1 add3
4 2 add4
... ... ...
用一条sql语句怎么得出如下表格
CompanyName Add1 Add2 Add3
公司1 add1 add2 add3
公司2 add4 ... ...
[解决办法]
需要使用函数
[解决办法]
--如果add不定的話,需要動態語句
--測試環境:
create table company(cID int,companyname varchar(30))
insert into company
select 1, '公司1 ' union all
select 2, '公司2 '
create table companyAdd(dID int,cID int,[Add] varchar(20))
insert into companyAdd
select 1,1, 'add1 ' union all
select 2,1, 'add2 ' union all
select 3,1, 'add3 ' union all
select 4,2, 'add4 '
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',max(case when id= '+ltrim(id)+ ' then [Add] end) as [add '+ltrim(id)+ '] '
from
(select id=(select count(1) from companyAdd where dID <=A.dID and cID=A.cID),B.companyName,A.[Add]
from companyAdd A,company B where A.cId=B.cID) T
group by id
select @sql= 'select companyName '+@sql+ ' from (select id=(select count(1) from companyAdd where dID <=A.dID and cID=A.cID),B.companyName,A.[Add]
from companyAdd A,company B where A.cId=B.cID) T group by companyName '
exec(@sql)
/*
companyName add1 add2 add3
----------------------------------------------
公司1 add1 add2 add3
公司2 add4 NULL NULL
*/
drop table companyAdd,company
[解决办法]
固定的可以这样
select a.CompanyName,
Add1=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 0 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID) else ' ' end,
Add2=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 1 then (select top 1 [Add] from (select top 2 * from companyAdd b where b.cID=a.cID order by b.dID)t order by dID desc) else ' ' end,
Add3=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 2 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID desc) else ' ' end
from company a
------解决方案--------------------
drop table company,companyAdd
go
create table company(cID int,CompanyName varchar(20))
insert into company
select 1, '公司1 '
union all select 2, '公司2 '
create table companyAdd(dID int,cID int,[Add] varchar(20))
insert into companyAdd
select 1,1, 'add1 '
union all select 2,1, 'add2 '
union all select 3,1, 'add3 '
union all select 4,2, 'add4 '
select a.CompanyName,
Add1=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 0 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID) else ' ' end,
Add2=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 1 then (select top 1 [Add] from (select top 2 * from companyAdd b where b.cID=a.cID order by b.dID)t order by dID desc) else ' ' end,
Add3=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 2 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID desc) else ' ' end
from company a
/*
CompanyName Add1 Add2 Add3
-------------------- -------------------- -------------------- --------------------
公司1 add1 add2 add3
公司2 add4
（所影响的行数为 2 行）
*/
[解决办法]
if object_id( 'pubs..company ') is not null
drop table company
go
create table company(cID int,CompanyName varchar(10))
insert into company(cID,CompanyName) values(1, '公司1 ')
insert into company(cID,CompanyName) values(2, '公司2 ')
go
if object_id( 'pubs..companyAdd ') is not null
drop table companyAdd
go
create table companyAdd(dID int,cID int,[Add] varchar(10))
insert into companyAdd(dID,cID,[Add]) values(1, 1, 'add1 ')
insert into companyAdd(dID,cID,[Add]) values(2, 1, 'add2 ')
insert into companyAdd(dID,cID,[Add]) values(3, 1, 'add3 ')
insert into companyAdd(dID,cID,[Add]) values(4, 2, 'add4 ')
go
declare @sql varchar(8000)
set @sql = 'select CompanyName '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then [add] else ' ' ' ' end) [add ' + cast(px as varchar) + '] '
from (select distinct px from (select CompanyName , t.px , t.[add] from company , (select px=(select count(1) from companyAdd where cID=a.cID and dID <a.dID)+1 , * from companyAdd a) t where company.cid = t.cid) m) as a
set @sql = @sql + ' from (select CompanyName , t.px , t.[add] from company , (select px=(select count(1) from companyAdd where cID=a.cID and dID <a.dID)+1 , * from companyAdd a) t where company.cid = t.cid) m group by CompanyName '
exec(@sql)
drop table companyAdd
drop table company
/*
CompanyName add1 add2 add3
----------- ---------- ---------- ----------
公司1 add1 add2 add3
公司2 add4
*/
[解决办法]
--如果add是定的,可以直接一句SQL
select
companyname,
max(case when id=1 then [add] end) as add1,
max(case when id=2 then [add] end) as add2,
max(case when id=3 then [add] end) as add3
from
(select id=(select count(1) from companyAdd where dID <=A.dID and cID=A.cID),B.companyName,A.[Add]
from companyAdd A,company B where A.cId=B.cID) T
group by companyname
/*
companyname add1 add2 add3
------------------------------ -------------------- -------------------- ------------
公司1 add1 add2 add3
公司2 add4 NULL NULL
*/
