是不是這個?
select * from (select rn=row_nunber() over (partition by djbh order by dj_sn),* from tb )a where rn>3
select * from (select rn=row_nunber() over (partition by djbh order by dj_sn),* from tb )a where rn>3
非常感谢 我先研究一下
非常感谢 我先研究一下
不好意思 我有点没看懂啊
我是想把表a里所有跟这个spid一样,数量一样的所有的记录
比如这个单据是3条记录,然后查出来表a里所有的spid和这个单据对应的,商品数量一样的3条记录
不好意思啊 我尽量说得准确些
表bsddmx
djbh TMA00117065 有3条明细记录,djbh都是一样的
spid 有3个不同的商品id
shl 3个商品对应的数量
现在要查出bsddmx表中所有跟TMA00117065这个单据一模一样的数据
djbh TMA00117065 有3条明细记录,djbh都是一样的
spid 有3个不同的商品id
shl 3个商品分别对应的数量
现在要查出bsddmx表中所有跟TMA00117065这个单据一模一样的数据
是spid一样,对应shl数量一样,明细数量一样
create table [a]([djbh] varchar(10),[spid] varchar(10),[shl] int)
insert [a]
select 'bh001' ,'sp001' ,10 union all
select 'bh001' ,'sp002' ,11 union all
select 'bh001' ,'sp003' ,15 union all
select 'bh002' ,'sp001' ,10 union all
select 'bh002' ,'sp002' ,11 union all
select 'bh002' ,'sp003' ,15 union all
select 'bh003' ,'sp001' ,97 union all
select 'bh003' ,'sp003' ,98 union all
select 'bh003' ,'sp004' ,99
select * from adjbh spid shl
---------- ---------- -----------
bh001 sp001 10
bh001 sp002 11
bh001 sp003 15
bh002 sp001 10
bh002 sp002 11
bh002 sp003 15
bh003 sp001 97
bh003 sp003 98
bh003 sp004 99(9 行受影响)--处理测试数据
select *,row_number() over(PARTITION BY djbh order by djbh,spid) as rowid into #a from a
select * from #a
djbh spid shl rowid
---------- ---------- ----------- --------------------
bh001 sp001 10 1
bh001 sp002 11 2
bh001 sp003 15 3
bh002 sp001 10 1
bh002 sp002 11 2
bh002 sp003 15 3
bh003 sp001 97 1
bh003 sp003 98 2
bh003 sp004 99 3(9 行受影响)--按单据编号进行分组合并
SELECT [djbh],
(SELECT [spid]+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [spid] ,
(SELECT cast([shl] as varchar(10))+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [shl]
into #b
FROM #a a
GROUP BY [djbh]
select * from #b
djbh spid shl
---------- ------------------------
bh001 sp001,sp002,sp003, 10,11,15,
bh002 sp001,sp002,sp003, 10,11,15,
bh003 sp001,sp003,sp004, 97,98,99,(3 行受影响)
--查询重复数量大于2的单据号
select djbh from
#b a,
(select spid,shl from #b group by spid,shl having count(djbh)>=2) b
where a.spid=b.spid and a.shl=b.shldjbh
----------
bh001
bh002(2 行受影响)
SELECT [djbh],
(SELECT [spid]+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [spid] ,
(SELECT cast([shl] as varchar(10))+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [shl]
into #b
FROM #a a
GROUP BY [djbh]
select * from #b我试了下
其实我这个需求只要上面这段代码合并分组就行了
第一步row_number() over处理测试数据是干嘛用的啊
还望赐教
SELECT [djbh],
(SELECT [spid]+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [spid] ,
(SELECT cast([shl] as varchar(10))+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [shl]
into #b
FROM #a a
GROUP BY [djbh]
select * from #b我试了下
其实我这个需求只要上面这段代码合并分组就行了
第一步row_number() over处理测试数据是干嘛用的啊
还望赐教
相同的单据编号分组用的。不然结果会是这样:/*
djbh spid shl
----- ----------------------------------------------------------------------------
bh001 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh002 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh003 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,(3 行受影响)
*/
SELECT [djbh],
(SELECT [spid]+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [spid] ,
(SELECT cast([shl] as varchar(10))+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [shl]
into #b
FROM #a a
GROUP BY [djbh]
select * from #b我试了下
其实我这个需求只要上面这段代码合并分组就行了
第一步row_number() over处理测试数据是干嘛用的啊
还望赐教
相同的单据编号分组用的。不然结果会是这样:/*
djbh spid shl
----- ----------------------------------------------------------------------------
bh001 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh002 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh003 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,(3 行受影响)
*/刚才我试了下 没有出现这种情况哎
SELECT [djbh],
(SELECT [spid]+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [spid] ,
(SELECT cast([shl] as varchar(10))+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [shl]
into #b
FROM #a a
GROUP BY [djbh]
select * from #b我试了下
其实我这个需求只要上面这段代码合并分组就行了
第一步row_number() over处理测试数据是干嘛用的啊
还望赐教
相同的单据编号分组用的。不然结果会是这样:/*
djbh spid shl
----- ----------------------------------------------------------------------------
bh001 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh002 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh003 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,(3 行受影响)
*/刚才我试了下 没有出现这种情况哎
编号也是为了排序,不然有的是【sp001,002】 有的是【002,sp001】,这样一样的数据也匹配不上了!
SELECT [djbh],
(SELECT [spid]+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [spid] ,
(SELECT cast([shl] as varchar(10))+',' FROM #a WHERE djbh=a.djbh
FOR XML PATH('')
) AS [shl]
into #b
FROM #a a
GROUP BY [djbh]
select * from #b我试了下
其实我这个需求只要上面这段代码合并分组就行了
第一步row_number() over处理测试数据是干嘛用的啊
还望赐教
相同的单据编号分组用的。不然结果会是这样:/*
djbh spid shl
----- ----------------------------------------------------------------------------
bh001 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh002 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,
bh003 sp001,sp002,sp003,sp001,sp002,sp003,sp001,sp003,sp004, 10,11,15,10,11,15,97,98,99,(3 行受影响)
*/刚才我试了下 没有出现这种情况哎
编号也是为了排序,不然有的是【sp001,002】 有的是【002,sp001】,这样一样的数据也匹配不上了!
恩 明白了 我的表有十几万条数据
现在执行下来有10几秒 时间太长了 还能再提高执行效率吗?