------------------------------------------------------------------------
-- Author: happyflystone
-- Date : 2009-04-13 23:45:34
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta( bianhao NVARCHAR(3), jianshu INT, shangfu INT, xianjin NVARCHAR(2), dshk INT, zyf INT, jsfs NVARCHAR(2))
Go
INSERT INTO ta
SELECT '001',1,20,'0',200,10,'上付' UNION ALL
SELECT '001',2,20,'0',200,10,'上付' UNION ALL
SELECT '002',3,0,'30',10,15,'现金' UNION ALL
SELECT '002',1,0,'30',10,15,'现金'
GO
--Start
;with t
as
(select rowid= row_number() over (partition by bianhao order by getdate()),*
from ta)
select bianhao = case when rowid = 1 then bianhao else '' end,
jianshu,
shangfu = case when rowid = 1 then shangfu else '' end,
xianjin = case when rowid = 1 then xianjin else '' end,
dshk = case when rowid = 1 then ltrim(dshk) else '' end,zyf,jsfs
from t--Result:
/*
bianhao jianshu shangfu xianjin dshk zyf jsfs
------- ----------- ----------- ------- ------------ ----------- ----
001 1 20 0 200 10 上付
2 0 10 上付
002 3 0 30 10 15 现金
1 0 15 现金
*/
--End
-- Author: happyflystone
-- Date : 2009-04-13 23:45:34
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta( bianhao NVARCHAR(3), jianshu INT, shangfu INT, xianjin NVARCHAR(2), dshk INT, zyf INT, jsfs NVARCHAR(2))
Go
INSERT INTO ta
SELECT '001',1,20,'0',200,10,'上付' UNION ALL
SELECT '001',2,20,'0',200,10,'上付' UNION ALL
SELECT '002',3,0,'30',10,15,'现金' UNION ALL
SELECT '002',1,0,'30',10,15,'现金'
GO
--Start
;with t
as
(select rowid= row_number() over (partition by bianhao order by getdate()),*
from ta)
select bianhao = case when rowid = 1 then bianhao else '' end,
jianshu,
shangfu = case when rowid = 1 then shangfu else '' end,
xianjin = case when rowid = 1 then xianjin else '' end,
dshk = case when rowid = 1 then ltrim(dshk) else '' end,zyf,jsfs
from t--Result:
/*
bianhao jianshu shangfu xianjin dshk zyf jsfs
------- ----------- ----------- ------- ------------ ----------- ----
001 1 20 0 200 10 上付
2 0 10 上付
002 3 0 30 10 15 现金
1 0 15 现金
*/
--End
if object_id('tb') is not null
drop table tb
go
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(bianhao NVARCHAR(3), jianshu INT, shangfu INT, xianjin NVARCHAR(2), dshk INT, zyf INT, jsfs NVARCHAR(2))
INSERT tb SELECT '001',1,20,'0',200,10,'上付'
INSERT tb SELECT '001',2,20,'0',200,10,'上付'
INSERT tb SELECT '002',3,0,'30',10,15,'现金'
INSERT tb SELECT '002',1,0,'30',10,15,'现金'
GO
select id=identity(int,1,1),* into # from tb
select
bianhao = case when px = 1 then bianhao else '' end,
jianshu,
shangfu = case when px = 1 then shangfu else '' end,
xianjin = case when px = 1 then xianjin else '' end,
dshk = case when px = 1 then ltrim(dshk) else '' end,
zyf,
jsfs
from (select px=(select count(1) from # where bianhao=a.bianhao and id<=a.id),* from # a) t
drop table #
/*
bianhao jianshu shangfu xianjin dshk zyf jsfs
------- ----------- ----------- ------- ------------ ----------- ----
001 1 20 0 200 10 上付
2 0 10 上付
002 3 0 30 10 15 现金
1 0 15 现金(4 行受影响)
*/
create proc sp_N
AS
select id=identity(int,1,1),* into # from tb
select
bianhao = case when px = 1 then bianhao else '' end,
jianshu,
shangfu = case when px = 1 then shangfu else '' end,
xianjin = case when px = 1 then xianjin else '' end,
dshk = case when px = 1 then ltrim(dshk) else '' end,
zyf,
jsfs
from (select px=(select count(1) from # where bianhao=a.bianhao and id<=a.id),* from # a) t
drop table #
--调用
EXEC sp_N
--结果
/*
bianhao jianshu shangfu xianjin dshk zyf jsfs
------- ----------- ----------- ------- ------------ ----------- ----
001 1 20 0 200 10 上付
2 0 10 上付
002 3 0 30 10 15 现金
1 0 15 现金(4 行受影响)*/
我的重复数据是从fhqdbh 表中查询的 以下是我的存储过程 我在asp 中 这么调用str_Sql="exec sp_fhqd " 错了?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--select * from fhqd where fhqdbh='MFAXQD20090414001'
ALTER proc sp_fhqd
AS
select id=identity(int,1,1),* into # from fhqd
select
fhqdbh = case when px = 1 then fhqdbh else '' end,
jianshu,
shangfu = case when px = 1 then shangfu else '' end,
yuejie = case when px =1 then yuejie else '' end,
xianjin = case when px = 1 then xianjin else '' end,
qiankuan = case when px =1 then qiankuan else '' end,
dshk = case when px = 1 then ltrim(dshk) else '' end,
zyf,
jsfs
from (select px=(select count(1) from # where fhqdbh=a.fhqdbh and id<=a.id),* from # a) t
drop table #GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO