--trySELECT max(id), sender
FROM Dv_Message
WHERE (incept = 'ssalfc') AND (flag = 0) AND (isSend = 1) AND (delR = 0)
GROUP BY sender--or
SELECT min(id), sender
FROM Dv_Message
WHERE (incept = 'ssalfc') AND (flag = 0) AND (isSend = 1) AND (delR = 0)
GROUP BY sender
FROM Dv_Message
WHERE (incept = 'ssalfc') AND (flag = 0) AND (isSend = 1) AND (delR = 0)
GROUP BY sender--or
SELECT min(id), sender
FROM Dv_Message
WHERE (incept = 'ssalfc') AND (flag = 0) AND (isSend = 1) AND (delR = 0)
GROUP BY sender
那么同一个 sender 会对应几个 不同的 id 你到底要哪一个?
说具体一点如果你要是想要最大的id
这么写SELECT max(id) , sender
FROM Dv_Message
WHERE (incept = 'ssalfc') AND (flag = 0) AND (isSend = 1) AND (delR = 0)
GROUP BY sender
create table 表(a varchar(10),b varchar(10),c money,d varchar(10),e money)
insert 表 select 'pur_b','m0',20.0000,'X',20.0000
union all select 'pur_b','m0',20.0000,'XX',40.0000
union all select 'pur_b','m0',20.0000,'m1',20.0000
union all select 'pur_b','m0',20.0000,'m2',20.0000
union all select 'pur_b','m0',20.0000,'m3',20.0000
union all select 'pur_b','m0',20.0000,'m4',20.0000
union all select 'pur_b','m2',10.0000,'m2-1',10.0000
union all select 'pur_b','m2',10.0000,'m2-2',10.0000
union all select 'pur_b','m2',10.0000,'m2-3',10.0000
union all select 'pur_b','m2',10.0000,'m2-4',10.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-1',50.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-2',100.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-3',50.0000
go
select * from 表
--处理
select id=identity(int,1,1),* into #t from 表select a=case id when (select min(id) from #t where a=a.a)
then a else '' end
,b=case id when (select min(id) from #t where a=a.a and b=a.b)
then b else '' end
,c,d,e
from #t adrop table #t
go--删除测试环境
drop table 表/*a b c d e
---------- ---------- --------------------- ---------- ---------------------
pur_b m0 20.0000 X 20.0000
pur_b m0 20.0000 XX 40.0000
pur_b m0 20.0000 m1 20.0000
pur_b m0 20.0000 m2 20.0000
pur_b m0 20.0000 m3 20.0000
pur_b m0 20.0000 m4 20.0000
pur_b m2 10.0000 m2-1 10.0000
pur_b m2 10.0000 m2-2 10.0000
pur_b m2 10.0000 m2-3 10.0000
pur_b m2 10.0000 m2-4 10.0000
pur_a m2-1 50.0000 m2-1-1 50.0000
pur_a m2-1 50.0000 m2-1-2 100.0000
pur_a m2-1 50.0000 m2-1-3 50.0000(所影响的行数为 13 行)
(所影响的行数为 13 行)a b c d e
---------- ---------- --------------------- ---------- ---------------------
pur_b m0 20.0000 X 20.0000
20.0000 XX 40.0000
20.0000 m1 20.0000
20.0000 m2 20.0000
20.0000 m3 20.0000
20.0000 m4 20.0000
m2 10.0000 m2-1 10.0000
10.0000 m2-2 10.0000
10.0000 m2-3 10.0000
10.0000 m2-4 10.0000
pur_a m2-1 50.0000 m2-1-1 50.0000
50.0000 m2-1-2 100.0000
50.0000 m2-1-3 50.0000
*/