根据type 聚合 同一个name的money 并且把 不同type的数据分成2列group by 来分的话 是id name money type 1 1 31 1 2 1 84 2我想要一行中统计这些数据 我记得sql 有个函数可以实现这个 但是忘了 求大虾帮忙 好像用到 h开头的什么函数了
一条sql 语句啊 sql 2005 中的新函数 组合使用 可以聚合同时分列
你仿照我这个一下,让你动动手,别忘记给分哦select docno,sum(case when left(Part,1) = '1' then Qty else 0 end) as ZJQty, sum(case when left(Part,1) <> '1' and left(Part,1) <> '8' then Qty else 0 end) as FJ1Qty, sum(case when left(Part,1) <> '1' and left(Part,1) <> '8' then 0 else 0 end) as FJ2Qty, sum(case when left(Part,1) = '8' then Qty else 0 end) as ZZLQty from WIM20200 group by docno
我贴下pivot的方法吧,行列转换很简单 原数据 id name money type 1 1 31 1 2 1 84 2 SELECT name, [1] AS money1, [2] AS money2 FROM (SELECT name, type, money FROM Test) AS a PIVOT (sum(money) FOR type IN ([1], [2])) AS pvt结果 name money1 money2 1 31 84
贴错了 原数据 id name money type 1 1 10 1 2 1 21 1 3 1 33 2 4 1 51 2
没有null出现,你是用的我贴的代码吗?选择的时候不要有id列,因为id列不一样所以聚合就会出现逆贴的代码那样了SELECT name, [1] AS money1, [2] AS money2 FROM (SELECT name, type, money FROM Test) AS a PIVOT (sum(money) FOR type IN ([1], [2])) AS pvt
是id name money type
1 1 31 1
2 1 84 2我想要一行中统计这些数据
我记得sql 有个函数可以实现这个 但是忘了
求大虾帮忙 好像用到 h开头的什么函数了
sql 2005 中的新函数 组合使用
可以聚合同时分列
sum(case when left(Part,1) <> '1' and left(Part,1) <> '8' then Qty else 0 end) as FJ1Qty,
sum(case when left(Part,1) <> '1' and left(Part,1) <> '8' then 0 else 0 end) as FJ2Qty,
sum(case when left(Part,1) = '8' then Qty else 0 end) as ZZLQty
from WIM20200 group by docno
原数据
id name money type
1 1 31 1
2 1 84 2
SELECT name, [1] AS money1, [2] AS money2
FROM (SELECT name, type, money
FROM Test) AS a
PIVOT (sum(money) FOR type IN ([1], [2])) AS pvt结果
name money1 money2
1 31 84
id name money type
1 1 10 1
2 1 21 1
3 1 33 2
4 1 51 2
name money1 money2
1 10 null
1 21 null
1 null 33
1 null 51
嗯...点解了
FROM (SELECT name, type, money
FROM Test) AS a
PIVOT (sum(money) FOR type IN ([1], [2])) AS pvt
FROM Test