解决方案 »
- 急急急, sql server装不上。。
- sql2005 以有数据表水平分4个区后,insert数据,会在原表和4个分区同时insert数据
- 求汇总统计一SQL语句。。。。。。。。
- 【求助】如何将SQL查询结果的列数据连接成一行(在SQL中用函数或存储过程实现)
- SQL语句里怎样比较数据库里日期字段 与指定查询日期相等
- SQL存储过程高难度问题,求高手解答
- SQL的存储过程 asp.net调用
- 如何转变SmallDateTime型的数据成下面的格式?2002-08-28 15:30:00换成20020828
- SQL+ASP如何提升访问速度
- 主表多个字段 与一个表对应如何建立查询
- 大侠们 来帮小弟看看下面的SQL文该怎么写啊~~??谢谢了
- sql server 日志
???
是什么意思?select id,jjdbh,pihao,tzdid,orderinfo
from tb
where jjdbh='' and tzdid=''
order by pihao ,orderinfo desc
不知道能不能达到你的要求?
http://topic.csdn.net/u/20081204/16/1ad697d0-b984-4723-814f-7e98bce72c2b.html
set @i=0
create table #t(id int identity(1,1),pid int,orderinfo int)
insert into #t(pid)
select id from tb_JjdPcxjData where tzdid='20081111160559RvD'
ORDER BY CAST(PARSENAME(REPLACE(PIHAO,'-','.'),3) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),2) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),1) AS INT)
update #t set @i=@i+1,orderinfo=@i
update tb_JjdPcxjData set tb_JjdPcxjData.orderinfo=#t.orderinfo from #t where #t.pid=tb_JjdPcxjData.id这是我写的,不过这是对pihao为'10-9-1'之类的时候,所以只是其中一种情况
--> (让你望见影子的墙)生成测试数据,时间:2008-12-04
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[jjdbh] nvarchar(8),[pihao] nvarchar(7),[tzdid] nvarchar(17),[orderin)fo] nvarchar(20))
go
Insert tb
select '6197',N'07105006','10-9-1',N'20081111160559RvD',null union all
select '6712',N'07105006','10-19-3',N'20081111160559RvD',null union all
select '6099',N'07105006','10-19-4',N'20081111160559RvD',null union all
select '5570',N'07105006','10-29-1',N'20081111160559RvD',null union all
select '6079',N'07105006','10-30-1',N'20081111160559RvD',null union all
select '6345',N'07115006','11-2-4',N'20081111160559RvD',null union all
select '6807',N'07115006','11-5-1',N'20081111160559RvD',null union all
select '5210',N'07115006','11-13-2',N'20081111160559RvD',null union all
select '5185',N'07115006','11-16-1',N'20081111160559RvD',null union all
select '6665',N'07115006','11-26-4',N'20081111160559RvD',null
Go
Select * from tb
select *
from tb
where jjdbh='07105006' and tzdid='20081111160559RvD'
order by CAST(PARSENAME(REPLACE(PIHAO,'-','.'),3) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),2) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),1) AS INT) 6197 07105006 10-9-1 20081111160559RvD NULL
6712 07105006 10-19-3 20081111160559RvD NULL
6099 07105006 10-19-4 20081111160559RvD NULL
5570 07105006 10-29-1 20081111160559RvD NULL
6079 07105006 10-30-1 20081111160559RvD NULL
INSERT TB
SELECT '6712', '07105006', '10-19-3', '20081111160559RvD', null UNION ALL
SELECT '6099', '07105006', '10-19-4', '20081111160559RvD', null UNION ALL
SELECT '5570', '07105006', '10-29-1', '20081111160559RvD', null UNION ALL
SELECT '6079', '07105006', '10-30-1', '20081111160559RvD', null UNION ALL
SELECT '6197', '07105006', '10-9-1', '20081111160559RvD', null UNION ALL
SELECT '5210', '07115006', '11-13-2', '20081111160559RvD', null UNION ALL
SELECT '5185', '07115006', '11-16-1', '20081111160559RvD', null UNION ALL
SELECT '6345', '07115006', '11-2-4', '20081111160559RvD', null UNION ALL
SELECT '6665', '07115006', '11-26-4', '20081111160559RvD', null UNION ALL
SELECT '6807', '07115006', '11-5-1', '20081111160559RvD', nullselect id,jjdbh,pihao,tzdid,pihao as orderinfo
from TB
where jjdbh='07105006' and tzdid='20081111160559RvD'
order by CAST(PARSENAME(REPLACE(PIHAO,'-','.'),3) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),2) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),1) AS INT)
from TB
where jjdbh='07105006' and tzdid='20081111160559RvD'
order by CAST(PARSENAME(REPLACE(PIHAO,'-','.'),3) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),2) AS INT),
CAST(PARSENAME(REPLACE(pihao,'-','.'),1) AS INT)
/*
6197 07105006 10-9-1 20081111160559RvD 10-9-1
6712 07105006 10-19-3 20081111160559RvD 10-19-3
6099 07105006 10-19-4 20081111160559RvD 10-19-4
5570 07105006 10-29-1 20081111160559RvD 10-29-1
6079 07105006 10-30-1 20081111160559RvD 10-30-1
*/
INSERT @TB
SELECT '6712', '07105006', '10-19-3', '20081111160559RvD', null UNION ALL
SELECT '6099', '07105006', '10-19-4', '20081111160559RvD', null UNION ALL
SELECT '5570', '07105006', '10-29-1', '20081111160559RvD', null UNION ALL
SELECT '6079', '07105006', '10-30-1', '20081111160559RvD', null UNION ALL
SELECT '6197', '07105006', '10-9-1', '20081111160559RvD', null UNION ALL
SELECT '5210', '07115006', '11-13-2', '20081111160559RvD', null UNION ALL
SELECT '5185', '07115006', '11-16-1', '20081111160559RvD', null UNION ALL
SELECT '6345', '07115006', '11-2-4', '20081111160559RvD', null UNION ALL
SELECT '6665', '07115006', '11-26-4', '20081111160559RvD', null UNION ALL
SELECT '6807', '07115006', '11-5-1', '20081111160559RvD', null
SELECT * INTO #
FROM @TB
ORDER BY CAST(PARSENAME(REPLACE(PIHAO,'-','.'),3) AS INT),
CAST(PARSENAME(REPLACE(PIHAO,'-','.'),2) AS INT),
CAST(PARSENAME(REPLACE(PIHAO,'-','.'),1) AS INT)SELECT *,ID2=IDENTITY(INT,1,1) INTO #T FROM #UPDATE @TB SET orderinfo=id2
FROM @TB AS A JOIN #T AS B ON A.ID=B.IDSELECT * FROM @TB ORDER BY orderinfoDROP TABLE #
DROP TABLE #T
/*
id jjdbh pihao tzdid orderinfo
----------- ---------- ---------- ------------------------- -----------
6197 07105006 10-9-1 20081111160559RvD 1
6712 07105006 10-19-3 20081111160559RvD 2
6099 07105006 10-19-4 20081111160559RvD 3
5570 07105006 10-29-1 20081111160559RvD 4
6079 07105006 10-30-1 20081111160559RvD 5
6345 07115006 11-2-4 20081111160559RvD 6
6807 07115006 11-5-1 20081111160559RvD 7
5210 07115006 11-13-2 20081111160559RvD 8
5185 07115006 11-16-1 20081111160559RvD 9
6665 07115006 11-26-4 20081111160559RvD 10(10 row(s) affected)
*/
--题目不是很懂declare @test120401 table(id int,jjdbh varchar(30),pihao varchar(30),tzdid varchar(50),orderinfo varchar(20),orderid int)
insert into @test120401(id,jjdbh,pihao,tzdid)
select 6712,'07105006','10-19-3','20081111160559RvD' union all
select 6099,'07105006','10-19-4','20081111160559RvD' union all
select 5570,'07105006','10-29-1','20081111160559RvD' union all
select 6079,'07105006','10-30-1','20081111160559RvD' union all
select 6197,'07105006','10-9-1','20081111160559RvD' union all
select 5210,'07115006','11-13-2','20081111160559RvD' union all
select 5185,'07115006','11-16-1','20081111160559RvD' union all
select 6345,'07115006','11-2-4','20081111160559RvD' union all
select 6665,'07115006','11-26-4','20081111160559RvD' union all
select 6665,'07115006','lot2','20081111160559RvD' union all
select 6637,'07115006','lot3','20081111160559RvD' union all
select 6667,'07115006','lot1','20081111160559RvD' union all
select 6668,'07115006','','20081111160559RvD' union all
select 6807,'07115006','11-5-1','20081111160559RvD'
select *FROM @TEST120401
ORDER BY CASE WHEN PIHAO LIKE 'lot%' then PIHAO WHEN ISNULL(PIHAO,'')='' THEN '0' ELSE
SUBSTRING(PIHAO,1,CHARINDEX('-',PIHAO))+RIGHT('00'+
SUBSTRING(SUBSTRING(PIHAO,CHARINDEX('-',PIHAO)+1,LEN(PIHAO)),1,CHARINDEX('-',SUBSTRING(PIHAO,CHARINDEX('-',PIHAO)+1,LEN(PIHAO)))-1),2)
+RIGHT('00'+REPLACE(RIGHT(PIHAO,2),'-',''),2) END,JJDBH,TZDID
set @i=0
create table #t(id int identity(1,1),pid int,orderinfo int)
insert into #t(pid)
select id from tb_JjdPcxjData where tzdid='20081111160559RvD'
order by case when pihao like 'lot%' then pihao when isnull(pihao,'')='' then '0' else
substring(pihao,1,charindex('-',pihao))+right('00'+
substring(substring(pihao,charindex('-',pihao)+1,len(pihao)),1,charindex('-',substring(pihao,charindex('-',pihao)+1,len(pihao)))-1),2)
+right('00'+replace(right(pihao,2),'-',''),2) end,tzdid ,jjdbhupdate #t set @i=@i+1,orderinfo=@i
update tb_JjdPcxjData set tb_JjdPcxjData.orderinfo=#t.orderinfo from #t where #t.pid=tb_JjdPcxjData.id
set @i=0
create table #t(id int identity(1,1),pid int,orderinfo int)
insert into #t(pid)
select id from tb_JjdPcxjData where tzdid='20081111160559RvD'
order by case when pihao like 'lot%' or isnumeric(pihao)=1 then pihao when isnull(pihao,'')='' or len(pihao)=0 then '1' else
substring(pihao,1,charindex('-',pihao))+right('00'+
substring(substring(pihao,charindex('-',pihao)+1,len(pihao)),1,charindex('-',substring(pihao,charindex('-',pihao)+1,len(pihao)))-1),2)
+right('00'+replace(right(pihao,2),'-',''),2) end,tzdid ,jjdbhupdate #t set @i=@i+1,orderinfo=@i
update tb_JjdPcxjData set tb_JjdPcxjData.orderinfo=#t.orderinfo from #t where #t.pid=tb_JjdPcxjData.id