比如表A:
物料 价格
水笔 70
水笔 80
水笔 60
钢笔 90
钢笔 70
钢笔 80 我要通过SQL语句得到表B:如下:物料 价格 FID
水笔 70 1
水笔 80 2
水笔 60 3
钢笔 90 1
钢笔 70 2
钢笔 80 3
物料 价格
水笔 70
水笔 80
水笔 60
钢笔 90
钢笔 70
钢笔 80 我要通过SQL语句得到表B:如下:物料 价格 FID
水笔 70 1
水笔 80 2
水笔 60 3
钢笔 90 1
钢笔 70 2
钢笔 80 3
(
物料 varchar(20),
价格 int,
FID int
)
insert into #TT2 select '水笔',70,1
insert into #TT2 select '水笔',80,2
insert into #TT2 select '水笔',60,3
insert into #TT2 select '钢笔',90,1
insert into #TT2 select '钢笔',70,2
insert into #TT2 select '钢笔',80,3declare @sql nvarchar(4000)
set @sql='select 物料'
select @sql=@sql+',max(case when FID='+cast(FID as varchar)+' then 价格 else 0 end) [价格'+ltrim(FID)+']'
from (select distinct FID from #TT2)TT
set @sql=@sql+' from #TT2 group by 物料 order by 物料 desc'
exec (@sql)
物料 价格1 价格2 价格3
-------------------- ----------- ----------- -----------
水笔 70 80 60
钢笔 90 70 80
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-06 17:09:02
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([物料] varchar(4),[价格] int)
insert [tb]
select '水笔',70 union all
select '水笔',80 union all
select '水笔',60 union all
select '钢笔',90 union all
select '钢笔',70 union all
select '钢笔',80
--------------开始查询--------------------------
select *,fid=row_number()over(partition by 物料 order by getdate()) from tb
----------------结果----------------------------
/* 物料 价格 fid
---- ----------- --------------------
钢笔 90 1
钢笔 70 2
钢笔 80 3
水笔 70 1
水笔 80 2
水笔 60 3(6 行受影响)
*/
declare @tb table (物料 varchar(4),价格 int)
insert into @tb
select '水笔',70 union all
select '水笔',80 union all
select '水笔',60 union all
select '钢笔',90 union all
select '钢笔',70 union all
select '钢笔',80--------->sql 2005
;with szy as(
select *,fid=row_number()over(partition by 物料 order by getdate())
from @tb
)select * from szy
---------->sql 2000select *,id=identity(int,1,1) into #t from @tbselect 物料,价格,id=(select count(*) from #t where 物料 =t.物料 and id<=t.id)
from #t t
物料 价格 id
---- ----------- -----------
水笔 70 1
水笔 80 2
水笔 60 3
钢笔 90 1
钢笔 70 2
钢笔 80 3(6 行受影响)drop table #t
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-06 17:09:02
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([物料] varchar(4),[价格] int)
insert [tb]
select '水笔',70 union all
select '水笔',80 union all
select '水笔',60 union all
select '钢笔',90 union all
select '钢笔',70 union all
select '钢笔',80
--------------开始查询--------------------------
select *,id=identity(int) into #t from tb
select 物料,价格,fid=(select count(1)+1 from #t where T.id>id and 物料=t.物料)
from #t t
drop table #t
----------------结果----------------------------
/* 物料 价格 fid
---- ----------- -----------
水笔 70 1
水笔 80 2
水笔 60 3
钢笔 90 1
钢笔 70 2
钢笔 80 3(6 行受影响)
*/
-- Author :GUGUDA(烤鸭,无聊地抄袭小F的数据)
-- Date :2009-12-06 17:09:02
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([物料] varchar(4),[价格] int)
insert [tb]
select '水笔',70 union all
select '水笔',80 union all
select '水笔',60 union all
select '钢笔',90 union all
select '钢笔',70 union all
select '钢笔',80SELECT
*
,(SELECT COUNT(1) FROM TB T2 WHERE T2.[物料]=T1.[物料] AND T2.[价格]<=T1.[价格])
FROM TB T1
ORDER BY [物料],[价格]
/*
钢笔 70 1
钢笔 80 2
钢笔 90 3
水笔 60 1
水笔 70 2
水笔 80 3
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-06 17:09:02
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([物料] varchar(4),[价格] int)
insert [tb]
select '水笔',70 union all
select '水笔',80 union all
select '水笔',60 union all
select '钢笔',90 union all
select '钢笔',70 union all
select '钢笔',80
--------------开始查询--------------------------
select *,id=identity(int) into #t from tb
select
物料,价格,fid=(select count(1)+1 from #t where T.id>id and 物料=t.物料)
from
#t t
order by 1
drop table #t
----------------结果----------------------------
/* 物料 价格 fid
---- ----------- -----------
钢笔 90 1
钢笔 70 2
钢笔 80 3
水笔 70 1
水笔 80 2
水笔 60 3(6 行受影响)
*/
select
物料,价格,fid=(select count(1)+1 from #t where T.id>id and 物料=t.物料)
from
#t t
越仔细越好呀。谢谢了。
go
create table [tb]([物料] varchar(4),[价格] int)
insert [tb]
select '水笔',70 union all
select '水笔',80 union all
select '水笔',60 union all
select '钢笔',90 union all
select '钢笔',70 union all
select '钢笔',80 union all
select '钢笔',20
--------------开始查询--------------------------
select *,id=identity(int) into #t from tbselect
物料,价格,fid=(select count(1)+1 from #t where T.id>id and 物料=t.物料)
into #t2
from
#t t
order by 1
declare @sql nvarchar(4000)
set @sql='select 物料'
select @sql=@sql+',max(case when FID='+cast(fid as varchar)+' then 价格 else 0 end) [价格'+ltrim(FID)+']'
from (select distinct FID from #t2)TT
set @sql=@sql+' from #t2 group by 物料 order by 物料 desc'
exec (@sql)
物料 价格1 价格2 价格3 价格4
---- ----------- ----------- ----------- -----------
水笔 70 80 60 0
钢笔 90 70 80 20
drop table tb,#t,#t2
如下名次查询的处理示例,只不过我这个是全表统一排名,你那个还需要按相同物料进行排名,加上物料相同这个条件即可.
名次查询的处理示例表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8