id name money age
1 a1 1 5
2 a2 5 5
3 b 3 3
4 c 2 2想要得到结果:
name money age
a 5(注3+2) 10(注5+5)
b 3 3
c 2 2
1 a1 1 5
2 a2 5 5
3 b 3 3
4 c 2 2想要得到结果:
name money age
a 5(注3+2) 10(注5+5)
b 3 3
c 2 2
解决方案 »
- TSQL如何格式化输出时间
- 有个问题不知道难不难解决。忘高手赐教。。。
- 怎样两次合计
- 求SQL,高手请进(如何把横行排列,变成纵向排列)
- timesStamp在SSIS中作为变量的疑问
- 快速查询表T1,字段B=0、字段A和值<=5000 按字段ID顺序排列最前面的记录, 并得到这些记录的最大ID和最小ID???
- 谁会写这个SQL语句,帮我啊!
- 在SQL里面怎样才能让一个数字四舍五入成两位小数?
- 求一查询语句,统计每个人的申请数目, 立项数目, 结项数目 ,请高手请指教!!!
- 紧急求助一条统计连续出勤人次的SQL语句,查询要求如下........
- sql2005求一个Sql语句
- 简单的查询问题,我菜不会,求教!
from tb
group by left(name,1)
insert into tb values(1 ,'a1' ,1 ,5)
insert into tb values(2 ,'a2' ,5 ,5)
insert into tb values(3 ,'b' ,3 ,3)
insert into tb values(4 ,'c' ,2 ,2)
goselect left(name,1) name , sum(money) money,sum(age) age
from tb
group by left(name,1)drop table tb/*
name money age
---- ----------- -----------
a 6 10
b 3 3
c 2 2(所影响的行数为 3 行)
*/
create table #
(
id int,
name varchar(10),
money int,
age int
)
insert into # select 1, 'a1', 1, 5
insert into # select 2, 'a2', 5, 5
insert into # select 3, 'b', 3, 3
insert into # select 4, 'c', 2, 2
select left(name,1) as name,sum(money) as money,sum(age) as age
from #
group by left(name,1)/*
name money age
---- ----------- -----------
a 6 10
b 3 3
c 2 2(3 行受影响)
-- 3+2 =5 不知道什么意思,不知道是不是我理解错误
*/
含a a不一定在第一位。含a的money是其他不含a的money和。 age是含a的age和
declare @table table (id int,name varchar(2),money int,age int)
insert into @table
select 1,'a1',1,5 union all
select 2,'a2',5,5 union all
select 3,'b',3,3 union all
select 4,'c',2,2select
left(name,1) as a,sum([money]) as b,sum(age) as c
from @table group by left(name,1)
/*
a b c
---- ----------- -----------
a 6 10
b 3 3
c 2 2
*/
--第一行算不出来5
from #tb
group by substring(name,1,patindex('%[0-9]%',name)-1)
/*
name money age
---- ----------- -----------
a 6 10
b 3 3
c 2 2(3 行受影响)
**/
insert into @a
select 1,'a1',1,5 union all
select 2,'a2',5,5 union all
select 3,'b',3,3 union all
select 4,'c',2,2SELECT NAME ,
CASE WHEN NAME='a' THEN (SELECT sum(CASE WHEN CHARINDEX('a',NAME )>0 THEN 0 ELSE MONEY END) FROM @a) ELSE SUM(MONEY) END MONEY ,
SUM(age) age
FROM
(
SELECT CASE WHEN CHARINDEX('a',NAME)>0 THEN 'a' ELSE NAME END name ,
CASE WHEN CHARINDEX('a',NAME)>0 THEN -1 ELSE MONEY END MONEY ,
age
FROM @a a
)aa
GROUP BY NAME --result
/*NAME MONEY age
---- ----------- -----------
a 5 10
b 3 3
c 2 2(所影响的行数为 3 行)*/
顶15楼的 declare @table table (id int,name varchar(2),money int,age int)
insert into @table
select 1,'a1',1,5 union all
select 2,'a2',5,5 union all
select 3,'b',3,3 union all
select 4,'c',2,2select distinct
case when CHARINDEX('a',NAME)>0 then 'a' else [name] end as name,
case when CHARINDEX('a',NAME)>0 then
(select sum(money) from @table where CHARINDEX('a',NAME)<=0) else money end as money,
case when CHARINDEX('a',NAME)>0 then
(select sum(age) from @table where CHARINDEX('a',NAME)>0) else age end as age
from @table--result
/*NAME MONEY age
---- ----------- -----------
a 5 10
b 3 3
c 2 2(所影响的行数为 3 行)*/
insert into tb values(1 ,'a1' ,1 ,5)
insert into tb values(2 ,'a2' ,5 ,5)
insert into tb values(3 ,'b' ,3 ,3)
insert into tb values(4 ,'c' ,2 ,2)
go
select top 1 'a' as [name],
(select sum([money]) from tb where id not in(select id from tb where charindex('a',[name])>0)) as [money],
(select sum(age) from tb where id in(select id from tb where charindex('a',[name])>0)) as age
from tb
union all
select [name],sum([money]),sum(age) from tb where charindex('a',[name])=0 group by [name]
go
drop table tb
/*
name money age
---------- ----------- -----------
a 5 10
b 3 3
c 2 2(3 行受影响)
*/
insert into tb values(1 ,'a1' ,1 ,5)
insert into tb values(2 ,'a2' ,5 ,5)
insert into tb values(3 ,'b' ,3 ,3)
insert into tb values(4 ,'c' ,2 ,2)
go
select 'a' as [name],
(select sum([money]) from tb where id not in(select id from tb where charindex('a',[name])>0)) as [money],
(select sum(age) from tb where id in(select id from tb where charindex('a',[name])>0)) as age
union all
select [name],sum([money]),sum(age) from tb where charindex('a',[name])=0 group by [name]
go
drop table tb
/*
name money age
---------- ----------- -----------
a 5 10
b 3 3
c 2 2(3 行受影响)
*/