查询语句,求解答 !

goodxushi 2010-10-14 02:18:54
序号 单别 单号 合同号 总量 总序号 批号
1 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.014 20100831
2 51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.022 20100831
3 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.015 20100831
4 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.015 20100831
5 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.002 20100831
6 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.003 20100831
7 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.004 20100831
8 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.005 20100831
9 51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
10 51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
11 51S5 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
12 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.021 20100831
13 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.001 20100831
14 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.001 20100831
15 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.003 20100831
16 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.004 20100831
17 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.005 20100831
18 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.006 20100831

要求:查询出在单别和单号相同的情况下总序号重复的记录。
上述的查询结果应为:序号3和4、9和10、13和14
...全文
78 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
SQLCenter 2010-10-14
#7

select * from tb t where
(select count(1) from tb where 单别=t.单别 and 单号=t.单号 and 总序号=t.总序号) > 1
回复
kevn 2010-10-14
[Quote=引用 7 楼 goodxushi 的回复:]
楼上各位不好意思 再请教:如果此表中没有“序号”此列 , 那查询语句又该如何写呢??

单别 单号 合同号 总量 总序号 批号
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.014 20100831
51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.02……
[/Quote]

所谓的总序号是哪来的?你这个排列的顺序?那你就加序号,row_number()
回复
goodxushi 2010-10-14
楼上各位不好意思 再请教:如果此表中没有“序号”此列 , 那查询语句又该如何写呢??

单别 单号 合同号 总量 总序号 批号
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.014 20100831
51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.022 20100831
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.015 20100831
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.015 20100831
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.002 20100831
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.003 20100831
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.004 20100831
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.005 20100831
51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
51S5 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
51S2 100831137 1100752-31 131.82 221.1100752-31.0001.002.021 20100831
51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.001 20100831
51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.001 20100831
51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.003 20100831
51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.004 20100831
51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.005 20100831
51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.006 20100831
要求:查询出在单别和单号相同的情况下总序号重复的记录。
上述的查询结果应为:序号3和4、9和10、13和14的记录
回复
qifachen 2010-10-14
select max(序号) from tb group by 单别,单号 having count(序号)>1
回复
kevn 2010-10-14
select max(序号) from tb group by 单别,单号 having count(序号)>1

回复
fpzgm 2010-10-14
都对。。
回复
SQLCenter 2010-10-14
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(序号 int, 单别 varchar(8), 单号 int, 合同号 varchar(10), 总量 numeric(5,2), 总序号 varchar(27), 批号 int)
insert into #
select 1, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.001.014', '20100831' union all
select 2, '51S2', 100831138, '1100752-31', 131.82, '221.1100752-31.0001.003.022', '20100831' union all
select 3, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.001.015', '20100831' union all
select 4, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.001.015', '20100831' union all
select 5, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.002.002', '20100831' union all
select 6, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.002.003', '20100831' union all
select 7, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.002.004', '20100831' union all
select 8, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.002.005', '20100831' union all
select 9, '51S2', 100831138, '1100752-31', 131.82, '221.1100752-31.0001.003.023', '20100831' union all
select 10, '51S2', 100831138, '1100752-31', 131.82, '221.1100752-31.0001.003.023', '20100831' union all
select 11, '51S5', 100831138, '1100752-31', 131.82, '221.1100752-31.0001.003.023', '20100831' union all
select 12, '51S2', 100831137, '1100752-31', 131.82, '221.1100752-31.0001.002.021', '20100831' union all
select 13, '51S2', 100831136, '1100752-31', 131.82, '221.1100752-31.0001.001.001', '20100831' union all
select 14, '51S2', 100831136, '1100752-31', 131.82, '221.1100752-31.0001.001.001', '20100831' union all
select 15, '51S2', 100831136, '1100752-31', 131.82, '221.1100752-31.0001.001.003', '20100831' union all
select 16, '51S2', 100831136, '1100752-31', 131.82, '221.1100752-31.0001.001.004', '20100831' union all
select 17, '51S2', 100831136, '1100752-31', 131.82, '221.1100752-31.0001.001.005', '20100831' union all
select 18, '51S2', 100831136, '1100752-31', 131.82, '221.1100752-31.0001.001.006', '20100831'

select * from # t where
exists (select 1 from # where 单别=t.单别 and 单号=t.单号 and 总序号=t.总序号 and 序号<>t.序号)

/*
序号 单别 单号 合同号 总量 总序号 批号
----------- -------- ----------- ---------- ------ --------------------------- -----------
3 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.015 20100831
4 51S2 100831137 1100752-31 131.82 221.1100752-31.0001.001.015 20100831
9 51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
10 51S2 100831138 1100752-31 131.82 221.1100752-31.0001.003.023 20100831
13 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.001 20100831
14 51S2 100831136 1100752-31 131.82 221.1100752-31.0001.001.001 20100831
*/
回复
SQLCenter 2010-10-14
select * from tb t where exists (select 1 from tb where 单别=t.单别 and 单号=t.单号 and 总序号=t.总序号 and 序号<>t.序号)
回复
喜-喜 2010-10-14
select a.序号 as 序号1,b.序号 as 序号2 from tb a inner join tb b on a.单别=b.单别 and a.单号=b.单号 and a.总序号=b.总序号 and a.序号<>b.序号
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-14 02:18
社区公告
暂无公告