我以经用了distinct呀 如下: SELECT distinct left(StrucID0,12) as ID,Content from TMemo where left(StrucID0,12) IN (select BZID from BList) and Content like '%工程%'
很奇怪,你先看看 SELECT Content from TMemo where Content like '工程' 结果是什么? 然后把语句逐步增加到:SELECT distinct left(StrucID0,12) as ID,Content from TMemo where left(StrucID0,12) IN (select BZID from BList) and Content like '工程'
select '1111111' as a,'oon' as b into #t insert into #t values ('1222222', 'aab' ) insert into #t values ('1114444', 'mmm') select left(a,3) as id, max(b) as maxb, min(b) as minb from #t group by left(a,3)结果 id maxb minb 111 oon mmm 122 aab aab
忙了大半天,总算搞定了语句如下: string strsql = "SELECT left(StrucID0,12) as ID from TMemo where left(StrucID0,12) IN (select BZID from BList) and Content like '%" + search + "%'" Group by left(StrucID0,12)";我想重中之重在于group by 语句!!根本无须distinct
SELECT distinct left(StrucID0,12) as ID,Content from TMemo where left(StrucID0,12) IN (select BZID from BList) and Content like '工程'程序执行后,为何有许多重复的记录呢,如何避免这种情况以达到我的要求呢?样例记录:
1.表TMemo
-----------------------------------------------------
StrucID0 Content
00000000000000000000 工程工程工程.....
00000000000000010001 fffffffffff...
00000000000010010010 uuuuuuuuuu....
00000001000000000000 恭恭敬敬......
00010002000000010000 aaaaaaaaa....
00030100001020023000 bbbbbbbbbb....
..........
------------------------------------------------------
2.表BList
BZID
000000000000
000000010000
000301000010
...........
-------------------------------------------------------对于上述示例数据我想显示的结果为:
000000000000 工程 /* 只有此条记录符合条件但实际上它显示了几条重复的记录,不知如何实现
即
sql语句中最后应为and Content like '%工程%'
000000000000 fffffffffff...
000000000000 uuuuuuuuuu....
如下:
SELECT distinct left(StrucID0,12) as ID,Content from TMemo where left(StrucID0,12) IN (select BZID from BList) and Content like '%工程%'
SELECT Content from TMemo where Content like '工程' 结果是什么? 然后把语句逐步增加到:SELECT distinct left(StrucID0,12) as ID,Content from TMemo where left(StrucID0,12) IN (select BZID from BList) and Content like '工程'
like '工程%'试试
insert into #t values ('1222222', 'aab' )
insert into #t values ('1114444', 'mmm')
select left(a,3) as id, max(b) as maxb, min(b) as minb from #t group by left(a,3)结果
id maxb minb
111 oon mmm
122 aab aab
string strsql = "SELECT left(StrucID0,12) as ID from TMemo where left(StrucID0,12) IN (select BZID from BList) and Content like '%" + search + "%'" Group by left(StrucID0,12)";我想重中之重在于group by 语句!!根本无须distinct