有表X,数据如下 a b
2006010056 OMR0001
2006010057 OMR0001
2006010058 OMR0001
2006010059 OMR0001
2006010060 OMR0001
2006010061 OMR0001
2006010062 OMR0001
2006020063 OMR0001
2006030064 OMR0001
2006040065 OMR00012006100056 OMR0002
2006100057 OMR0002
2006100058 OMR0002
2006100059 OMR0002
2006100060 OMR0002
2006100061 OMR0002
2006100062 OMR0002
2006110063 OMR0002
2006120064 OMR0002
2006130065 OMR0002比如有0MR0001-OMR0010,
我想取A列的前6位做比对,怎样将每一个OMR里不同于大多数的数据列出来,就是我标记的部分
----------------------------
不知道说清楚没
2006010056 OMR0001
2006010057 OMR0001
2006010058 OMR0001
2006010059 OMR0001
2006010060 OMR0001
2006010061 OMR0001
2006010062 OMR0001
2006020063 OMR0001
2006030064 OMR0001
2006040065 OMR00012006100056 OMR0002
2006100057 OMR0002
2006100058 OMR0002
2006100059 OMR0002
2006100060 OMR0002
2006100061 OMR0002
2006100062 OMR0002
2006110063 OMR0002
2006120064 OMR0002
2006130065 OMR0002比如有0MR0001-OMR0010,
我想取A列的前6位做比对,怎样将每一个OMR里不同于大多数的数据列出来,就是我标记的部分
----------------------------
不知道说清楚没
解决方案 »
- 在数据库中怎么表示列数据的单位?
- CONCAT函数的问题------对数据类型而言运算符无效。运算符为concatenation,类型为text
- 分解字符串
- 存储过程这样写到底错在哪里了啊,着急等待。。。
- 放100分大家来讨论一下,动态生成创建局域临时表的语句,执行该语句后局域临时表也跟着free了,有办法做到不free吗。
- SQL SERVER 2005右键无单步调试菜单?
- SQL Server 2005 标准版,企业版,enterprise版有什么区别?
- 关于SQL语句的请教
- 很菜的问题:如何把SQL SERVER/DTS PACKAGE生成的VB.bas独立执行和编译?我在执行时,不报错,但是一闪就结束,也没有把数据UPLOAD到SQL SERVER中。
- 建表问题,我想建立一个跟原表结构相同的表
- 难,SQL SEREVER 备份时的问题
- 火车站站查询
go
--> -->
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([a] int,[b] nvarchar(7))
Insert #T
select 2006010056,N'OMR0001' union all
select 2006010057,N'OMR0001' union all
select 2006010058,N'OMR0001' union all
select 2006010059,N'OMR0001' union all
select 2006010060,N'OMR0001' union all
select 2006010061,N'OMR0001' union all
select 2006010062,N'OMR0001' union all
select 2006020063,N'OMR0001' union all
select 2006030064,N'OMR0001' union all
select 2006040065,N'OMR0001'
Go
Select * from #T t where not exists(select 1 from #T where abs([a]-t.[a])=1)
(10 個資料列受到影響)
a b
----------- -------
2006020063 OMR0001
2006030064 OMR0001
2006040065 OMR0001(3 個資料列受到影響)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] varchar(7))
insert [tb]
select 2006010056,'OMR0001' union all
select 2006010057,'OMR0001' union all
select 2006010058,'OMR0001' union all
select 2006010059,'OMR0001' union all
select 2006010060,'OMR0001' union all
select 2006010061,'OMR0001' union all
select 2006010062,'OMR0001' union all
select 2006020063,'OMR0001' union all
select 2006030064,'OMR0001' union all
select 2006040065,'OMR0001' union all
select 2006100056,'OMR0002' union all
select 2006100057,'OMR0002' union all
select 2006100058,'OMR0002' union all
select 2006100059,'OMR0002' union all
select 2006100060,'OMR0002' union all
select 2006100061,'OMR0002' union all
select 2006100062,'OMR0002' union all
select 2006110063,'OMR0002' union all
select 2006120064,'OMR0002' union all
select 2006130065,'OMR0002'
---查询---
select a.*
from tb a,(select left(a,6) as a,count(1) as c from tb group by left(a,6)) b
where left(a.a,6)=b.a
order by b.c
---结果---
a b
----------- -------
2006020063 OMR0001
2006030064 OMR0001
2006040065 OMR0001
2006110063 OMR0002
2006120064 OMR0002
2006130065 OMR0002
2006010056 OMR0001
2006010057 OMR0001
2006010058 OMR0001
2006010059 OMR0001
2006010060 OMR0001
2006010061 OMR0001
2006010062 OMR0001
2006100056 OMR0002
2006100057 OMR0002
2006100058 OMR0002
2006100059 OMR0002
2006100060 OMR0002
2006100061 OMR0002
2006100062 OMR0002(所影响的行数为 20 行)