select Name as 名称 ,Unit as 单位 ,本部=(select sum(counts) from tablename where Name=a.Name and Dept1='门市') ,本部=(select sum(counts) from tablename where Name=a.Name and Dept1='门市') ,总库存=(select sum(counts) from tablename where Name=a.Name) from tablename a group by Name
select 字段名 as 仓库,字段名 as xxx from table
没办法,交叉制表select 名称,单位,sum(case when 仓库名称 ='本部' then 数量 else 0 end) as 本部 ,sum(case when 仓库名称 ='门市' then 数量 else 0 end) as 门市 ,sum(数量) as 总库存 from table1 group by 名称,单位
newly_ignorant(不学无术) 的方式不错,如查不嫌麻烦的话, 还可以这样写:SELECT 名称,单位,sum(本部) AS 本部,sum(门市) AS 门市,sum(总库存) As 总库存 FROM (SELECT 名称,单位, (case when 仓库名称 ='本部' then ISNULL(数量,0) else 0 end) as 本部, (case when 仓库名称 ='门市' then ISNULL(数量,0) else 0 end) as 门市,,数量 as 总库存 from table1 ) AS D_View WHERE 1=1 group by 名称,单位
商品名1 个 10 本部
商品名1 个 10 门市
商品名2 对 20 本部
商品名2 对 20 门市
要变成标题:名称,单位,本部,门市,总库存
商品名1 个 10 10 20
商品名2 对 20 20 40
sql2000的sql怎么写?
,Unit as 单位
,本部=(select sum(counts) from tablename where Name=a.Name and Dept1='门市')
,本部=(select sum(counts) from tablename where Name=a.Name and Dept1='门市')
,总库存=(select sum(counts) from tablename where Name=a.Name)
from tablename a
group by Name
,sum(case when 仓库名称 ='门市' then 数量 else 0 end) as 门市
,sum(数量) as 总库存
from table1
group by 名称,单位
还可以这样写:SELECT 名称,单位,sum(本部) AS 本部,sum(门市) AS 门市,sum(总库存) As 总库存
FROM (SELECT 名称,单位,
(case when 仓库名称 ='本部' then ISNULL(数量,0) else 0 end) as 本部,
(case when 仓库名称 ='门市' then ISNULL(数量,0) else 0 end) as 门市,,数量 as 总库存
from table1 ) AS D_View
WHERE 1=1
group by 名称,单位