Notice (8): file_put_contents(): Write of 270 bytes failed with errno=28 No space left on device [CORE/src/Log/Engine/FileLog.php, line 140]

        if (!$mask) {
            file_put_contents($pathname, $message . "\n", FILE_APPEND);

file_put_contents - [internal], line ??
Cake\Log\Engine\FileLog::log() - CORE/src/Log/Engine/FileLog.php, line 140
Cake\Log\Log::write() - CORE/src/Log/Log.php, line 392
Cake\Log\Log::warning() - CORE/src/Log/Log.php, line 477
DebugKit\ToolbarService::isSuspiciouslyProduction() - ROOT/vendor/cakephp/debug_kit/src/ToolbarService.php, line 169
DebugKit\ToolbarService::isEnabled() - ROOT/vendor/cakephp/debug_kit/src/ToolbarService.php, line 105
DebugKit\Plugin::bootstrap() - ROOT/vendor/cakephp/debug_kit/src/Plugin.php, line 48
Cake\Http\BaseApplication::pluginBootstrap() - CORE/src/Http/BaseApplication.php, line 182
Cake\Http\Server::bootstrap() - CORE/src/Http/Server.php, line 111
Cake\Http\Server::run() - CORE/src/Http/Server.php, line 79
[main] - ROOT/webroot/index.php, line 40

Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 5131 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Code Context

        $success = $this->_File->ftruncate(0) &&
            $this->_File->fwrite($contents) &&
            $this->_File->fflush();

SplFileObject::fwrite() - [internal], line ??
Cake\Cache\Engine\FileEngine::set() - CORE/src/Cache/Engine/FileEngine.php, line 141
Cake\Database\Schema\CachedCollection::describe() - CORE/src/Database/Schema/CachedCollection.php, line 85
Cake\ORM\Table::getSchema() - CORE/src/ORM/Table.php, line 513
Cake\ORM\Query::addDefaultTypes() - CORE/src/ORM/Query.php, line 290
Cake\ORM\Query::__construct() - CORE/src/ORM/Query.php, line 184
Cake\ORM\Table::query() - CORE/src/ORM/Table.php, line 1702
Cake\ORM\Table::find() - CORE/src/ORM/Table.php, line 1263
App\Controller\NewsController::action_() - APP/Controller/NewsController.php, line 6437
App\Controller\NewsController::get_data() - APP/Controller/NewsController.php, line 5701
App\Controller\NewsController::action_cache() - APP/Controller/NewsController.php, line 5625
App\Controller\NewsController::cache_action() - APP/Controller/NewsController.php, line 5281
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3226
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317

Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 284 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Code Context

        $success = $this->_File->ftruncate(0) &&
            $this->_File->fwrite($contents) &&
            $this->_File->fflush();

SplFileObject::fwrite() - [internal], line ??
Cake\Cache\Engine\FileEngine::set() - CORE/src/Cache/Engine/FileEngine.php, line 141
Cake\I18n\TranslatorRegistry::get() - CORE/src/I18n/TranslatorRegistry.php, line 206
Cake\I18n\I18n::getTranslator() - CORE/src/I18n/I18n.php, line 148
__ - CORE/src/I18n/functions.php, line 45
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3420
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 5437 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Code Context

        $success = $this->_File->ftruncate(0) &&
            $this->_File->fwrite($contents) &&
            $this->_File->fflush();

SplFileObject::fwrite() - [internal], line ??
Cake\Cache\Engine\FileEngine::set() - CORE/src/Cache/Engine/FileEngine.php, line 141
Cake\Database\Schema\CachedCollection::describe() - CORE/src/Database/Schema/CachedCollection.php, line 85
Cake\ORM\Table::getSchema() - CORE/src/ORM/Table.php, line 513
Cake\ORM\Query::addDefaultTypes() - CORE/src/ORM/Query.php, line 290
Cake\ORM\Query::__construct() - CORE/src/ORM/Query.php, line 184
Cake\ORM\Table::query() - CORE/src/ORM/Table.php, line 1702
Cake\ORM\Table::find() - CORE/src/ORM/Table.php, line 1263
App\Controller\NewsController::action_() - APP/Controller/NewsController.php, line 6426
App\Controller\NewsController::get_data() - APP/Controller/NewsController.php, line 5701
App\Controller\NewsController::action_cache() - APP/Controller/NewsController.php, line 5625
App\Controller\NewsController::cache_action() - APP/Controller/NewsController.php, line 5281
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3436
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317

Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 27428 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Code Context

        $success = $this->_File->ftruncate(0) &&
            $this->_File->fwrite($contents) &&
            $this->_File->fflush();

SplFileObject::fwrite() - [internal], line ??
Cake\Cache\Engine\FileEngine::set() - CORE/src/Cache/Engine/FileEngine.php, line 141
Cake\Cache\Cache::write() - CORE/src/Cache/Cache.php, line 266
App\Controller\NewsController::action_() - APP/Controller/NewsController.php, line 6766
App\Controller\NewsController::get_data() - APP/Controller/NewsController.php, line 5701
App\Controller\NewsController::action_cache() - APP/Controller/NewsController.php, line 5493
App\Controller\NewsController::cache_action() - APP/Controller/NewsController.php, line 5281
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3698
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73

Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 4076 of 4085 bytes [APP/Controller/NewsController.php, line 5571]

Code Context

               {
                      $a=unserialize($line2);
               }

unserialize - [internal], line ??
App\Controller\NewsController::action_cache() - APP/Controller/NewsController.php, line 5571
App\Controller\NewsController::cache_action() - APP/Controller/NewsController.php, line 5281
App\Controller\NewsController::article_show() - APP/Controller/NewsController.php, line 4290
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3822
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Routing\Middleware\RoutingMiddleware::process() - CORE/src/Routing/Middleware/RoutingMiddleware.php, line 161

Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 3980 of 4085 bytes [APP/Controller/NewsController.php, line 5571]

Code Context

               {
                      $a=unserialize($line2);
               }

unserialize - [internal], line ??
App\Controller\NewsController::action_cache() - APP/Controller/NewsController.php, line 5571
App\Controller\NewsController::cache_action() - APP/Controller/NewsController.php, line 5281
App\Controller\NewsController::article_show() - APP/Controller/NewsController.php, line 4310
App\Controller\NewsController::view() - APP/Controller/NewsController.php, line 3822
Cake\Controller\Controller::invokeAction() - CORE/src/Controller/Controller.php, line 539
Cake\Controller\ControllerFactory::handle() - CORE/src/Controller/ControllerFactory.php, line 140
Cake\Controller\ControllerFactory::invoke() - CORE/src/Controller/ControllerFactory.php, line 115
Cake\Http\BaseApplication::handle() - CORE/src/Http/BaseApplication.php, line 317
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 77
Cake\Http\Middleware\CsrfProtectionMiddleware::process() - CORE/src/Http/Middleware/CsrfProtectionMiddleware.php, line 164
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\I18n\Middleware\LocaleSelectorMiddleware::process() - CORE/src/I18n/Middleware/LocaleSelectorMiddleware.php, line 61
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Http\Middleware\BodyParserMiddleware::process() - CORE/src/Http/Middleware/BodyParserMiddleware.php, line 157
Cake\Http\Runner::handle() - CORE/src/Http/Runner.php, line 73
Cake\Routing\Middleware\RoutingMiddleware::process() - CORE/src/Routing/Middleware/RoutingMiddleware.php, line 161

一道关于数据库(经典父子级ID关联)更新题

数据库操作教程 2022-09-23 17:41:44 转载来源: 网络整理/侵权必删

昨天，一同事发过来的一道数据库题目，就是哪种经典的父子级ID在同一数据库表中设计类型。需要在原表中添加一个字段，同时，将该节点的父子级详细信息插入到原表新增的一字段中，具体效果如下图

昨天，一同事发过来的一道数据库题目，就是哪种经典的父子级 ID 在同一数据库表中设计类型。需要在原表中添加一个字段，同时，将该节点的父子级详细信息插入到原表新增的一字段中，具体效果如下图。

AreaCode 、AreaName、ParentCode （原表三字段）. Content __新增字段，更新该 AreaCode 下所有父级菜单信息至新增至原表的 Content 字段下面，用红线框起来（意思应该讲明白了吧.）

AreaCode：地区 ID AreaName：地区介绍 ParentCode：父级 AreaCode （Content---将该 AreaCode 下的所有父级 AreaName 拼成类似：越城区,绍兴市,浙江省字符串插入）

更新前:

更新后:

在项目中，相对于此种内容，一般会存储在内存中，作为缓存使用，避免频繁连接数据库，带来的性能问题。

代码就不上了，简单的递归即可实现。

1 先从数据库将该表所有内容取出来，缓存起来。（该表差不多 3000 左右条数据）
2 再写个递归函数，根据每次传入的 AreaCode（第一步已缓存数据库取出来的整表集合可转成 Dictionary<int,AreaRegion> 类型），获取其 ParentCode 值，只要 ParentCode 不为 0（即不为顶级结点，ParentCode 为顶级结点），那么，继续调用该函数，传入本次查询实体的 ParentCode 信息。
3 在代码很方便查出父结点所有信息，再 Update 至数据库即可。

感谢清海扬波 grayboy 完全可以通过公用表表达式完成

Code 如下：

with cte(areacode,areaName,content) as(--- 查询 ParentCode 为 0 （ParentCode 为 0 的为顶级菜单）select areacode,areaName,cast(areaName as varchar(50)) AS contentfrom [AreaRegion] where parentcode=0union all-- 再进行递归查询 select a.areacode,a.areaName,cast(a.content+','+b.areaName as varchar(50） AS content 9 from [AreaRegion] a 10 inner join cte b on a.parentcode=b.areacode )select * from cte

--- 楼主当初想法（我想复杂了 SB 了）

1 楼主，首当其冲想到的是，即采用游标存储整表记录。

2 游标每次取得一条记录的 AreaCode ，再通过该 AreaCode 查出其所有父级表（包括自身）信息，最后，将查询出的该表某字段拼成字符串，

插入表变量中（两字段分别为：AreaCode、Content）。

3 最后，将定义的表变量与原表通过 INNER JOIN（表变量中插入了 AreaCode 字段），进行联表更新。

更新脚本具体如下：

USE JKCRMGO--定义游标DECLARE updateCursor CURSOR SCROLL FORSELECT A.AreaCode  FROM DBO.AreaRegion A--打开游标OPEN updateCursor--定义变量 存储依次获取游标值DECLARE @aID NVARCHAR(30)=''---定义变量 存储父结点具体信息DECLARE @pStr NVARCHAR(300)='';--定义表变量 存储两字段 AreaCode Content ( Content :为该字段所有父级菜单信息)DECLARE @TempTable TABLE(AreaCode INT PRIMARY KEY,Content NVARCHAR(3000))--首次获取游标第一个值 插入变量 @AIDFETCH FIRST FROM updateCursor INTO @AIDWHILE(@@FETCH_STATUS=0)BEGIN--PRINT(@AID)   ;---通过传入的 @AID ,查询其所有父级菜单信息WITH TB AS(   ---递归查询父子菜单信息   公用表表达式递归查询   SELECT A.*,0 AS LEVEL FROM JKCRM.DBO.AreaRegion A    WHERE A.AreaCode=@AID   UNION ALL   SELECT B.* ,LEVEL+1 AS LEVEL FROM TB A INNER JOIN JKCRM.DBO.AreaRegion B   ON A.ParentCode=B.AreaCode )---再将查询的表信息 拼接成字符串 此处 即采用 SELECT 循环查询SELECT @pStr=@pStr+  CASE WHEN @pStr='' THEN TB.AreaName ELSE ','+TB.AreaName END FROM TB ORDER BY TB.LEVEL ASC -- PRINT(@pSTR) --插入表变量INSERT INTO @TempTable SELECT @aID,@pStr---将此次通过 AreaCode 获取的该节点 @pStr 置空SET @pStr='' FETCH NEXT FROM updateCursor INTO @AIDEND--SELECT B.AreaCode,B.AreaName,B.ParentCode,A.Content,A.AreaCode--FROM @TempTable A RIGHT JOIN DBO.AreaRegion B ON A.AreaCode=B.AreaCode ORDER BY   B.AreaCode ---此处 即可进行 UPDATE 更新操作 UPDATE A SET A.Content=B.Content  FROM   DBO.AreaRegion A INNER JOIN @TempTable B ON A.AreaCode=B.AreaCode--关闭释放游标CLOSE updateCursorDEALLOCATE updateCursor

原表部分脚本 SQL

/*Navicat SQL Server Data TransferSource Server     : SQLSource Server Version : 120000Source Host      : .:1433Source Database    : JKCRMSource Schema     : dboTarget Server Type  : SQL ServerTarget Server Version : 120000File Encoding     : 65001Date: 2015-06-12 11:20:40*/-- ------------------------------ Table structure for AreaRegion-- ----------------------------DROP TABLE [dbo].[AreaRegion]GOCREATE TABLE [dbo].[AreaRegion] ([AreaCode] varchar(10) NOT NULL ,[AreaName] varchar(50) NULL ,[ParentCode] varchar(10) NULL ,[Content] nvarchar(200) NULL )GO-- ------------------------------ Records of AreaRegion-- ----------------------------INSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110000', N'北京市', N'0', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110100', N'东城区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110200', N'西城区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110300', N'崇文区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110400', N'宣武区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110500', N'朝阳区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110600', N'丰台区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110700', N'石景山区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110800', N'海淀区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'110900', N'门头沟区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111000', N'房山区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111100', N'通州区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111200', N'顺义区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111300', N'昌平区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111400', N'大兴区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111500', N'怀柔区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111600', N'平谷区', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111700', N'密云县', N'110000', null)GOGOINSERT INTO [dbo].[AreaRegion] ([AreaCode], [AreaName], [ParentCode], [Content]) VALUES (N'111800', N'延庆县', N'110000', null)

标签: 一道关于数据库经典父子 ID 关联更新

声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！

上一篇: 数据库表的创建、管理和数据操作(实验一)

上一篇: SQLServer无法收缩日志文件的原因分析及解决办法

娱乐休闲

工商与经济

电脑与网络

公司与企业

教育与培训

文学

艺术

体育与健身

新闻与媒体

卫生与健康

科学/文化

生活与服务

研究所/中心

高等院校

财税

地理学

各地卫生

搜索引擎

散文/随笔

商场市场

民间艺术

展览/活动

机构组织

石油/天然气

一道关于数据库(经典父子级ID关联)更新题

热门文章

热点

标签列表

分享到:

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索

一道关于数据库(经典父子级ID关联)更新题

猜您喜欢

热门文章

热点

标签列表

分享到:

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索