SELECT MIN(ID),ISNULL(NAME,'其它')NAME,SUM(数量) FROM TB GROUP BY ISNULL(NAME,'其它')
SELECT MAX(ID),ISNULL(NAME,'其它')NAME,SUM(数量) FROM TB GROUP BY ISNULL(NAME,'其它')
select max(id) id,isnull(名称,'其他') 名称,sum(数量) 数量 from tb group by isnull(名称,'其他')
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-06 11:23:08 -- 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]([id] INT,[名称] NVARCHAR(10),[数量] INT) INSERT [tb] SELECT 1,N'苹果',23 UNION ALL SELECT 2,N'西瓜',30 UNION ALL SELECT 3,NULL,120 UNION ALL SELECT 4,N'香蕉',16 UNION ALL SELECT 5,N'其他',9 GO --SELECT * FROM [tb]-->SQL查询如下: select max(id) id,isnull(名称,'其他') 名称,sum(数量) 数量 from tb group by isnull(名称,'其他') order by 1 /* id 名称 数量 ----------- ---------- ----------- 1 苹果 23 2 西瓜 30 4 香蕉 16 5 其他 129(4 行受影响) */
SELECT max(ID),NAME,SUM(数量) FROM (select id,ISNULL(NAME,'其它')NAME, 数量 from TB ) a GROUP BY name
CREATE TABLE #TT( ID INT,NAME VARCHAR(20),NUM INT) INSERT #TT SELECT 1 ,'苹果', 23 INSERT #TT SELECT 2 ,'西瓜', 30 INSERT #TT SELECT 3 ,Null, 120 INSERT #TT SELECT 4 ,'香蕉', 16 INSERT #TT SELECT 5 ,'其他', 9 SELECT ID,NAME,CASE WHEN NAME='其他' THEN NUM+(SELECT SUM(NUM) FROM #TT WHERE NAME IS NULL) ELSE NUM END AS NUM FROM #TT WHERE NAME IS NOT NULL ID NAME NUM ----------- -------------------- ----------- 1 苹果 23 2 西瓜 30 4 香蕉 16 5 其他 129(4 行受影响)
--------------------------------------------- --> Author : js_szy --> Target : ★★★ --> Date : 2010-04-06 11:25:03 --> Version: SQL Server 2005 ---------------------------------------------
--> 测试数据: [tq] if object_id('[tq]') is not null drop table [tq] go create table [tq] (id int,xm varchar(4),nn int) insert into [tq] select 1,'苹果',23 union all select 2,'西瓜',30 union all select 3,null,120 union all select 4,'香蕉',16 union all select 5,'其他',9 select id=max(id),xm=isnull(xm,'其他'),nn=sum(nn) from [tq] group by isnull(xm,'其他') order by id id xm nn ----------- ---- ----------- 1 苹果 23 2 西瓜 30 4 香蕉 16 5 其他 129(4 行受影响)
都好强悍 我还想着用case呢
IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[名称] NVARCHAR(10),[数量] INT) INSERT [tb] SELECT 1,N'苹果',23 UNION ALL SELECT 2,N'西瓜',30 UNION ALL SELECT 3,NULL,120 UNION ALL SELECT 4,N'香蕉',16 UNION ALL SELECT 5,N'其他',9 GOselect [名称],sum([数量]) [数量] from ( select isnull([名称],'其他') [名称],[数量] from [tb] ) tt group by [名称]名称 数量 ---------- ----------- 苹果 23 其他 129 西瓜 30 香蕉 16(4 行受影响)
from tb
group by isnull(名称,'其他')
-- Author : htl258(Tony)
-- Date : 2010-04-06 11:23:08
-- 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]([id] INT,[名称] NVARCHAR(10),[数量] INT)
INSERT [tb]
SELECT 1,N'苹果',23 UNION ALL
SELECT 2,N'西瓜',30 UNION ALL
SELECT 3,NULL,120 UNION ALL
SELECT 4,N'香蕉',16 UNION ALL
SELECT 5,N'其他',9
GO
--SELECT * FROM [tb]-->SQL查询如下:
select max(id) id,isnull(名称,'其他') 名称,sum(数量) 数量
from tb
group by isnull(名称,'其他')
order by 1
/*
id 名称 数量
----------- ---------- -----------
1 苹果 23
2 西瓜 30
4 香蕉 16
5 其他 129(4 行受影响)
*/
FROM (select id,ISNULL(NAME,'其它')NAME, 数量 from TB ) a
GROUP BY name
INSERT #TT SELECT 1 ,'苹果', 23
INSERT #TT SELECT 2 ,'西瓜', 30
INSERT #TT SELECT 3 ,Null, 120
INSERT #TT SELECT 4 ,'香蕉', 16
INSERT #TT SELECT 5 ,'其他', 9
SELECT ID,NAME,CASE WHEN NAME='其他' THEN NUM+(SELECT SUM(NUM) FROM #TT WHERE NAME IS NULL) ELSE NUM END AS NUM
FROM #TT
WHERE NAME IS NOT NULL
ID NAME NUM
----------- -------------------- -----------
1 苹果 23
2 西瓜 30
4 香蕉 16
5 其他 129(4 行受影响)
--> Author : js_szy
--> Target : ★★★
--> Date : 2010-04-06 11:25:03
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: [tq]
if object_id('[tq]') is not null drop table [tq]
go
create table [tq] (id int,xm varchar(4),nn int)
insert into [tq]
select 1,'苹果',23 union all
select 2,'西瓜',30 union all
select 3,null,120 union all
select 4,'香蕉',16 union all
select 5,'其他',9
select id=max(id),xm=isnull(xm,'其他'),nn=sum(nn) from [tq]
group by isnull(xm,'其他')
order by id id xm nn
----------- ---- -----------
1 苹果 23
2 西瓜 30
4 香蕉 16
5 其他 129(4 行受影响)
我还想着用case呢
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[名称] NVARCHAR(10),[数量] INT)
INSERT [tb]
SELECT 1,N'苹果',23 UNION ALL
SELECT 2,N'西瓜',30 UNION ALL
SELECT 3,NULL,120 UNION ALL
SELECT 4,N'香蕉',16 UNION ALL
SELECT 5,N'其他',9
GOselect [名称],sum([数量]) [数量]
from
(
select isnull([名称],'其他') [名称],[数量] from [tb]
) tt
group by [名称]名称 数量
---------- -----------
苹果 23
其他 129
西瓜 30
香蕉 16(4 行受影响)