r********9
发帖数: 39 | 1 有个表 Table（Time,ID，Value）
例如
Time ID Value
10/20/2011 a 3
10/21/2011 a 2
10/22/2011 a 1
10/23/2011 a 2
10/20/2011 b 2
10/21/2011 b 3
10/22/2011 b 4
10/23/2011 b 5
10/20/2011 c 2
10/21/2011 c 3
10/22/2011 c 4
10/23/2011 c 1
如何写个简单的query实现在相同Time下，不同ID的Value之间进行简单的计算？如果计算公式
是(a+b)*c，那返回的结果为
10/20/2011 10
10/21/2011 15
10/22/2011 20
10/23/2011 7 | i*****w
发帖数: 75 | 2 Declare @tbl TABLE (dt datetime, ID varchar(2), value int)
INSERT INTO @tbl(dt, ID, value)
SELECT '10/20/2011', 'a', 3
UNION ALL
SELECT '10/21/2011', 'a', 2
UNION ALL
SELECT '10/22/2011', 'a', 1
UNION ALL
SELECT '10/23/2011', 'a', 2
UNION ALL
SELECT '10/20/2011', 'b', 2
UNION ALL
SELECT '10/21/2011', 'b', 3
UNION ALL
SELECT '10/22/2011', 'b', 4
UNION ALL
SELECT '10/23/2011', 'b', 5
UNION ALL
SELECT '10/20/2011', 'c', 2
UNION ALL
SELECT '10/21/2011', 'c', 3
UNION ALL
SELECT '10/22/2011', 'c', 4
UNION ALL
SELECT '10/23/2011', 'c', 1
SELECT dt, (a+b)*c as Result from
(
SELECT dt, ID, value
FROM @tbl)p
pivot
(max(value) for ID in (a,b,c)
) AS pvt | r********9
发帖数: 39 | 3 赞大牛！pivot这个函数真给力
【在 i*****w 的大作中提到】
: Declare @tbl TABLE (dt datetime, ID varchar(2), value int)
: INSERT INTO @tbl(dt, ID, value)
: SELECT '10/20/2011', 'a', 3
: UNION ALL
: SELECT '10/21/2011', 'a', 2
: UNION ALL
: SELECT '10/22/2011', 'a', 1
: UNION ALL
: SELECT '10/23/2011', 'a', 2
: UNION ALL
| g***l
发帖数: 18555 | 4 需要构建PIVOT TABLE
TIME, A_VALUE, B_VALUE, C_VALUE
注意SAME TIME+ A_VALUE不能有重复的，你的TABLE要有这个麻烦了
10/20/2011 a 3
10/20/2011 a 4 | i*****w
发帖数: 75 | 5 Even if you have duplicated records for the same day, that's still fine with
the pivot function. All you have to do is to change the aggregation
function. In my example, I used MAX and you could change it to SUM, AVG, etc
. based on your business rules.
【在 g***l 的大作中提到】
: 需要构建PIVOT TABLE
: TIME, A_VALUE, B_VALUE, C_VALUE
: 注意SAME TIME+ A_VALUE不能有重复的，你的TABLE要有这个麻烦了
: 10/20/2011 a 3
: 10/20/2011 a 4
| n********6
发帖数: 1511 | 6 Pivot is powerful.
Everyday is a learning day.
with
etc
【在 i*****w 的大作中提到】
: Even if you have duplicated records for the same day, that's still fine with
: the pivot function. All you have to do is to change the aggregation
: function. In my example, I used MAX and you could change it to SUM, AVG, etc
: . based on your business rules.
|
|
