教一条SQL,主要是排序问题.
tableA 变换后
num id name num id name
1 001 A 1 j001 A
2 002 B 6 j001 A
3 003 C 7 j001 A
4 004 D 2 k002 B
5 005 E 8 k002 B
6 k001 A 3 j003 C
7 j001 A 9 j003 C
8 k002 B 4 004 D
9 j003 C 10 004 D
10 null D 5 005 E
11 a005 E 11 005 E
规则就是:name值相同的看id值的第一位是否是j或k(如果j,k都有,升序取前),如果不为j,k升序取前,null排最后.谢谢!
[解决办法]
select * from tableA where left(id,1)=j group by name ordy by name asc
union
select * from tableA where left(id,1)=k group by name ordy by name asc
union
select * from tableA where left(id,1)!=j and left(id,1)!=k group by name ordy by name asc
