value1 value2 ID
100
450 175 5
175
--------------------------
220
160
800 170 6
150
100
-------------------------- …… …… ……value1那列的值等于对应的value2那列的和
我想问怎么通过一条查询语句得出value1那列的值PS:很着急,解决马上就结了
100
450 175 5
175
--------------------------
220
160
800 170 6
150
100
-------------------------- …… …… ……value1那列的值等于对应的value2那列的和
我想问怎么通过一条查询语句得出value1那列的值PS:很着急,解决马上就结了
问题描述得不清楚, value2的数据怎么那样子的
另外,是不是每条记录都有id?
不然怎么确定哪几条记录对应的合计在value1里?
或许不用更改declare @cc table(value2 int ,ID int)
insert into @cc select 100,5
insert into @cc select 175,5
insert into @cc select 175,5
insert into @cc select 220,6
insert into @cc select 160,6
insert into @cc select 170,6
insert into @cc select 150,6
insert into @cc select 100,6
--分组求和
select sum(value2),ID from @cc group by ID
declare @cc table(value2 int ,ID int)
insert into @cc select 100,5
insert into @cc select 175,5
insert into @cc select 175,5
insert into @cc select 220,6
insert into @cc select 160,6
insert into @cc select 170,6
insert into @cc select 150,6
insert into @cc select 100,6
--分组求和
select sum(value2) as value1,ID from @cc group by ID
/*result
value1 ID
----------- -----------
450 5
800 6
100
450 175 5
175
--------------------------
220
160
800 170 6
150
100
-------------------------- 如果你第一行,第三行ID是空的,怎么分组?[align=center]==== 思想重于技巧 ====
[/align]
100
450 175 5
175
--------------------------
220
160
800 170 6
150
100
-----------------------------------select sum(value2) value1 , ID group by id
value1 value2 ID
100
450 175 5
175
--------------------------
220
160
800 170 6
150
100
--------------------------
这个是我想在查询后显示的效果,而value1的值是算出来的,数据库中查不到
表结构还用说吗?看一眼就知道了,字段:value1,value2,ID,都是nvarchar型的
450 100 5
450 175 5
450 175 5
--------------------------
800 220 6
800 160 6
800 170 6
800 150 6
800 100 6
-------------------------- 这样比较清楚了?
insert into @cc select 100,5
insert into @cc select 175,5
insert into @cc select 175,5
insert into @cc select 220,6
insert into @cc select 160,6
insert into @cc select 170,6
insert into @cc select 150,6
insert into @cc select 100,6
--分组求和
select b.value1,a.* from @cc a left join
(select sum(value2) as value1,ID from @cc group by ID) b
on a.ID = b.ID
/*
value1 value2 ID
----------- ----------- -----------
450 100 5
450 175 5
450 175 5
800 220 6
800 160 6
800 170 6
800 150 6
800 100 6
declare @cc table(value2 int ,ID int)
insert into @cc select 100,5
insert into @cc select 175,5
insert into @cc select 175,5
insert into @cc select 220,6
insert into @cc select 160,6
insert into @cc select 170,6
insert into @cc select 150,6
insert into @cc select 100,6
--
select value1=(case when Num=1 then ltrim(d.value1) else '' end),c.value2,c.ID from (select value2,ID,Num=(select count(1) from
(select value2,ID,row_number() over(order by ID) as Num from @cc) a
where a.ID=b.ID and a.Num<=b.Num)
from (select value2,ID,row_number() over(order by ID) as Num from @cc) b) c left join (select sum(value2) as value1,ID from @cc group by ID) d
on c.ID = d.ID/*
value1 value2 ID
------------ ----------- -----------
450 100 5
175 5
175 5
800 220 6
160 6
170 6
150 6
100 6
declare @cc table(value2 int ,ID int)
insert into @cc select 100,5
insert into @cc select 175,5
insert into @cc select 175,5
insert into @cc select 220,6
insert into @cc select 160,6
insert into @cc select 170,6
insert into @cc select 150,6
insert into @cc select 100,6
--
select value1=(case when Num=1 then ltrim(d.value1) else '' end),c.value2,
ID=(case when Num=1 then ltrim(d.ID) else '' end) from (select value2,ID,Num=(select count(1) from
(select value2,ID,row_number() over(order by ID) as Num from @cc) a
where a.ID=b.ID and a.Num<=b.Num)
from (select value2,ID,row_number() over(order by ID) as Num from @cc) b) c left join (select sum(value2) as value1,ID from @cc group by ID) d
on c.ID = d.ID/*
value1 value2 ID
------------ ----------- ------------
450 100 5
175
175
800 220 6
160
170
150
100
declare @cc table(value2 int ,ID int)
insert into @cc select 100,5
insert into @cc select 175,5
insert into @cc select 175,5
insert into @cc select 220,6
insert into @cc select 160,6
insert into @cc select 170,6
insert into @cc select 150,6
insert into @cc select 100,6--
select value1=(case when Num=d.cun%2+1 then ltrim(d.value1) else '' end),c.value2,
ID=(case when Num=d.cun%2+1 then ltrim(d.ID) else '' end) from (select value2,ID,Num=(select count(1) from
(select value2,ID,row_number() over(order by ID) as Num from @cc) a
where a.ID=b.ID and a.Num<=b.Num)
from (select value2,ID,row_number() over(order by ID) as Num from @cc) b) c left join (select sum(value2) as value1,ID,count(1) as cun from @cc group by ID) d
on c.ID = d.ID
/*
value1 value2 ID
------------ ----------- ------------
100
450 175 5
175
220
800 160 6
170
150
100