如何删除三表关联数据？

2009-11-01 09:51:10 294 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ESP32+micro-ROS机器人开发实战：轻量级ROS2嵌入式集成 ROS2是现代机器人软件栈的核心框架，其基于DDS的分布式通信与QoS保障能力为多节点协同提供基础支撑；而micro-ROS作为专为微控制器优化的ROS2兼容中间件，解决了资源受限设备（如ESP32）接入ROS2生态的关键难题。其通过分层架构将DDS通信下沉至上位机Agent，使MCU仅需运行轻量Client，大幅降低内存开销（实测<180KB）并保留标准ROS2接口语义。该方案广泛应用于教育机器人、移动底盘、传感器节点等嵌入式场景，尤其适合基于ESP-IDF开发的Wi-Fi/BLE物联网机器人系统。本文以阅读详情

如A表
id name date flag
01 a 2009-01-01 4
02 b 2009-05-01 1
03 c 2009-05-01 4
04 d 2009-07-01 1
......
b表
id xmid xmname fair
01 a01 aaaa 300
01 a02 bbbbb 40
01 a03 ccccc 10
02 a07 dddd 0
02 a06 eeee 0
02 a14 ffff 30
03 a25 g 56
04 a25 g 23
.........
C表
xmid xm price count
a01 1 6 2
a01 2 5 6
a01 3 3 54
a02 1 4 10

.....

a表通过id和B表关联，a表和b表是一对多关系,b表和c表通过xmid关联，b表和c表是多对多关系,
请问，如何将a表中日期在‘2009-06-01’以前和flag=4的b表和c表数据删除，但保留a表中的数据?

...全文

【解决问题】‘vue‘ 不是内部或外部命令，也不是可运行的程序或批处理文件。【解决问题】'vue' 不是内部或外部命令，也不是可运行的程序或批处理文件。阅读详情

DTWUJP

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

feixianxxx 2009-11-01

打赏
举报

create table #A

(

  id varchar(10),

  name varchar(20),

  date datetime,

  flag int

)

insert into #A select '01','a','2009-01-01',4

union all select '02','b','2009-05-01',1

union all select '03','c','2009-05-01',4

union all select '04','d','2009-07-01',1

create table #B1

(

  id varchar(10),

  xmid varchar(10),

  xmname nvarchar(20),

  fair int 

)

insert into #B1 select '01','a01','aaaa',300

union all select '01','a02','bbbbb',40

union all select '01','a03','ccccc',10

union all select '02','a07','dddd',0

union all select '02','a06','eeee',0

union all select '02','a14','ffff',30

union all select '03','a25','g',56

union all select '04','a25','g',23

create table #C1

(

  xmid varchar(10),

  xm int,

  price int,

  count int

)

insert into #C1 select 'a01',1,6,2

union all select 'a01',2,5,6

union all select 'a01',3,3,54

union all select 'a02',1,4,10

GO

CREATE PROC DEL_A 

AS

BEGIN

--先要删除#c1 

delete #C1 

from #a a join #b1 b on a.id=b.id 

JOIN #C1 C ON B.xmid=C.xmid

where [date]<'2009-06-01' and flag=4

--然后删除关联的#B1

delete #b1 

from #a a join #b1 b on a.id=b.id 

where [date]<'2009-06-01' and flag=4

END 

--

exec dbo.DEL_A 

--检测--

SELECT * FROM #B1

SELECT * FROM #C1

/*

id         xmid       xmname               fair

---------- ---------- -------------------- -----------

02         a07        dddd                 0

02         a06        eeee                 0

02         a14        ffff                 30

04         a25        g                    23



(4 行受影响)



xmid       xm          price       count

---------- ----------- ----------- -----------

*/

果果数据用下

lidanzi 2009-11-01

打赏
举报

学习来的

feixianxxx 2009-11-01

打赏
举报

......

ws_hgo 2009-11-01

打赏
举报

create table #A

(

  id varchar(10),

  name varchar(20),

  date datetime,

  flag int

)

insert into #A select '01','a','2009-01-01',4

union all select '02','b','2009-05-01',1

union all select '03','c','2009-05-01',4

union all select '04','d','2009-07-01',1

create table #B1

(

  id varchar(10),

  xmid varchar(10),

  xmname nvarchar(20),

  fair int 

)

insert into #B1 select '01','a01','aaaa',300

union all select '01','a02','bbbbb',40

union all select '01','a03','ccccc',10

union all select '02','a07','dddd',0

union all select '02','a06','eeee',0

union all select '02','a14','ffff',30

union all select '03','a25','g',56

union all select '04','a25','g',23

create table #C1

(

  xmid varchar(10),

  xm int,

  price int,

  count int

)

insert into #C1 select 'a01',1,6,2

union all select 'a01',2,5,6

union all select 'a01',3,3,54

union all select 'a02',1,4,10

/*

表通过id和B表关联，a表和b表是一对多关系,b表和c表通过xmid关联，b表和c表是多对多关系, 

请问，如何将a表中日期在‘2009-06-01’以前和flag=4的b表和c表数据删除，但保留a表中的数据?

*/



--删除C表的

delete from #C1 where xmid in

(

 select xmid from #B1 where id in (select id from #A where date<'2009-06-01' and flag=4)

)

--删除B表

delete from #B1 where id in (select id from #A where date<'2009-06-01' and flag=4)

SQL77 2009-11-01

打赏
举报

建立外键关联起来呀,用级联方便

要不写个触发器

xman_78tom 2009-11-01

打赏
举报



delete C where xmid in

(select xmid from B where id in (select id from A where [date]<'2009-06-01' and flag=4))



delete B where id in (select id from A where [date]<'2009-06-01' and flag=4)

DTWUJP 2009-11-01

打赏
举报

谢谢楼上，表中没有外键，能给个具体语句吗？

guguda2008 2009-11-01

打赏
举报

级联删除或者存储过程

如何删除三表关联数据？

相关推荐