例子:我现在有两个表第一个表
ID NAME BOOK DATA
1 JOKE SHUXUE 2004-12-12
.
.
.
第二个表
S_ID ID S_LIKEMAN picre
0001 1 小张 12
0002 1 小李 13
我现在要怎么写SQL语句.才出现
BOOK S_LIKEMAN picre S_LIKEMAN picre
shuxue 小张 12 小李 13
意思就是书只能显示一次,他们的联系人都要显示.create table tb1(id int,name varchar(20),book varchar(20),date datetime)
insert tb1 values(1,'JOKE','SHUXUE','2004-12-12')create table tb2(s_id varchar(10),id int,s_likeman varchar(20),pricre int)
insert tb2 values('0001',1,'小张',12)
insert tb2 values('0002',1,'小李',13)declare @sql varchar(8000)
set @sql='select a.book'
select @sql=@sql+',max(case when b.s_likeman='''+s_likeman+''' then b.s_likeman end) s_likeman,
max(case when b.pricre='+cast(pricre as varchar)+' then b.pricre end) pricre'
from (select distinct s_likeman,pricre from tb2) aexec (@sql+' from tb1 a,tb2 b where a.id=b.id group by a.book')drop table tb1,tb2/*
book s_likeman pricre s_likeman pricre
-------------------- -------------------- ----------- -------------------- -----------
SHUXUE 小李 13 小张 12
*/
ID NAME BOOK DATA
1 JOKE SHUXUE 2004-12-12
.
.
.
第二个表
S_ID ID S_LIKEMAN picre
0001 1 小张 12
0002 1 小李 13
我现在要怎么写SQL语句.才出现
BOOK S_LIKEMAN picre S_LIKEMAN picre
shuxue 小张 12 小李 13
意思就是书只能显示一次,他们的联系人都要显示.create table tb1(id int,name varchar(20),book varchar(20),date datetime)
insert tb1 values(1,'JOKE','SHUXUE','2004-12-12')create table tb2(s_id varchar(10),id int,s_likeman varchar(20),pricre int)
insert tb2 values('0001',1,'小张',12)
insert tb2 values('0002',1,'小李',13)declare @sql varchar(8000)
set @sql='select a.book'
select @sql=@sql+',max(case when b.s_likeman='''+s_likeman+''' then b.s_likeman end) s_likeman,
max(case when b.pricre='+cast(pricre as varchar)+' then b.pricre end) pricre'
from (select distinct s_likeman,pricre from tb2) aexec (@sql+' from tb1 a,tb2 b where a.id=b.id group by a.book')drop table tb1,tb2/*
book s_likeman pricre s_likeman pricre
-------------------- -------------------- ----------- -------------------- -----------
SHUXUE 小李 13 小张 12
*/
declare @str varchar(8000)select @str='select spbm 'select @str=@str+','+afield+'=max(case when afield='+afield+' then jg else 0 end )'
from 表select @str=@str+' from 表 group by spbm'exec(@str)
insert tb select '0100135','零售价格', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'会员价', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union all select '0100135', '零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union all select '0100135', '末批进价', 6.5000 ,'2007-12-19 09:07:55.903'
go--静态SQL,指afield值固定为这三种。
select spbm ,
max(case afield when '零售价格' then jg else 0 end) '零售价格',
max(case afield when '会员价' then jg else 0 end) '会员价',
max(case afield when '末批进价' then jg else 0 end) '末批进价',
max(shrq) shrq
from tb
group by spbm
/*
spbm 零售价格 会员价 末批进价 shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135 7.5000 7.0000 6.5000 2007-12-19 09:07:55.903(1 行受影响)
*/--动态SQL,指afield值不固定。
declare @sql varchar(8000)
set @sql = 'select spbm'
select @sql = @sql + ' , max(case afield when ''' + afield + ''' then jg else 0 end) [' + afield + ']'
from (select distinct afield from tb) as a
set @sql = @sql + ',max(shrq) shrq from tb group by spbm'
exec(@sql)
/*
spbm 会员价 零售价格 末批进价 shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135 7.0000 7.5000 6.5000 2007-12-19 09:07:55.903(1 行受影响)
*/drop table tb[/code]
insert t
select '0100135','零售价格',7.0000 ,'2007-04-16 15:05:56.250'
union select '0100135','会员价', 7.0000 ,'2007-04-16 15:05:56.250'
union select '0100135','末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union select '0100135','零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union select '0100135','末批进价', 6.5000 ,'2007-12-19 09:07:55.903' declare @str varchar(8000)select @str='select spbm 'select @str=@str+','+afield+'=max(case when afield='''+afield+''' then jg else 0 end )'
from (select * from t a where not exists(select 1 from t where spbm=a.spbm and afield=a.afield and a.jg<jg)) tselect @str=@str+' ,shrq=max(shrq) from t group by spbm'
exec(@str)
drop table t/* 结果
spbm 会员价 零售价格 末批进价 shrq
---------- -------------- -------------- -------------- ------------------------------------------------------
0100135 7.00 7.50 6.50 2007-12-19 09:07:55.903*/
insert @t select '0100135','零售价格',7.00,'2007-04-16 15:05:56.250'
union all select '0100135','会员价',7.00,'2007-04-16 15:05:56.250'
union all select '0100135','末批进价', 5.50 ,'2007-04-16 15:05:56.250'
union all select '0100135','零售价格', 7.50 ,'2007-12-19 09:07:55.903'
union all select '0100135','末批进价', 6.50 ,'2007-12-19 09:07:55.903'select spbm,
sum(case afiedl when '零售价格' then jg else 0 end) as 零售价格 ,
sum(case afiedl when '会员价' then jg else 0 end) as 会员价,
sum(case afiedl when '末批进价' then jg else 0 end) as 末批进价,
max(shrq)
from (
select a.spbm,a.afiedl,a.jg,a.shrq from @t a,
(select spbm,afiedl,max(shrq) shrq from @t group by spbm,afiedl) b
where a.spbm = b.spbm and a.afiedl = b.afiedl and a.shrq = b.shrq
) a
group by spbm
结果
0100135 7.50 7.00 6.50 2007-12-19 09:07:55.903
------- ---------- ---------- ---------- ------------------------------------------------------
0100135 7.50 7.00 6.50 2007-12-19 09:07:55.903