我的原表记录如下:
spbm afield jg shrq
0100135 零售价格 7.0000 2007-04-16 15:05:56.250
0100135 会员价 7.0000 2007-04-16 15:05:56.250
0100135 末批进价 5.5000 2007-04-16 15:05:56.250
0100135 零售价格 7.5000 2007-12-19 09:07:55.903
0100135 末批进价 6.5000 2007-12-19 09:07:55.903
还有很多spbm不一样的记录,但每个spbm都按上面那样处理在建立新表,存放一条记录时,不同的afield,要增加相应的字段
最终形成的记录:spbm,(不同的afield),(相同afield,shrq最大的,yjg的值填到新增的相应字段),相同afield最大的shrq如上表,形成的记录应该为
spbm 零售价格 会员价 末批进价 shrq
0100135 7.5 7 6.5 2007-12-19 09:07:55.903这个比较复杂,这样的sql语句应该怎么写呀,我折腾不出来,请各位高手帮我想想办法?
spbm afield jg shrq
0100135 零售价格 7.0000 2007-04-16 15:05:56.250
0100135 会员价 7.0000 2007-04-16 15:05:56.250
0100135 末批进价 5.5000 2007-04-16 15:05:56.250
0100135 零售价格 7.5000 2007-12-19 09:07:55.903
0100135 末批进价 6.5000 2007-12-19 09:07:55.903
还有很多spbm不一样的记录,但每个spbm都按上面那样处理在建立新表,存放一条记录时,不同的afield,要增加相应的字段
最终形成的记录:spbm,(不同的afield),(相同afield,shrq最大的,yjg的值填到新增的相应字段),相同afield最大的shrq如上表,形成的记录应该为
spbm 零售价格 会员价 末批进价 shrq
0100135 7.5 7 6.5 2007-12-19 09:07:55.903这个比较复杂,这样的sql语句应该怎么写呀,我折腾不出来,请各位高手帮我想想办法?
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
*/
insert a 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' declare @sql varchar(8000)
set @sql='select spbm,'
select @sql=@sql+'max(case when afield='''+afield+''' then jg end) ['+afield+'],' from a group by afieldset @sql=@sql+'max(shrq) shrq from a group by spbm'
exec(@sql)--result
/*spbm 会员价 零售价格 末批进价 shrq
---------- ---------------------- ---------------------- ---------------------- ------------------------------------------------------
0100135 7.0000 7.5000 6.5000 2007-12-19 09:07:55.903
*/
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
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
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 spbmdrop table tb/*
spbm 零售价格 会员价 末批进价 shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135 7.5000 7.0000 6.5000 2007-12-19 09:07:55.903(1 行受影响)
*/
insert a select '0100135','零售价格', 7.0000 ,'2008-04-16 15:05:56.250' 這樣鳥兒的就不對了
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
from
(
select spbm,
case when afield = '零售价格' then max(jg) as 零售价格,
case when afield = '会员价' then max(jg) as 会员价,
case when afield = '末批进价' then max(jg) as 末批进价,
max(shrq)
from
table_1
group by spbm, afield
) a
group by spbm
set @sql='select spbm'
select @sql=@sql+',max(case afield when '''+afield+''' then jg else 0 end)['+afield+']'
from (select distinct afield from ta) as t
select @sql=@sql+',max(shrq) from ta group by spbm'
exec(@sql)练习