create table tb(Id int, proname varchar(32), publishdate datetime,city varchar(50), boss varchar(32))
insert tb select 1, 'x1', '2007-10-11','北京','boss1'
union all select 2, 'x2', '2007-10-11','北京', 'boss1'
union all select 3, 'x3', '2007-10-11', '北京','boss1'
union all select 4, 'y1', '2007-10-11', '上海','boss2'
union all select 5, 'y2', '2007-10-11', '上海','boss2'
union all select 6, 'y3', '2007-10-11', '上海','boss2'
union all select 7, 'z1', '2007-10-11','长沙', 'boss3'
union all select 8, 'z2', '2007-10-11', '长沙','boss3'
union all select 9, 'z3', '2007-10-11', '长沙','boss3'
union all select 10, 'n1', '2007-10-11','北京', 'boss4'
union all select 11, 'n2', '2007-10-11', '北京','boss4'
union all select 12, 'n3', '2007-10-11', '北京','boss4'
union all select 13, 'y1', '2007-10-11', '长沙','boss5'现在要得出这样的排序结果
id proname publishdate city boss
1, 'x1', '2007-10-11','北京','boss1'
10,'n1', '2007-10-11','北京','boss4'
2, 'x2', '2007-10-11','北京','boss1'
11,'n2', '2007-10-11','北京','boss4'
3, 'x3', '2007-10-11', '北京','boss1'
12, 'n3', '2007-10-11','北京','boss4'
4, 'y1', '2007-10-11', '上海','boss2'
7, 'z1', '2007-10-11','长沙', 'boss3'
13, 'y1', '2007-10-11', '长沙','boss5'
5, 'y2', '2007-10-11', '上海','boss2'
8, 'z2', '2007-10-11', '长沙','boss3'
6, 'y3', '2007-10-11', '上海','boss2'
9, 'z3', '2007-10-11', '长沙','boss3'
就是我指定城市字段的记录要优先排序,但要求boss值之间交叉不能连续,除非没其它不同值,后面其它城市就按先城市值交叉,后boss值交叉显示不知道能实现不,请指教!
insert tb select 1, 'x1', '2007-10-11','北京','boss1'
union all select 2, 'x2', '2007-10-11','北京', 'boss1'
union all select 3, 'x3', '2007-10-11', '北京','boss1'
union all select 4, 'y1', '2007-10-11', '上海','boss2'
union all select 5, 'y2', '2007-10-11', '上海','boss2'
union all select 6, 'y3', '2007-10-11', '上海','boss2'
union all select 7, 'z1', '2007-10-11','长沙', 'boss3'
union all select 8, 'z2', '2007-10-11', '长沙','boss3'
union all select 9, 'z3', '2007-10-11', '长沙','boss3'
union all select 10, 'n1', '2007-10-11','北京', 'boss4'
union all select 11, 'n2', '2007-10-11', '北京','boss4'
union all select 12, 'n3', '2007-10-11', '北京','boss4'
union all select 13, 'y1', '2007-10-11', '长沙','boss5'现在要得出这样的排序结果
id proname publishdate city boss
1, 'x1', '2007-10-11','北京','boss1'
10,'n1', '2007-10-11','北京','boss4'
2, 'x2', '2007-10-11','北京','boss1'
11,'n2', '2007-10-11','北京','boss4'
3, 'x3', '2007-10-11', '北京','boss1'
12, 'n3', '2007-10-11','北京','boss4'
4, 'y1', '2007-10-11', '上海','boss2'
7, 'z1', '2007-10-11','长沙', 'boss3'
13, 'y1', '2007-10-11', '长沙','boss5'
5, 'y2', '2007-10-11', '上海','boss2'
8, 'z2', '2007-10-11', '长沙','boss3'
6, 'y3', '2007-10-11', '上海','boss2'
9, 'z3', '2007-10-11', '长沙','boss3'
就是我指定城市字段的记录要优先排序,但要求boss值之间交叉不能连续,除非没其它不同值,后面其它城市就按先城市值交叉,后boss值交叉显示不知道能实现不,请指教!
解决方案 »
- sql语句:在一个表中查询符合条件的数据
- |M| SQL2005 我在企业管理器中打开表 然后在里面改数据 他不让保存 提示:错误源: Net sqlClinet Data Provider. 错误信息 ...
- SQL分类统计语句求教!
- 系统数据库造质疑~~~~
- 事件查看器出现如下错误:SQLServerAgent could not be started
- 是sql server的bug还是语句的错误?
- 分页优化,请大侠们帮着看一下
- 100分跪求~!~ 递归类型检索
- 高分求教数据转移效率问题
- 急聘全职Delphi程序员!
- 如保将查询分析器中的查询结果分文件分工作表导出到xls中
- 听说这高手如云,请大家看看这个问题可否实现
--------------------------
你的结果是不是指定'北京'了,这样北京就在顶部了,同时boss 不连续
剩下的记录只要boss交替就行了
把数据取出来处理一下应该可以实现
insert tb select 1, 'x1', '2007-10-11','北京','boss1'
union all select 2, 'x2', '2007-10-11','北京', 'boss1'
union all select 3, 'x3', '2007-10-11', '北京','boss1'
union all select 4, 'y1', '2007-10-11', '上海','boss2'
union all select 5, 'y2', '2007-10-11', '上海','boss2'
union all select 6, 'y3', '2007-10-11', '上海','boss2'
union all select 7, 'z1', '2007-10-11','长沙', 'boss3'
union all select 8, 'z2', '2007-10-11', '长沙','boss3'
union all select 9, 'z3', '2007-10-11', '长沙','boss3'
union all select 10, 'n1', '2007-10-11','北京', 'boss4'
union all select 11, 'n2', '2007-10-11', '北京','boss4'
union all select 12, 'n3', '2007-10-11', '北京','boss4'
union all select 13, 'y1', '2007-10-11', '长沙','boss5'
goselect * from tb
order by case city when '北京' then 1 else 2 end ,
cast(substring(proname,2,len(proname)) as int),
left(proname,1)
drop table tb
/*
Id proname publishdate city boss
----------- -------------------------------- ----------------------- -------------------------------------------------- --------------------------------
10 n1 2007-10-11 00:00:00.000 北京 boss4
1 x1 2007-10-11 00:00:00.000 北京 boss1
11 n2 2007-10-11 00:00:00.000 北京 boss4
2 x2 2007-10-11 00:00:00.000 北京 boss1
12 n3 2007-10-11 00:00:00.000 北京 boss4
3 x3 2007-10-11 00:00:00.000 北京 boss1
4 y1 2007-10-11 00:00:00.000 上海 boss2
13 y1 2007-10-11 00:00:00.000 长沙 boss5
7 z1 2007-10-11 00:00:00.000 长沙 boss3
5 y2 2007-10-11 00:00:00.000 上海 boss2
8 z2 2007-10-11 00:00:00.000 长沙 boss3
6 y3 2007-10-11 00:00:00.000 上海 boss2
9 z3 2007-10-11 00:00:00.000 长沙 boss3(13 行受影响)
*/
龟,你的前提 是proname有规则 ,最后一位能让你排序,如果没有规则 白搭
就是假如我指定要求北京的优先显示,如果出现北京只有一个boss的话,就是先必须是北京的BOSS排在第一条,再其它城市BOSS与北京BOss这间交替!我自己都晕了总的来说就是:指定城市如BOSS字段有多个值,就先指定城市BOSS之间交替,再其它城市交替,BOSS交替;如果指定城市BOSS字段只有一个值的话,就指定城市与其它城市交替(第一条记录是指定城市的),BOSS交替
:)
如果理论上都没有排序的办法 那SQL恐怕更无能为力