select max(field) from table where field like 'CB%' group by substring(field,1,3) //未测试
TO : windindance(风之舞) 我用的是ACCESS数据库,不知它支持SUBSTRING否?
select distinct 字段名 from 表名 where 字段值 like 'CB%' group by 字段名
同意windindance(风之舞) 如果记录的信息要完整可以 select * from table inner join (select max(fieldID) as fieldID from table where field like 'CB%' group by substring(field,1,3)) as tableA on table.fieldID=tableA.fieldID 以上要求FieldID为主键,如果Field(你上面要处理的字段)就是主键,则fieldID=fieldID该语句未测试
SELECT DISTINCT LEFT(ID, 3) FROM DM_PRODUCTGG WHERE LEFT(ID, 2) = 'CB'
'*********ID 改为你的字段名,应该没错************ SELECT DISTINCT LEFT(ID, 3),ID FROM DM_PRODUCTGG WHERE LEFT(ID, 2) = 'cb'
select max(field) from table where field like 'CB%' group by left(field,3)
select distinct left(a.field1, 1), (select top 1 b.field1 from table1 b where left(b.field1,1)=left(a.field1,1)) from table1 a
刚才有些地方写错了 select distinct left(a.房号, 2), (select top 2 b.房号 from 单元 b where left(b.房号,2)=left(a.房号,2)) from 单元 a where a.房号 like "CB*"
刚才有些地方写错了 select distinct left(a.field1, 2), (select top 2 b.field1 from table1 b where left(b.field1,2)=left(a.field1,2)) from table1 a where a.field1 like "CB*"
where field like 'CB%'
group by substring(field,1,3)
//未测试
我用的是ACCESS数据库,不知它支持SUBSTRING否?
如果记录的信息要完整可以
select * from table inner join (select max(fieldID) as fieldID
from table where field like 'CB%' group by substring(field,1,3))
as tableA on table.fieldID=tableA.fieldID
以上要求FieldID为主键,如果Field(你上面要处理的字段)就是主键,则fieldID=fieldID该语句未测试
dbcontrols(泰山__帮助你使我感到快乐.)
你们两个的方法我都试过了,都不对
mid(field,3,1)
FROM DM_PRODUCTGG
WHERE LEFT(ID, 2) = 'CB'
SELECT DISTINCT LEFT(ID, 3),ID
FROM DM_PRODUCTGG
WHERE LEFT(ID, 2) = 'cb'
where field like 'CB%'
group by left(field,3)
(select top 1 b.field1 from table1 b
where left(b.field1,1)=left(a.field1,1))
from table1 a
select distinct left(a.房号, 2),
(select top 2 b.房号 from 单元 b
where left(b.房号,2)=left(a.房号,2))
from 单元 a where a.房号 like "CB*"
select distinct left(a.field1, 2),
(select top 2 b.field1 from table1 b
where left(b.field1,2)=left(a.field1,2))
from table1 a where a.field1 like "CB*"