SJZL0020120703_78 东莞
SJZL0020120703_79 东莞
SJZL0020120703_80 东莞
SJZL0020120703_81 深圳
SJZL0020120703_82 东莞
SJZL0020120703_83 东莞
SJZL0020120703_84 东莞
SJZL0020120703_86 深圳
SJZL0020120703_89 东莞
SJZL0020120703_93 东莞
SJZL0020120703_98 东莞
SJZL0020120703_102 深圳
SJZL0020120703_112 东莞
SJZL0020120704_1 东莞
SJZL0020120704_5 东莞
SJZL0020120704_10 深圳
SJZL0020120704_20 东莞
SJZL0020120704_50 深圳
我用的数据库是SQL2005,数据库名是cjh,流水号字段:danh,仓库字段:cangku,怎么从最后面往事查数据啊,况且cangku字段是‘东莞’的。比如:最后一条东莞是SJZL0020120704_20,往上查第2条是SJZL0020120704_5,再上一条是SJZL0020120704_1,依次类推,他们都是有些连续,有些不连续的。就和上面显示的数据差不错,请大家帮帮忙。 最好是一次查一条,因为我查到的这数据是显示到label1里的,一次只能显示一条。
7 个解决方案
#1
select * from T where cangku='东莞' order by to_number(substr(hei,0,14)) desc,to_number(substr(hei,16)) desc
#2
先把仓库字段 做 模糊查询: %东莞
然后再降序排:Order By ... desc
然后再降序排:Order By ... desc
#3
DECLARE @Temp Table(ID NVARCHAR(255), Name NVARCHAR(255));
INSERT INTO @Temp
SELECT 'SJZL0020120704_1', '东莞' UNION ALL
SELECT 'SJZL0020120704_5', '东莞' UNION ALL
SELECT 'SJZL0020120704_10', '深圳' UNION ALL
SELECT 'SJZL0020120704_20', '东莞' UNION ALL
SELECT 'SJZL0020120704_50', '深圳'
SELECT *
FROM @Temp
WHERE Name = '东莞'
ORDER BY CAST(SUBSTRING(ID, 16, LEN(ID) - 15 + 1) AS INT) ASC
INSERT INTO @Temp
SELECT 'SJZL0020120704_1', '东莞' UNION ALL
SELECT 'SJZL0020120704_5', '东莞' UNION ALL
SELECT 'SJZL0020120704_10', '深圳' UNION ALL
SELECT 'SJZL0020120704_20', '东莞' UNION ALL
SELECT 'SJZL0020120704_50', '深圳'
SELECT *
FROM @Temp
WHERE Name = '东莞'
ORDER BY CAST(SUBSTRING(ID, 16, LEN(ID) - 15 + 1) AS INT) ASC
#4
这是结果:
SJZL0020120704_1 东莞
SJZL0020120704_5 东莞
SJZL0020120704_20 东莞
SJZL0020120704_1 东莞
SJZL0020120704_5 东莞
SJZL0020120704_20 东莞
#5
#3正解
#6
3楼正解
#7
with temp as(select row_number() over(order by 编号 desc) as rn,编号,cangk,hei from cjh where cangk='东莞')select * from temp where rn=1
一次获取一个值,想要获取下一个值,把1改成2,以此类推
#1
select * from T where cangku='东莞' order by to_number(substr(hei,0,14)) desc,to_number(substr(hei,16)) desc
#2
先把仓库字段 做 模糊查询: %东莞
然后再降序排:Order By ... desc
然后再降序排:Order By ... desc
#3
DECLARE @Temp Table(ID NVARCHAR(255), Name NVARCHAR(255));
INSERT INTO @Temp
SELECT 'SJZL0020120704_1', '东莞' UNION ALL
SELECT 'SJZL0020120704_5', '东莞' UNION ALL
SELECT 'SJZL0020120704_10', '深圳' UNION ALL
SELECT 'SJZL0020120704_20', '东莞' UNION ALL
SELECT 'SJZL0020120704_50', '深圳'
SELECT *
FROM @Temp
WHERE Name = '东莞'
ORDER BY CAST(SUBSTRING(ID, 16, LEN(ID) - 15 + 1) AS INT) ASC
INSERT INTO @Temp
SELECT 'SJZL0020120704_1', '东莞' UNION ALL
SELECT 'SJZL0020120704_5', '东莞' UNION ALL
SELECT 'SJZL0020120704_10', '深圳' UNION ALL
SELECT 'SJZL0020120704_20', '东莞' UNION ALL
SELECT 'SJZL0020120704_50', '深圳'
SELECT *
FROM @Temp
WHERE Name = '东莞'
ORDER BY CAST(SUBSTRING(ID, 16, LEN(ID) - 15 + 1) AS INT) ASC
#4
这是结果:
SJZL0020120704_1 东莞
SJZL0020120704_5 东莞
SJZL0020120704_20 东莞
SJZL0020120704_1 东莞
SJZL0020120704_5 东莞
SJZL0020120704_20 东莞
#5
#3正解
#6
3楼正解
#7
with temp as(select row_number() over(order by 编号 desc) as rn,编号,cangk,hei from cjh where cangk='东莞')select * from temp where rn=1
一次获取一个值,想要获取下一个值,把1改成2,以此类推