序号 单别 单号 合同号 总量 总序号 批号
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
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
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
*/
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的记录
所谓的总序号是哪来的?你这个排列的顺序?那你就加序号,row_number()
(select count(1) from tb where 单别=t.单别 and 单号=t.单号 and 总序号=t.总序号) > 1