数据库查询语句取前三位
我在数据库查询出的数据 1000001 ，2000001 ，3000001 ，3000002 ，4个7位的数字 我想去掉重复的3 而且只有这七位数字的前三个
select xx from biao where LEN(xx)>=5
谢谢回答的大神
[解决办法]
加个distinct去重：
select distinct xx from biao where LEN(xx)>=5
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-25 15:39:12
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[biao]
if object_id('[biao]') is not null drop table [biao]
go
create table [biao]([a] VARCHAR(10))
insert [biao]
select 1000001 union all
select 2000001 union all
select 3000001 union all
select 3000002
--------------开始查询--------------------------
select DISTINCT left(a,3) from [biao]
----------------结果----------------------------
/*
------
100
200
300
*/
if object_id('Tempdb..#a') is not null drop table #a
create table #a(
id int identity(1,1) not null,
col varchar(10) null
)
Insert Into #a
select '1000001' union all
select '2000001' union all
select '3000001' union all
select '300000'
select left(col,3)as col from #a group by left(col,3)
---------------------
--结果
col
------
100
200
300
(3 行受影响)
create table #TB(A VARCHAR(50))
insert #TB
select 1000001 union
select 2000001 union
select 3000001 union
select 3000002
--------------开始查询--------------------------
select DISTINCT left(A,3) from #TB --where
--where后面是条件，如果有的话就加上，如果没有就不用加
