insert c select a,b,isnull(c,0) from t1 group by a,b
insert t2 select A,B,sum(C) C from t1 group by A,B
insert c select a,b,isnull(c,0) from t1 group by a,b having count(1)>1还是要这样?
-------------------------------------------------------------------------- -- AUTHOR : HTL258(TONY) -- DATE : 2010-03-31 08:15:41 -- VERSION:MICROSOFT SQL SERVER 2008 (SP1) - 10.0.2531.0 (INTEL X86) -- MAR 29 2009 10:27:29 -- COPYRIGHT (C) 1988-2008 MICROSOFT CORPORATION -- DEVELOPER EDITION ON WINDOWS NT 5.1 <X86> (BUILD 2600: SERVICE PACK 2) -------------------------------------------------------------------------- --> 生成测试数据表:TBIF NOT OBJECT_ID('[TB]') IS NULL DROP TABLE [TB] GO CREATE TABLE [TB]([A] NVARCHAR(10),[B] NVARCHAR(10),[C] INT) INSERT [TB] SELECT 'A1','B1',1 UNION ALL SELECT 'A1','B1',2 UNION ALL SELECT 'A1','B1',3 UNION ALL SELECT 'A2','B2',1 UNION ALL SELECT 'A2','B2',2 UNION ALL --增加一条记录 SELECT 'A3','B3',3 GO --SELECT * FROM [TB]-->SQL查询如下: --创建C表 SELECT * INTO C FROM TB WHERE 1=0--选择1: INSERT C SELECT A,B,SUM(ISNULL(C,0)) FROM TB GROUP BY A,B --结果1: SELECT * FROM C /* A B C ---------- ---------- ----------- A1 B1 6 A2 B2 3 A3 B3 3(3 行受影响) */--清除C表内容; TRUNCATE TABLE C --选择2: INSERT C SELECT A,B,SUM(ISNULL(C,0)) FROM TB GROUP BY A,B HAVING COUNT(1)>1--结果2: SELECT * FROM C /* A B C ---------- ---------- ----------- A1 B1 6 A2 B2 3(2 行受影响) */
--??? insert into t2(A,B,C,D) select A,B,sum(isnull(C,0)),max(D) from t1 group by A,B a1,b1 d取d3, a2,b2 d取d4的条件是什么? 取c取大的那条吗?
--取最大 insert into t2(A,B,C,D) select A,B,sum(isnull(C,0)),max(D) from t1 group by A,B --如果只取有相同的A,B 加上having count(1)>1
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-03-31 08:35:34 -- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([A] NVARCHAR(10),[B] NVARCHAR(10),[C] INT,[d] NVARCHAR(10)) INSERT [tb] SELECT 'a1','b1',1,'aa' UNION ALL SELECT 'a1','b1',2,'c1' UNION ALL SELECT 'a1','b1',3,'d3' UNION ALL SELECT 'a2','b2',1,'56' UNION ALL SELECT 'a2','b2',2,'d4' UNION ALL --增加一条记录 SELECT 'A3','B3',3,'d5' GO --SELECT * FROM [TB]-->SQL查询如下: --创建C表 IF NOT OBJECT_ID('[C]') IS NULL DROP TABLE [C] GO SELECT * INTO C FROM TB WHERE 1=0--插入结果到C表 INSERT C SELECT A,B,SUM(ISNULL(C,0)),MAX(D) FROM TB GROUP BY A,B --结果集: SELECT * FROM C /* A B C d ---------- ---------- ----------- ---------- a1 b1 6 d3 a2 b2 3 d4 A3 B3 3 d5(3 行受影响)*/这样?
insert t2
selct A,B,sum(C) C from t1 group by A,B
insert t2
select A,B,sum(C) C from t1 group by A,B
-- AUTHOR : HTL258(TONY)
-- DATE : 2010-03-31 08:15:41
-- VERSION:MICROSOFT SQL SERVER 2008 (SP1) - 10.0.2531.0 (INTEL X86)
-- MAR 29 2009 10:27:29
-- COPYRIGHT (C) 1988-2008 MICROSOFT CORPORATION
-- DEVELOPER EDITION ON WINDOWS NT 5.1 <X86> (BUILD 2600: SERVICE PACK 2)
--------------------------------------------------------------------------
--> 生成测试数据表:TBIF NOT OBJECT_ID('[TB]') IS NULL
DROP TABLE [TB]
GO
CREATE TABLE [TB]([A] NVARCHAR(10),[B] NVARCHAR(10),[C] INT)
INSERT [TB]
SELECT 'A1','B1',1 UNION ALL
SELECT 'A1','B1',2 UNION ALL
SELECT 'A1','B1',3 UNION ALL
SELECT 'A2','B2',1 UNION ALL
SELECT 'A2','B2',2 UNION ALL
--增加一条记录
SELECT 'A3','B3',3
GO
--SELECT * FROM [TB]-->SQL查询如下:
--创建C表
SELECT * INTO C FROM TB WHERE 1=0--选择1:
INSERT C SELECT A,B,SUM(ISNULL(C,0)) FROM TB GROUP BY A,B
--结果1:
SELECT * FROM C
/*
A B C
---------- ---------- -----------
A1 B1 6
A2 B2 3
A3 B3 3(3 行受影响)
*/--清除C表内容;
TRUNCATE TABLE C
--选择2:
INSERT C SELECT A,B,SUM(ISNULL(C,0)) FROM TB GROUP BY A,B HAVING COUNT(1)>1--结果2:
SELECT * FROM C
/*
A B C
---------- ---------- -----------
A1 B1 6
A2 B2 3(2 行受影响)
*/
表t1、t2相同当t1中,A、B两个字段相同时,将字段C求和后插入到t2中在t1中:A B C da1 b1 1 aa
a1 b1 2 c1
a1 b1 3 d3a2 b2 1 56
a2 b2 2 d4
需要在t2中得到
a1 b1 6 d3
a2 b2 3 d4
--???
insert into t2(A,B,C,D)
select A,B,sum(isnull(C,0)),max(D) from t1 group by A,B
a1,b1 d取d3,
a2,b2 d取d4的条件是什么? 取c取大的那条吗?
insert into t2(A,B,C,D)
select A,B,sum(isnull(C,0)),max(D) from t1 group by A,B --如果只取有相同的A,B 加上having count(1)>1
-- Author : htl258(Tony)
-- Date : 2010-03-31 08:35:34
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([A] NVARCHAR(10),[B] NVARCHAR(10),[C] INT,[d] NVARCHAR(10))
INSERT [tb]
SELECT 'a1','b1',1,'aa' UNION ALL
SELECT 'a1','b1',2,'c1' UNION ALL
SELECT 'a1','b1',3,'d3' UNION ALL
SELECT 'a2','b2',1,'56' UNION ALL
SELECT 'a2','b2',2,'d4' UNION ALL
--增加一条记录
SELECT 'A3','B3',3,'d5'
GO
--SELECT * FROM [TB]-->SQL查询如下:
--创建C表
IF NOT OBJECT_ID('[C]') IS NULL
DROP TABLE [C]
GO
SELECT * INTO C FROM TB WHERE 1=0--插入结果到C表
INSERT C SELECT A,B,SUM(ISNULL(C,0)),MAX(D) FROM TB GROUP BY A,B
--结果集:
SELECT * FROM C
/*
A B C d
---------- ---------- ----------- ----------
a1 b1 6 d3
a2 b2 3 d4
A3 B3 3 d5(3 行受影响)*/这样?