也可以吧
Select
ID,
SUM(Case v2 When 0 Then val Else 0 End) As val1,
SUM(Case v2 When 1 Then val Else 0 End) As val2,
SUM(val) As total
from
(select a.ID as ID,a.val as val,b.v2 as v2 from table2 a,table1 b where a.v1=b.v1)t
Group By ID
Order By ID
Select
ID,
SUM(Case v2 When 0 Then val Else 0 End) As val1,
SUM(Case v2 When 1 Then val Else 0 End) As val2,
SUM(val) As total
from
(select a.ID as ID,a.val as val,b.v2 as v2 from table2 a,table1 b where a.v1=b.v1)t
Group By ID
Order By ID
解决方案 »
- sqlserver2005数据库,位置列的批量修改
- 分页存储过程如何调用存储过程?
- on [primary] textimage_on [primary]后面的textimage_on [primary]什么意思?
- 怎么判断一个字段上是否存在索引?
- 在MS-SQL server数据库中要保存文件,怎么样定义它的类型啊!!!!
- 从access中如何将一个表的某些字段导入sql server中一个不同名的表的不同名的字段中?
- 按姓氏笔画排序问题?
- order by case when @OrderBy=1 then 'CreateDate desc' end 不起作用?
- 小弟请问各位大虾用vb6.0在SQL2000中插入图片怎么做?(请给出编码)不胜感激
- 请教一下编码问题
- 只有 exec('sql语句部分')才能执行带有变量的sql语句吗?高手进来看看...
- 急!多表查询的问题!求助!!
Create Table TEST1(v2 Int,v1 Varchar(10))
Create Table TEST2(ID Varchar(10),val Int,v1 Varchar(10))
--插入数据
Insert TEST1 Values(0, 'a')
Insert TEST1 Values(0, 'b')
Insert TEST1 Values(0, 'c')
Insert TEST1 Values(1, 'd')
Insert TEST1 Values(1, 'e')Insert TEST2 Values('a', 1, 'a')
Insert TEST2 Values('b', 5, 'b')
Insert TEST2 Values('c', 6, 'c')
Insert TEST2 Values('a', 4, 'd')
Insert TEST2 Values('b', 2, 'e')
--测试
Select
B.ID,
SUM(Case A.v2 When 0 Then B.val Else 0 End) As val1,
SUM(Case A.v2 When 1 Then B.val Else 0 End) As val2,
SUM(val) As total
from TEST2 B
Inner Join TEST1 A
On A.v1=B.v1
Group By B.ID
Order By B.ID
--删除测试环境
Drop Table TEST1,TEST2
--结果
/*
ID val1 val2 total
a 1 4 5
b 5 2 7
c 6 0 6
*/