表名dg
invoice wsjh
aa 50
aa 70
bb 80
bb 65
bb 10
cc 20
cc 90
cc 30
實現的結果為:
invoice wsjh xj
aa 50
aa 70 120
bb 80
bb 65
bb 10 155
cc 20
cc 90
cc 30 140
這樣子可以實現嗎?麻煩您們了.謝謝...
invoice wsjh
aa 50
aa 70
bb 80
bb 65
bb 10
cc 20
cc 90
cc 30
實現的結果為:
invoice wsjh xj
aa 50
aa 70 120
bb 80
bb 65
bb 10 155
cc 20
cc 90
cc 30 140
這樣子可以實現嗎?麻煩您們了.謝謝...
GO
CREATE TABLE DG(
INVOICE VARCHAR(50)
,WSJH INT
)
INSERT INTO DG
SELECT 'aa', 50 UNION ALL
SELECT 'aa', 70 UNION ALL
SELECT 'bb', 80 UNION ALL
SELECT 'bb', 65 UNION ALL
SELECT 'bb', 10 UNION ALL
SELECT 'cc', 20 UNION ALL
SELECT 'cc', 90 UNION ALL
SELECT 'cc', 30
GO
--2000用临时表
IF OBJECT_ID('TEMPDB..#') IS NOT NULL DROP TABLE #
GO
SELECT IDENTITY(INT,1,1) 'ID',INVOICE,WSJH INTO # FROM DG
SELECT T1.INVOICE,T1.WSJH
,ISNULL(CAST(T2.TOTAL AS VARCHAR(50)),'')
FROM # T1
LEFT JOIN (
SELECT INVOICE,SUM(WSJH) 'TOTAL' FROM DG GROUP BY INVOICE
) T2 ON T1.INVOICE=T2.INVOICE AND NOT EXISTS(
SELECT 1 FROM # T3 WHERE T3.INVOICE=T1.INVOICE AND T3.ID>T1.ID
)
/*
aa 50
aa 70 120
bb 80
bb 65
bb 10 155
cc 20
cc 90
cc 30 140
*/
GO
CREATE TABLE DG(
INVOICE VARCHAR(50)
,WSJH INT
)
INSERT INTO DG
SELECT 'aa', 50 UNION ALL
SELECT 'aa', 70 UNION ALL
SELECT 'bb', 80 UNION ALL
SELECT 'bb', 65 UNION ALL
SELECT 'bb', 10 UNION ALL
SELECT 'cc', 20 UNION ALL
SELECT 'cc', 90 UNION ALL
SELECT 'cc', 30
GO--2000用临时表
IF OBJECT_ID('TEMPDB..#') IS NOT NULL DROP TABLE #
GO
SELECT IDENTITY(INT,1,1) 'ID',INVOICE,WSJH INTO # FROM DG
SELECT T1.INVOICE,T1.WSJH
,ISNULL(CAST(T2.TOTAL AS VARCHAR(50)),'')
FROM # T1
LEFT JOIN (
SELECT INVOICE,SUM(WSJH) 'TOTAL' FROM DG GROUP BY INVOICE
) T2 ON T1.INVOICE=T2.INVOICE AND NOT EXISTS(
SELECT 1 FROM # T3 WHERE T3.INVOICE=T1.INVOICE AND T3.ID>T1.ID
)--2005用ROW_NUMBER()
SELECT T1.INVOICE,T1.WSJH
,ISNULL(CAST(T2.TOTAL AS VARCHAR(50)),'')
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY INVOICE) 'ID',* FROM DG
) T1
LEFT JOIN (
SELECT INVOICE,SUM(WSJH) 'TOTAL' FROM DG GROUP BY INVOICE
) T2 ON T1.INVOICE=T2.INVOICE AND NOT EXISTS(
SELECT 1 FROM (
SELECT ROW_NUMBER() OVER (ORDER BY INVOICE) 'ID',* FROM DG
) T3 WHERE T3.INVOICE=T1.INVOICE AND T3.ID>T1.ID
)
/*
aa 50
aa 70 120
bb 80
bb 65
bb 10 155
cc 20
cc 90
cc 30 140
*/
--> 测试数据: #t
if object_id('tempdb.dbo.#t') is not null drop table #t
go
create table #t (invoice varchar(2),wsjh int)
insert into #t
select 'aa',50 union all
select 'aa',70 union all
select 'bb',80 union all
select 'bb',65 union all
select 'bb',10 union all
select 'cc',20 union all
select 'cc',90 union all
select 'cc',30declare @t table(id int identity(1,1),invoice varchar(2),wsjh int ,jx int)insert into @t(invoice,wsjh)
select * from #tdeclare @jx int
set @jx = 0
declare @invoice varchar(2)
set @invoice = ''update a set
@jx = case when @invoice = invoice then @jx+wsjh else wsjh end
,@invoice = invoice
,jx= @jx
from @t aselect invoice,wsjh,jx=
(case when (select max(jx) from @t where a.invoice = invoice) = jx then rtrim(jx) else ''end)
from @t ainvoice wsjh jx
------- ----------- ------------
aa 50
aa 70 120
bb 80
bb 65
bb 10 155
cc 20
cc 90
cc 30 140(所影响的行数为 8 行)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-01-15 20:50:45
-- 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]([invoice] varchar(2),[wsjh] int)
insert [tb]
select 'aa',50 union all
select 'aa',70 union all
select 'bb',80 union all
select 'bb',65 union all
select 'bb',10 union all
select 'cc',20 union all
select 'cc',90 union all
select 'cc',30
--------------开始查询--------------------------
select
a.*,b.num as xj
from
tb a,
(select invoice,sum(wsjh) as num from tb group by invoice with rollup)b
where
a.invoice=b.invoice
----------------结果----------------------------
/* invoice wsjh xj
------- ----------- -----------
aa 50 120
aa 70 120
bb 80 155
bb 65 155
bb 10 155
cc 20 140
cc 90 140
cc 30 140(8 行受影响)
*/
go
create table [tb]([invoice] varchar(2),[wsjh] int)
insert [tb]
select 'aa',50 union all
select 'aa',70 union all
select 'bb',80 union all
select 'bb',65 union all
select 'bb',10 union all
select 'cc',20 union all
select 'cc',90 union all
select 'cc',30
--DROP TABLE #TB
SELECT IDD=IDENTITY(INT,1,1),* INTO #TB FROM TBSELECT
*,
CASE WHEN NOT EXISTS(SELECT 1 FROM #TB WHERE invoice=T.invoice AND IDD>T.IDD)
THEN
(SELECT LTRIM(SUM(wsjh)) FROM #TB WHERE invoice=T.invoice) ELSE '' END AS NEWinvoice
FROM #TB TIDD invoice wsjh NEWinvoice
----------- ------- ----------- ------------
1 aa 50
2 aa 70 120
3 bb 80
4 bb 65
5 bb 10 155
6 cc 20
7 cc 90
8 cc 30 140(所影响的行数为 8 行)
----sinpoal
1> IF OBJECT_ID('dg') IS NOT NULL DROP TABLE DG
2> GO
1> CREATE TABLE dg(
2> invoice VARCHAR(50),
3> wsjh INT
4> )
5> INSERT INTO dg
6> SELECT 'aa', 50 UNION ALL
7> SELECT 'aa', 70 UNION ALL
8> SELECT 'bb', 80 UNION ALL
9> SELECT 'bb', 65 UNION ALL
10> SELECT 'bb', 10 UNION ALL
11> SELECT 'cc', 20 UNION ALL
12> SELECT 'cc', 90 UNION ALL
13> SELECT 'cc', 30
14> GO(8 行受影响)
1>
2> select *,sum(wsjh)as [sum] from dg group by invoice,wsjh with rollup
3> go
invoice wsjh sum
-------------------------------------------------- ----------- -----------
aa 50 50
aa 70 70
aa NULL 120
bb 10 10
bb 65 65
bb 80 80
bb NULL 155
cc 20 20
cc 30 30
cc 90 90
cc NULL 140
NULL NULL 415(12 行受影响)
1>
楼上已经很多了,凑个热闹:
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
CREATE TABLE test(
INVOICE VARCHAR(50)
,WSJH INT
)
INSERT INTO test
SELECT 'aa', 50 UNION ALL
SELECT 'aa', 70 UNION ALL
SELECT 'bb', 80 UNION ALL
SELECT 'bb', 65 UNION ALL
SELECT 'bb', 10 UNION ALL
SELECT 'cc', 20 UNION ALL
SELECT 'cc', 90 UNION ALL
SELECT 'cc', 30
GO
select * from testalter table test add id int identity(1,1)select invoice,wsjh,case when not exists(select 1 from test where invoice=t1.invoice and id>t1.id) then
(select sum(wsjh) from test where invoice=t1.invoice) else '' end as new
from test t1
--------------------
aa 50 0
aa 70 120
bb 80 0
bb 65 0
bb 10 155
cc 20 0
cc 90 0
cc 30 140