try:
SELECT BMH,
X=DWDM+'6'+RIGHT(YXSM,2)+RIGHT(1000+(SELECT COUNT(DISTINCT(ZYDM)) FROM SBK WHERE ZYDM<=A.ZYDM),3) +
RIGHT(10000+(SELECT COUNT(*) FROM SBK WHERE YXSM+ZYDM=A.YXSM+A.ZYDM AND YXSM+ZYDM+BMH<=A.YXSM+A.ZYDM+A.BMH),4) FROM SBK A order by case when left(bmh,4)='你说的特定debmh的前四位' then null else left(bmh,4) end
这样实现的就是先排,最后面加一个desc就是后排,你要实现的是这个功能吗?
SELECT BMH,
X=DWDM+'6'+RIGHT(YXSM,2)+RIGHT(1000+(SELECT COUNT(DISTINCT(ZYDM)) FROM SBK WHERE ZYDM<=A.ZYDM),3) +
RIGHT(10000+(SELECT COUNT(*) FROM SBK WHERE YXSM+ZYDM=A.YXSM+A.ZYDM AND YXSM+ZYDM+BMH<=A.YXSM+A.ZYDM+A.BMH),4) FROM SBK A order by case when left(bmh,4)='你说的特定debmh的前四位' then null else left(bmh,4) end
这样实现的就是先排,最后面加一个desc就是后排,你要实现的是这个功能吗?
set @str='6001'SELECT BMH,
X=DWDM+'6'+RIGHT(YXSM,2)+RIGHT(1000+(SELECT COUNT(DISTINCT(ZYDM)) FROM SBK WHERE ZYDM<=A.ZYDM),3) +
RIGHT(10000+(SELECT COUNT(*) FROM SBK WHERE YXSM+ZYDM=A.YXSM+A.ZYDM AND YXSM+ZYDM+BMH<=A.YXSM+A.ZYDM+A.BMH),4)
FROM SBK A
order by (case when left(bmh,4)=@str then 1 else 2 end)
经测试,不得行。下面是测试后的数据(指定的是4116,结果还是被排到中间去了):YXSM,ZYDM,BMH,KSBH
'001','010105','321698158','106376010010001'
'001','010105','321698832','106376010010002'
'001','010105','370897796','106376010010003'
'001','010105','370897839','106376010010004'
'001','010105','371398315','106376010010005'
'001','010105','371398675','106376010010006'
'001','010105','410498949','106376010010007'
'001','010105','411697056','106376010010008'
'001','010105','411697462','106376010010009'
'001','010105','411697952','106376010010010'
'001','010105','422098787','106376010010011'
'001','010105','431599415','106376010010012'
'001','010105','460499070','106376010010013'
'001','010105','500192801','106376010010014'
'001','010105','500298653','106376010010015'
set @str='6001'
SELECT BMH,
X=DWDM
+'6'
+RIGHT(YXSM,2)
+RIGHT(1000
+( SELECT COUNT(DISTINCT(ZYDM))
FROM SBK
WHERE ZYDM<=A.ZYDM),3)
+RIGHT(10000
+( SELECT COUNT(*)
FROM SBK
WHERE YXSM+ZYDM=A.YXSM+A.ZYDM
AND YXSM+ZYDM+BMH<=A.YXSM+A.ZYDM+A.BMH),4) FROM SBK AORDER BY ( REPLACE
( bmh,
left(bmh,4),
(CASE WHEN left(bmh,4)=@str then '0000' else left(bmh,4) end)
)
)
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[YXSM] [varchar] (3),
[ZYDM] [varchar] (6),
[BMDDM] [varchar] (4),
[BMH] [varchar] (9)
)INSERT INTO @test (YXSM, ZYDM, BMDDM, BMH)
SELECT
YXSM, ZYDM, BMDDM, BMH
FROM dbo.test
ORDER BY YXSM, ZYDM
, (CASE WHEN BMDDM = '4116' THEN 1 ELSE 0 END) DESC
, BMHSELECT
YXSM, ZYDM, BMDDM, BMH,
X = '10637' + '6' + RIGHT(YXSM,2)
+ RIGHT(1000 + (SELECT COUNT(DISTINCT(ZYDM)) FROM @test WHERE ZYDM<=A.ZYDM), 3)
+ RIGHT(10000 + (SELECT COUNT(*) FROM @test WHERE ID <=A.ID AND YXSM+ZYDM = A.YXSM + A.ZYDM), 4) FROM @test A
ORDER BY IDGO