Access数据库的多表更新方法和列变行一例

最新推荐文章于 2021-08-21 08:46:23 发布

hsg77

最新推荐文章于 2021-08-21 08:46:23 发布

阅读量4.3k

点赞数

CC 4.0 BY-SA版权

分类专栏：总结数据库文章标签： access 数据库 join sql

本文链接：https://blog.csdn.net/hsg77/article/details/7420226

数据库同时被 2 个专栏收录

86 篇文章

订阅专栏

总结

83 篇文章

订阅专栏

本文介绍了Access数据库中多表更新的方法，并通过具体实例展示了用途分区列变行的操作技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

(1)Access数据库的多表更新方法
x = "update " + DLTB + " a inner join tbarea2 b on a.objectid=b.FID set a." + fd_dltb_xzdwmj + "=b.area_xzdw, a." + fd_dltb_lxdwmj + "=b.area_lxdw";
SQLList.Add(x);

update dltb a inner join tbarea2 b on a.objectid=b.fid set a.dltb_xzdwmj=b.area_xzdw,a.dltb_lxdwmj=b.area_lxdw;

(2)ytfq sql(用途分区) 列变行一例
select xzqdm,sum(t4) as jbntbhqmj,sum(t5) as ybndqmj,sum(t6) as czjsydqmj,sum(t7) as chzjsydqmj,sum(t8) as dlgkydqmj,
sum(t9) as fjlyydqmj,sum(t10) as sthjaqkzqmj,sum(t11) as zrywhycbhqmj,sum(t12) as lyydqmj,sum(t13) as myydqmj,
sum(t14) as qtydqmj
from
(
select xzqdm,sum(fqmj) as t4,0 as t5,0 as t6,0 as t7,0 as t8,0 as t9,0 as t10,0 as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='010' group by xzqdm
union all
select xzqdm,0 as t4,sum(fqmj) as t5,0 as t6,0 as t7,0 as t8,0 as t9,0 as t10,0 as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='020' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,sum(fqmj) as t6,0 as t7,0 as t8,0 as t9,0 as t10,0 as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='030' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,sum(fqmj) as t7,0 as t8,0 as t9,0 as t10,0 as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='040' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,0 as t7,sum(fqmj) as t8,0 as t9,0 as t10,0 as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='050' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,0 as t7,0 as t8,sum(fqmj) as t9,0 as t10,0 as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='060' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,0 as t7,0 as t8,0 as t9,sum(fqmj) as t10,0 as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='070' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,0 as t7,0 as t8,0 as t9,0 as t10,sum(fqmj) as t11,0 as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='080' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,0 as t7,0 as t8,0 as t9,0 as t10,0 as t11,sum(fqmj) as t12, 0 as t13,0 as t14 from xb_ytfq_data where ytfqdm='090' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,0 as t7,0 as t8,0 as t9,0 as t10,0 as t11,0 as t12, sum(fqmj) as t13,0 as t14 from xb_ytfq_data where ytfqdm='100' group by xzqdm
union all
select xzqdm,0 as t4,0 as t5,0 as t6,0 as t7,0 as t8,0 as t9,0 as t10,0 as t11,0 as t12, 0 as t13,sum(fqmj) as t14 from xb_ytfq_data where ytfqdm='990' group by xzqdm
) as t
group by xzqdm