ID NAME KIND1 KIND2 PRICE
1 A K1A K2A 100
2 A K1B K2B -100
3 A K1C K2C 10
4 B K1A K2A 200
要求合并结果为:
3 A K1C K2C 10 //(前面三条记录的和)
4 B K1A K2A 200
1 A K1A K2A 100
2 A K1B K2B -100
3 A K1C K2C 10
4 B K1A K2A 200
要求合并结果为:
3 A K1C K2C 10 //(前面三条记录的和)
4 B K1A K2A 200
ID,name ,KIND1,KIND2,
PRICE=(select SUM(price) from tb where K.NAME=NAME)
FROM TB K
WHERE NOT EXISTS(SELECT * FROM TB WHERE K.NAME=NAME AND ID>k.id)
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-18 14:41:04
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[NAME] NVARCHAR(10),[KIND1] NVARCHAR(10),[KIND2] NVARCHAR(10),[PRICE] INT)
INSERT [tb]
SELECT 1,'A','K1A','K2A',100 UNION ALL
SELECT 2,'A','K1B','K2B',-100 UNION ALL
SELECT 3,'A','K1C','K2C',10 UNION ALL
SELECT 4,'B','K1A','K2A',200
GO
--SELECT * FROM [tb]-->SQL查询如下:
SELECT ID,Name,KInD1,KIND2,PRICE=(SELECT SUM(Price) FROM tb WHERE NAME=T.NAME)
FROM tb T
WHERE ID=(SELECT MAX(ID) FROM tb WHERE NAME=T.NAME)
ORDER BY ID
/*
ID Name KInD1 KIND2 PRICE
----------- ---------- ---------- ---------- -----------
3 A K1C K2C 10
4 B K1A K2A 200(2 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[NAME] varchar(1),[KIND1] varchar(3),[KIND2] varchar(3),[PRICE] int)
insert [tb]
select 1,'A','K1A','K2A',100 union all
select 2,'A','K1B','K2B',-100 union all
select 3,'A','K1C','K2C',10 union all
select 4,'B','K1A','K2A',200
---查询---
select a.id,a.name,a.kind1,a.kind2,b.price
from tb a
join (select name,sum(price)price from tb group by name)b on a.name=b.name
where not exists(select 1 from tb where name=a.name and id>a.id)---结果---
id name kind1 kind2 price
----------- ---- ----- ----- -----------
3 A K1C K2C 10
4 B K1A K2A 200(2 行受影响)
INSERT [tb]
SELECT 1,'A','K1A','K2A',100 UNION ALL
SELECT 2,'A','K1B','K2B',-100 UNION ALL
SELECT 3,'A','K1C','K2C',10 UNION ALL
SELECT 4,'B','K1A','K2A',200
select max(id) id , name , max(KIND1) KIND1 , max(KIND2) KIND2 , sum(PRICE) PRICE from tb t group by name
drop table tb /*
id name KIND1 KIND2 PRICE
----------- ---------- ---------- ---------- -----------
3 A K1C K2C 10
4 B K1A K2A 200(所影响的行数为 2 行)
*/
INSERT [tb]
SELECT 1,'A','K1A','K2A',100 UNION ALL
SELECT 2,'A','K1B','K2B',-100 UNION ALL
SELECT 3,'A','K1C','K2C',10 UNION ALL
SELECT 4,'B','K1A','K2A',200--方法一
select max(id) id , name , max(KIND1) KIND1 , max(KIND2) KIND2 , sum(PRICE) PRICE from tb t group by name--方法二
select m.id , m.name , m.KIND1 , m.KIND2 , (select sum(price) from tb where name = m.name) price from tb m where id = (select max(id) from tb where name = m.name) order by m.name--方法三
select m.id , m.name , m.KIND1 , m.KIND2 , (select sum(price) from tb where name = m.name) price from tb m where not exists (select 1 from tb where name = m.name and id > m.id) order by m.namedrop table tb /*
id name KIND1 KIND2 PRICE
----------- ---------- ---------- ---------- -----------
3 A K1C K2C 10
4 B K1A K2A 200(所影响的行数为 2 行)
*/