[data]
id A1 A2 A3 clsId
1 1 0 0 1
2 1 1 1 3
3 0 0 1 1
4 1 0 0 1
5 0 1 0 2
6 1 0 0 1
7 1 1 1 1
8 0 0 1 2
9 1 0 0 1
10 1 0 0 1
11 1 1 1 3
12 0 0 1 1
13 1 0 0 1
14 0 1 0 2
15 1 0 0 1
16 1 1 1 1
17 0 0 1 2
18 1 0 0 1想查出前几条(如5条)以 clsId=1 数据中A1,A2,A3 的各自和,且要是id较小的那几条(如查全部的和为: select sum(A1),sum(A2),sum(A3) from [data] where clsId=1)
id A1 A2 A3 clsId
1 1 0 0 1
2 1 1 1 3
3 0 0 1 1
4 1 0 0 1
5 0 1 0 2
6 1 0 0 1
7 1 1 1 1
8 0 0 1 2
9 1 0 0 1
10 1 0 0 1
11 1 1 1 3
12 0 0 1 1
13 1 0 0 1
14 0 1 0 2
15 1 0 0 1
16 1 1 1 1
17 0 0 1 2
18 1 0 0 1想查出前几条(如5条)以 clsId=1 数据中A1,A2,A3 的各自和,且要是id较小的那几条(如查全部的和为: select sum(A1),sum(A2),sum(A3) from [data] where clsId=1)
解决方案 »
- 帮我看这个sql怎么写?
- 怎样实现字段的随机查询?
- 求一条取消查询的命令!!!!!!
- sql在表中插入记录的问题
- sqlserver2005 XP SP3环境中安装问题
- 数据库连接正确,读取表有问题
- 请高手帮忙修复SQLServer数据库
- avg函数的正确用法,问题虽小,分可不少
- 关于在MS SQL SERVER删除表中的一列可否加条件?
- JDK1.3,jdbc-odbc访问Sql7.0,修改某字段(为字符串,该字段定义长度为50字节),UPDATE之后,用SELECT一看,发现该字段值后全部是空格,也就是说空格没有被TRIM掉,怎么在UPDATE里解决?
- DBLINK后读取数据
- 求一条sql(请高手帮下解决下)
select sum(A1),sum(A2),sum(A3)
from
[data]
where
id in(select top 5 id from [data] where clsId=1 order by id asc)
from (select top 5 * from [data] where clsId=1 order by id) t
from (select top 10 percent * from [data] where clsId=1 order by id) t
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-23 12:43:47
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[A1] int,[A2] int,[A3] int,[clsId] int)
insert [tb]
select 1,1,0,0,1 union all
select 2,1,1,1,3 union all
select 3,0,0,1,1 union all
select 4,1,0,0,1 union all
select 5,0,1,0,2 union all
select 6,1,0,0,1 union all
select 7,1,1,1,1 union all
select 8,0,0,1,2 union all
select 9,1,0,0,1 union all
select 10,1,0,0,1 union all
select 11,1,1,1,3 union all
select 12,0,0,1,1 union all
select 13,1,0,0,1 union all
select 14,0,1,0,2 union all
select 15,1,0,0,1 union all
select 16,1,1,1,1 union all
select 17,0,0,1,2 union all
select 18,1,0,0,1
--------------开始查询--------------------------
select sum(a1)a1,sum(a2)a2,sum(a3)a3 from (SELECT * FROM TB AS T WHERE ID IN(SELECT TOP 5 ID FROM TB ORDER BY ID ))t
----------------结果----------------------------
/* a1 a2 a3
----------- ----------- -----------
3 2 2(1 行受影响)*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-23 12:44:42
-- Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)---------------------------------*/
--> 生成测试数据表:dataIF NOT OBJECT_ID('[data]') IS NULL
DROP TABLE [data]
GO
CREATE TABLE [data]([id] INT,[A1] INT,[A2] INT,[A3] INT,[clsId] INT)
INSERT [data]
SELECT 1,1,0,0,1 UNION ALL
SELECT 2,1,1,1,3 UNION ALL
SELECT 3,0,0,1,1 UNION ALL
SELECT 4,1,0,0,1 UNION ALL
SELECT 5,0,1,0,2 UNION ALL
SELECT 6,1,0,0,1 UNION ALL
SELECT 7,1,1,1,1 UNION ALL
SELECT 8,0,0,1,2 UNION ALL
SELECT 9,1,0,0,1 UNION ALL
SELECT 10,1,0,0,1 UNION ALL
SELECT 11,1,1,1,3 UNION ALL
SELECT 12,0,0,1,1 UNION ALL
SELECT 13,1,0,0,1 UNION ALL
SELECT 14,0,1,0,2 UNION ALL
SELECT 15,1,0,0,1 UNION ALL
SELECT 16,1,1,1,1 UNION ALL
SELECT 17,0,0,1,2 UNION ALL
SELECT 18,1,0,0,1
GO
--SELECT * FROM [data]-->SQL查询如下:
select sum(A1),sum(A2),sum(A3)
from [data]
where id in(
select top 10 percent id
from [data]
where clsId=1
order by id desc)
/*
----------- ----------- -----------
2 1 1(所影响的行数为 1 行)
*/
--> 测试数据: @T1
declare @T1 table (id int,A1 int,A2 int,A3 int,clsId int)
insert into @T1
select 1,1,0,0,1 union all
select 2,1,1,1,3 union all
select 3,0,0,1,1 union all
select 4,1,0,0,1 union all
select 5,0,1,0,2 union all
select 6,1,0,0,1 union all
select 7,1,1,1,1 union all
select 8,0,0,1,2 union all
select 9,1,0,0,1 union all
select 10,1,0,0,1 union all
select 11,1,1,1,3 union all
select 12,0,0,1,1 union all
select 13,1,0,0,1 union all
select 14,0,1,0,2 union all
select 15,1,0,0,1 union all
select 16,1,1,1,1 union all
select 17,0,0,1,2 union all
select 18,1,0,0,1select
id
,A1 = sum(A1)
,A2 = sum(A2)
,A3 = sum(A3)
,clsid = max(clsid)
from @T1 a
where (select count(1) from @T1 where a.id >= id and clsid = a.clsid) <= 5
and clsid = 1
group by id with rollupid A1 A2 A3 clsid
----------- ----------- ----------- ----------- -----------
1 1 0 0 1
3 0 0 1 1
4 1 0 0 1
6 1 0 0 1
7 1 1 1 1
NULL 4 1 2 1(所影响的行数为 6 行)