我有个表TT字段是spbh(商品编号),jhdbh(进货单编号),dj(单价),sj(时间);
我想用SQL语句实现读出来的记录的spbh都不能一样,每条记录还要有最近的7个dj
也就是读出来的表如下:
spbh1,dj,dj1,dj2,dj3,dj4,dj5,dj6
spbh2,dj,dj1,dj2,dj3,dj4,dj5,dj6
如果麻烦的话就读3-4个单价就行了,多的我参考下就OK了,先谢谢了
我想用SQL语句实现读出来的记录的spbh都不能一样,每条记录还要有最近的7个dj
也就是读出来的表如下:
spbh1,dj,dj1,dj2,dj3,dj4,dj5,dj6
spbh2,dj,dj1,dj2,dj3,dj4,dj5,dj6
如果麻烦的话就读3-4个单价就行了,多的我参考下就OK了,先谢谢了
(字段1 int,字段2 int,字段3 varchar(20))insert @t
select 2000,1,'a' union all
select 2000,2,'b' union all
select 2000,3,'c' union all
select 2005,6,'d' union all
select 2005,7,'e' union all
select 2005,8,'f'select 字段1,[字段31]=max(case when 字段2=1 then 字段3 else '' end),
[字段32]=max(case when 字段2=2 then 字段3 else '' end),
[字段33]=max(case when 字段2=3 then 字段3 else '' end)
from
(
select 字段1,字段2=(select count(1) from @t where 字段1=a.字段1 and 字段2<=a.字段2),字段3
from @t a
) a group by 字段1
CREATE FUNCTION dbo.f_TT(@spbh varchar(10))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(dj as varchar)
FROM (
SELECT DISTINCT TOP 7 DJ from TT WHERE spbh=@spbh
) a
--print @re
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT SPBH,DJ=dbo.f_TT(SPBH) FROM TT GROUP BY SPBH