查询语句，求解答！

2010-10-14 02:18:54 147 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL Server 2022 常见问题解答月度工作总结本月主要围绕SQL Server 2022常见问题解答开展工作。解答了用户关于安装配置、性能优化、数据迁移、安全设置等多方面的问题。提供详细的步骤与方法，帮助用户解决在SQL Server 2022使用过程中遇到的各种难题，确保用户能顺利地运用该数据库管理系统，提升工作效率。阅读详情

序号单别单号合同号总量总序号批号
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

...全文

搞定安卓11+抓包难题：用Kitsune Mask、magic_overlayfs和LSPosed三件套绕过SSL Pinning（保姆级教程）本文详细解析了安卓11+系统中抓包面临的核心挑战，包括SSL Pinning和Root检测机制，并提供了使用Kitsune Mask、magic_overlayfs和LSPosed三件套的完整解决方案。通过动态分区技术解析和工具链配置，帮助开发者绕过安全限制，实现高效抓包分析。阅读详情

goodxushi

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

SQLCenter 2010-10-14

打赏
举报

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.序号

查询语句，求解答 ！

相关推荐

查询语句，求解答！