create table t_ht
(
bh varchar(10)
)
insert into t_ht select 'HTZZ-1'
insert into t_ht select 'HTZZ-2'
insert into t_ht select 'HTZZ-3'
insert into t_ht select 'HTZZ-4'
insert into t_ht select 'HTZZ-5'
insert into t_ht select 'HTZZ-6'
insert into t_ht select 'HTZZ-7'
insert into t_ht select 'HTZZ-8'
insert into t_ht select 'HTZZ-9'
insert into t_ht select 'HTZZ-10'
insert into t_ht select 'HTZZ-11'
insert into t_ht select 'HTZZ-12'
insert into t_ht select 'HTZZ-13'create table t_ht1
(
bh varchar(10)
)
insert into t_ht1 select 'HTGZ-1'
insert into t_ht1 select 'HTGZ-2'
insert into t_ht1 select 'HTGZ-3'
insert into t_ht1 select 'HTGZ-4'
insert into t_ht1 select 'HTGZ-5'
insert into t_ht1 select 'HTGZ-6'
insert into t_ht1 select 'HTGZ-7'
insert into t_ht1 select 'HTGZ-8'
insert into t_ht1 select 'HTGZ-9'
insert into t_ht1 select 'HTGZ-10'
insert into t_ht1 select 'HTGZ-11'
insert into t_ht1 select 'HTGZ-12'
insert into t_ht1 select 'HTGZ-13'drop table t_ht
drop table t_ht1/*
想要得到这样的排序结果
bh
HTZZ-1
HTZZ-2
HTZZ-3
HTZZ-4
HTZZ-5
HTZZ-6
HTZZ-7
HTZZ-8
HTZZ-9
HTZZ-10
HTZZ-11
HTZZ-12
HTZZ-13
HTGZ-1
HTGZ-2
HTGZ-3
HTGZ-4
HTGZ-5
HTGZ-6
HTGZ-7
HTGZ-8
HTGZ-9
HTGZ-10
HTGZ-11
HTGZ-12
HTGZ-13
*/这是原来的SQL写法,结果不对select * from (SELECT t_ht.bh
FROM t_ht
union all
SELECT
t_ht1.bh
FROM t_ht1 ) a
order by cast(right(a.bh,len(a.bh)-charindex('-',a.bh)) as int)请大家指教
解决方案 »
- 急!急!急!急!从sql 2005中导出数据到excel中,出现如下错误?????
- 有两个表,表结构都一样,我想把这两个表select *的结果集合并到一个结集中,请问如何写?
- 统计多个表的字段.
- sqlServer2005的datalength函数计算字符串长度的疑问??
- 请留意!!!
- 根據表名獲得各個字段名稱及字段長度,不能用Select *
- 高手求助!怎么用SSIS实现HTTP远程下载文件~!
- 建库错误:COLLATE(在线)
- 可不可以这样建外键?User表和File表,在File表中有UserID1,UserID1,可不可以与User表中User相关联?
- 急!我的SQLServer2000的企业管理器突然打不开了,怎么回事?请高手指教
- 对如下的3个表查询,SQL语句如何写?
- 关于创建数据库索引的问题,很急,马上给分
FROM t_ht
union all
SELECT
t_ht1.bh
FROM t_ht1 ) a
order by left(bh,charindex('-',bh)-1) desc,cast(right(a.bh,len(a.bh)-charindex('-',a.bh)) as int)
select * from (SELECT t_ht.bh FROM t_ht
union all SELECT t_ht1.bh FROM t_ht1 ) a
order by left(bh,4) desc,cast(stuff(bh,1,5,'') as int)