**************很难哦***************

einsteincao 2004-09-20 04:35:30
如何写出如下条件的Query

FS_VATCustomerCode:

id VATCustomerCode
1 John
2 Mike
3 Jully



FS_VATProductCode:

id VATProductCode
1 Bike
2 Car
3 Tractor




FS_VATProductCustomerCombination:

VATCustomerCode VATProductCode Tax
John Bike 2
John Car 500
Mike Tractor 149
Jully Bike 2
Jully Car 388
Jully Tractor 149




希望用一个表变量,实现如下结果集:


ColumnA Column1 Column2 Column3
-------------------------------------------------------------------------
ProductCode John Mike Jully
Bike 2 2
Car 500 388
Tractor 149 149

...全文
145 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
Andy__Huang 2004-09-20
  • 打赏
  • 举报
回复
哦!結果應該改一點

ProductCode John Mike Jully
------------------------------------------
Bike 2 NULL 2
Car 500 NULL 388
Tractor NULL 149 149
Andy__Huang 2004-09-20
  • 打赏
  • 举报
回复
create table tb1( id int,VATCustomerCode varchar(100))
Insert into tb1
select '1','John'
union all select '2','Mike'
union all select '3','Jully'

create table tb2(id int,VATProductCode varchar(100))
Insert into tb2
select '1','Bike'
union all select '2','Car'
union all select '3','Tractor'

create table tb3(VATCustomerCode varchar(100),VATProductCode varchar(100),Tax varchar(10))
Insert into tb3
select 'John','Bike','2'
union all select 'John','Car','500'
union all select 'Mike','Tractor','149'
union all select 'Jully','Bike','2'
union all select 'Jully','Car','388'
union all select 'Jully','Tractor','149'

--刪除
drop table tb1
drop table tb2
drop table tb3

--結果
select a.VATProductCode,
John=(select Tax from tb3 inner join tb1 on tb1.VATCustomerCode =tb3.VATCustomerCode
where tb3.VATProductCode=a.VATProductCode and tb3.VATCustomerCode ='John')
,Mike=(select Tax from tb3 inner join tb1 on tb1.VATCustomerCode =tb3.VATCustomerCode
where tb3.VATProductCode=a.VATProductCode and tb3.VATCustomerCode ='Mike')
,Jully=(select Tax from tb3 inner join tb1 on tb1.VATCustomerCode =tb3.VATCustomerCode
where tb3.VATProductCode=a.VATProductCode and tb3.VATCustomerCode ='Jully')
from tb2 a

John Mike Jully
------------------------------------------
Bike 2 NULL 2
Car 500 NULL 388
Tractor NULL 149 149
zanglinfeng 2004-09-20
  • 打赏
  • 举报
回复
UP
zjcxc 元老 2004-09-20
  • 打赏
  • 举报
回复
--这就是动态的,但这个表变量就只在exec中有效,所以是没有意义的事

declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''
select @s1=@s1+',Column'+cast(id as varchar)+' varchar(20)' --定义表结构信息
,@s2=@s2+','''+VATCustomerCode+'''' --表头信息
,@s3=@s3+',max(case b.VATCustomerCode when '''
+VATCustomerCode+''' then Tax else '''' end)'
from FS_VATCustomerCode order by id
exec('
--定义表变量
declare @tb table(ColumnA varchar(20)'+@s1+')

--插入表头数据到表变量
insert @tb
select ''ProductCode'''+@s2+'

--插入统计数据到表变量
insert @tb
select b.VATProductCode'+@s3+'
from FS_VATProductCode a,FS_VATProductCustomerCombination b
where a.VATProductCode=b.VATProductCode
group by b.VATProductCode,a.id
order by a.id

--显示出结果
select * from @tb')
zjcxc 元老 2004-09-20
  • 打赏
  • 举报
回复
--示例

--示例数据
create table FS_VATCustomerCode(id int,VATCustomerCode varchar(20))
insert FS_VATCustomerCode select 1,'John'
union all select 2,'Mike'
union all select 3,'Jully'

create table FS_VATProductCode(id int,VATProductCode varchar(20))
insert FS_VATProductCode select 1,'Bike'
union all select 2,'Car'
union all select 3,'Tractor'

create table FS_VATProductCustomerCombination(VATCustomerCode varchar(20),VATProductCode varchar(20),Tax varchar(20))
insert FS_VATProductCustomerCombination select 'John' ,'Bike' ,'2'
union all select 'John' ,'Car' ,' 500'
union all select 'Mike' ,'Tractor','149'
union all select 'Jully','Bike' ,'2'
union all select 'Jully','Car' ,'388'
union all select 'Jully','Tractor','149'
go

--查询
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''
select @s1=@s1+',Column'+cast(id as varchar)+' varchar(20)'
,@s2=@s2+','''+VATCustomerCode+''''
,@s3=@s3+',max(case b.VATCustomerCode when '''
+VATCustomerCode+''' then Tax else '''' end)'
from FS_VATCustomerCode order by id
exec('
declare @tb table(ColumnA varchar(20)'+@s1+')
insert @tb
select ''ProductCode'''+@s2+'
insert @tb
select b.VATProductCode'+@s3+'
from FS_VATProductCode a,FS_VATProductCustomerCombination b
where a.VATProductCode=b.VATProductCode
group by b.VATProductCode,a.id
order by a.id
select * from @tb')
go

drop table FS_VATCustomerCode,FS_VATProductCode,FS_VATProductCustomerCombination

/*--测试结果

ColumnA Column1 Column2 Column3
-------------------- -------------------- -------------------- -----------
ProductCode John Mike Jully
Bike 2 2
Car 500 388
Tractor 149 149
--*/
99831323 2004-09-20
  • 打赏
  • 举报
回复
--动态的(呵呵,没测试,你自己测试吧):


declare @sql varchar(8000)
declare @Fields varchar(8000)
declare @Fields1 varchar(8000)
declare @Values varchar(8000)
declare @Select varchar(8000)

set @Fields='ColumnA'
set @Fields1='id int IDENTITY(1,1),
ColumnA varchar(30)'

set @Values='''ProductCode'''
set @select=''

select @Fields=@Fields+',Column'+cast(id as varchar(10)),
@Fields1=@Fields1+',Column'+cast(id as varchar(10))+' varchar(30)',
@Values=@Values+','''+VATCustomerCode+'''',
@select=@select+',cast(sum(case when VATCustomerCode='''+VATCustomerCode+''' then Tax else null end) as varchar(30)) as [Column'+cast(id as varchar(10))+']'
from FS_VATCustomerCode
order by id

exec ('
declare @t table ('+@Fields1+'
)
insert @t ('+@Fields+') values ('+@values+')
insert @t ('+@Fields+')
select VATProductCode'+@select+'
from FS_VATProductCustomerCombination
group by VATProductCode

select '+@Fields+' from @t order by id
'
)


99831323 2004-09-20
  • 打赏
  • 举报
回复
declare @t table (id int IDENTITY(1,1),
ColumnA varchar(30),
Column1 varchar(30),
Column2 varchar(30),
Column3 varchar(30)
)
insert @t (ColumnA ,Column1,Column2,Column3 ) values ('ProductCode','John','Mike','Jully')
insert @t (ColumnA ,Column1,Column2,Column3 )
select VATProductCode,
cast(sum(case when VATCustomerCode='John' then Tax else null end) as varchar(30)),
cast(sum(case when VATCustomerCode='Mike' then Tax else null end) as varchar(30)),
cast(sum(case when VATCustomerCode='Jully' then Tax else null end) as varchar(30))
from FS_VATProductCustomerCombination
group by VATProductCode
select ColumnA ,Column1,Column2,Column3 from @t order by id

--固定的

zjcxc 元老 2004-09-20
  • 打赏
  • 举报
回复
用表变量没有意思,因为你的FS_VATCustomerCode表是动态的,这样的话,表变量也是用动态的,就是放在exec中,那么这个表变量就只能在exec中有效,这样的做法还不如直接在exec中出结果
zjcxc 元老 2004-09-20
  • 打赏
  • 举报
回复
这个问题不是已经答过了吗?
99831323 2004-09-20
  • 打赏
  • 举报
回复
用表变量比较困难,除非你知道FS_VATCustomerCode表的行数
黑马 2004-09-20
  • 打赏
  • 举报
回复
表变量是什么?

以学习态度询问
xuam 2004-09-20
  • 打赏
  • 举报
回复
用交叉报表!
einsteincao 2004-09-20
  • 打赏
  • 举报
回复
ProductCode John Mike Jully 这一行是insert到表变量中去的 需要让它在表变量的第一行显示..

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧