比如有这样的表段
id name pay
1 huang 2000
2 liu 5000
...........
我想输出的结果是
查询出来的样子是:
1 huang 2000
2 liu 5000
无 无 7000第二种情况是
1 huang 2000
2 liu 5000
3 huang 3000 ...........(两个huang)我想输出的结果是:
1 huang 5000
2 liu 5000
无 无 10000谢谢了
id name pay
1 huang 2000
2 liu 5000
...........
我想输出的结果是
查询出来的样子是:
1 huang 2000
2 liu 5000
无 无 7000第二种情况是
1 huang 2000
2 liu 5000
3 huang 3000 ...........(两个huang)我想输出的结果是:
1 huang 5000
2 liu 5000
无 无 10000谢谢了
另外你这种情况下想select出id是不合理的,同一个name可能对应着多个id,如果你想要一个序号就另有方法,你先说说你的数据库环境。USE [tempdb]
GOif exists (select 1
from sysobjects
where id = object_id('CSDN1111')
and type = 'U')
drop table CSDN1111
goCREATE TABLE [dbo].[CSDN1111](
[id] [int],
[name] [nvarchar](50) ,
[pay] [int]
)
GO
insert into [CSDN1111]
select 1,'huang',2000 union all
select 2,'liu',5000 union all
select 3,'huang',3000
Go-- 以上是插入示例数据select [name],SUM(pay) from [CSDN1111] group by [name]
union all
select '',SUM(pay) from [CSDN1111]
Godrop table CSDN1111
Go
比如有这样的表段
id name pay
1 huang 2000
2 liu 5000
...........
我想输出的结果是
查询出来的样子是:
1 huang 2000
2 liu 5000
无 无 7000第二种情况是
1 huang 2000
2 liu 5000
3 huang 3000
(两个huang)
我想输出的结果是:
1 huang 5000
2 liu 5000
无 无 10000
*/
--情况一:
go
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl(
id varchar(5),
name varchar(10),
pay int
)
go
insert tbl
select '1','huang',2000 union all
select '2','liu',5000select * from tbl
union all
select '合','计',SUM(pay) from tbl/*
结果:
id name pay
1 huang 2000
2 liu 5000
合 计 7000
*/
--情况二go
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl(
id varchar(5),
name varchar(10),
pay int
)
go
insert tbl
select '1','huang',2000 union all
select '2','liu',5000 union all
select '1','huang',3000
select id,name,SUM(pay) from tbl
group by name,id
union all
select '合','计',SUM(pay) from tbl/*
结果:(我把相同name的id改成一样了)
id name (无列名)
1 huang 5000
2 liu 5000
合 计 10000*/
go
create table [tb]([id] int,[name] varchar(5),[pay] int)
insert [tb]
select 1,'huang',2000 union all
select 2,'liu',5000 union all
select 3,'huang',3000
goselect min(ltrim(id)) as id,name,sum(pay) as pay from tb group by name
union all
select '无','无',sum(pay) from tb/**
id name pay
------------ ----- -----------
1 huang 5000
2 liu 5000
无 无 10000(3 行受影响)
**/select isnull(name,'无') as name,sum(pay) as pay from tb group by name with rollup
/**
name pay
----- -----------
huang 5000
liu 5000
无 10000(3 行受影响)
**/
union
select '无','无',sum(pay) from tb
use demo
go
create table sc(sno varchar(9),/*所以还是在这把sno改成varchar(9)就可以定义下面的外键约束了*/
cno varchar(20),
grade smallint,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno));/*建立学生选课表*/--插入数据insert into sc values('2006001','1',86);
insert into sc values('2006001','2',90);
insert into sc values('2006002','3',89);
insert into sc values('2006003','4',90);
insert into sc values('2006003','5',97);
insert into sc values('2006004','2',78);
insert into sc values('2006005','1',78);
insert into sc values('2006006','6',67);
insert into sc values('2006005','3',50);
insert into sc values('2006006','4',70);
insert into sc values('2006007','5',97);
insert into sc values('2006007','2',78);
--开始查询
use demo
go
;with c as
(
select sno,cno,grade,NTILE(1) over(order by grade desc) as gp from sc
)
select '','',SUM(grade) from c group by gp
--这种方法看上去有点走了弯路. 不知道有没有更简单的
--测试数据
declare @T table (id int,name varchar(5),pay int)
insert into @T
select 1,'huang',2000 union all
select 2,'liu',5000 union all
select 3,'huang',3000--查询
select isnull(name,'无') as name,sum(pay) as pay
from @T group by name with Rollup
--结果
/*
name pay
----- -----------
huang 5000
liu 5000
无 10000
*/