sqlserver2016版添加的json操作
sqlserver自带的json的操作主要能实现：json验证、字段提取、修改、表格转json、json转表格式
一、isjson实现json验证
--结果：1-json格式正确；0-json格式错误 declare @json1 nvarchar(max) = '{"id":1,"name":"ki","age":22,"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}' print isjson(@json1)
结果：1
二、JSON_VALUE取出json值
--只能取出json值，无法取出对象和数组 declare @json2 nvarchar(max) = '{"id":1,"name":"ki","ages":["22","33"],"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}' select JSON_VALUE(@json2,'$.name') --取出数组中的单个值 select JSON_VALUE(@json2,'$.ages[0]')
三、JSON_QUERY取出json中的对象和数组
declare @json3 nvarchar(max) = '{"id":1,"name":"ki","age":22,"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}' select JSON_QUERY(@json3,'$.son') select JSON_QUERY(@json3,'$.list')
结果：
结果1：{"name":"son","age":1}
结果2：[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]
四、JSON_MODIFY修改json对象和值
JSON_MODIFY可以实现修改value（包括：字符串、数组、子json），删除键值对，重命名key
declare @json4 nvarchar(max) = '{"id":1,"name":"ki","ages":[22,33],"son":{"name":"son","age":1},"list":[{"city":"上海","area":"松江"},{"city":"上海","area":"松江"}]}' --a.修改值，默认修改字符串。无法直接修改对象 SET @json4=JSON_MODIFY(@json4,'$.name','newNmae') select @json4 --修改数组的值 SET @json4=JSON_MODIFY(@json4,'$.ages[0]',55) select @json4 --追加append,将值追加到数组末尾 SET @json4=JSON_MODIFY(@json4,'append $.ages',55) select @json4 --lax/strict --新增键值对，如果要修改的key在json中不存在默认新建，默认是lax。 --如果使用了strict，则要修改的key必须在json中存在，否则会报错 SET @json4=JSON_MODIFY(@json4,'lax $.newKey','test') select @json4 -- SET @json4=JSON_MODIFY(@json4,'strict $.newKey2','test')--该行报错 --删除一个key-value --将value设置成null，则该key-vaule消失 SET @json4=JSON_MODIFY(@json4,'$.newKey',null) select @json4 --b.多次修改，使用嵌套方式 SET @json4=JSON_MODIFY(JSON_MODIFY(@json4,'$.name','hello'),'append $.ages',33) select @json4 --c.修改对象（子json和数组） --使用JSON_QUERY方法，将要修改的的新值使用JSON_QUERY输出，保证sqlserver能识别出该值为对象 --修改子json实例 SET @json4=JSON_MODIFY(@json4,'$.son',JSON_QUERY(JSON_MODIFY(@json4,'$.son.age',18),'$.son')) select @json4 --修改数组 SET @json4=JSON_MODIFY(@json4,'$.ages',JSON_QUERY(JSON_MODIFY(@json4,'$.ages[0]',1111),'$.ages')) select @json4 --d.重命名key --先向json中新增一个新的key-value，value与原值相同，key是新值；然后将原key的value设置为null。原key将自动消失。 SET @json4=JSON_MODIFY(JSON_MODIFY(@json4,'$.msgId',1),'$.id',null) select @json4
五、json字符串转化成表格式
--如果没有数组，可以将OUTER APPLY OPENJSON() WITH()省略 declare @json5 nvarchar(max) = '{"id":1,"name":"ki","age":22,"son":{"name":"son","age":1},"list":[{"city":"上海","area":"青浦"},{"city":"上海","area":"松江"}]}' select * from openjson(@json5) with( id nvarchar(500) 'strict $.id',--strict表示json中必须包含该字段 name nvarchar(500) 'strict $.name', age nvarchar(500) 'strict $.age', son_name nvarchar(500) 'strict $.son.name', son_age nvarchar(500) 'strict $.son.age', list nvarchar(max) '$.list' AS JSON--必须是nvarchar(max) 类型 ) OUTER APPLY OPENJSON(list) WITH( city varchar(80) '$.city', area varchar(80) '$.area' );
--更多级json的转化 declare @json51 nvarchar(max) = '{"msgId":"msgId666","orderNo":"00000","orderType":"00000","orderLevel":"00000","goodsList":[{"lineNo":0,"shipperId":"0021","goodsId":"0021","goodsName":"一年级语文","barCode":"9787107291029","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"4","masUnitQty":"40"},{"equipmentId":"equipmentId","sortPortId":"6","masUnitQty":"30"}]},{"lineNo":0,"shipperId":"0000","goodsId":"0000","goodsName":"一年级数学","barCode":"9787107291029","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"8","masUnitQty":"10"},{"equipmentId":"equipmentId","sortPortId":"10","masUnitQty":"20"}]},{"lineNo":0,"shipperId":"0021","goodsId":"0021","goodsName":"大学语文","barCode":"9787519117429","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"14","masUnitQty":"49"},{"equipmentId":"equipmentId","sortPortId":"16","masUnitQty":"39"}]},{"lineNo":0,"shipperId":"0000","goodsId":"0000","goodsName":"大学数学","barCode":"9787519117429","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"18","masUnitQty":"18"},{"equipmentId":"equipmentId","sortPortId":"20","masUnitQty":"21"}]}]}' select msg_id,order_no,order_type,order_level,line_no,shipper_id,goods_id,goods_name,barcode,equipment_id,sort_port_id,mas_unit_qty from openjson(@json51) with( msg_id nvarchar(500) '$.msgId',--strict表示json中必须包含该字段 order_no nvarchar(500) '$.orderNo', order_type nvarchar(500) '$.orderType', order_level nvarchar(500) '$.orderLevel', list nvarchar(max) '$.goodsList' AS JSON--必须是nvarchar(max) 类型 ) OUTER APPLY OPENJSON(list) WITH( line_no nvarchar(500) '$.lineNo', shipper_id nvarchar(500) '$.shipperId', goods_id nvarchar(500) '$.goodsId', goods_name nvarchar(500) '$.goodsName', barcode nvarchar(500) '$.barCode', twicelist nvarchar(max) '$.sortPortList' AS JSON--必须是nvarchar(max) 类型 ) OUTER APPLY OPENJSON(twicelist) WITH( equipment_id nvarchar(500) '$.equipmentId', sort_port_id nvarchar(500) '$.sortPortId', mas_unit_qty nvarchar(500) '$.masUnitQty' )
结果：
六、将表格式转化成json格式
转换出单个子json实例
--for json auto 安装原列名自动转化 --for json path 指定输出json的key名称,并可以控制输出字段 -- for json auto,WITHOUT_ARRAY_WRAPPER 跟在for json auto/path后可以删除中括号 select * from student for json auto select * from student for json auto,WITHOUT_ARRAY_WRAPPER select id as uniqueId, name, name as 'info.name' from student for json path
结果：
结果：1[{"id":1,"name":"ki","age":34,"create_time":"2020-09-01T11:12:43.230"},{"id":2,"name":"李四","age":33,"create_time":"2020-09-01T11:12:43.230"},{"id":3,"name":"ki","age":34,"create_time":"2020-09-01T11:12:50.987"},{"id":4,"name":"李四a","age":33,"create_time":"2020-09-01T11:12:50.987"}] 结果2：{"id":1,"name":"ki","age":34,"create_time":"2020-09-01T11:12:43.230"},{"id":2,"name":"李四","age":33,"create_time":"2020-09-01T11:12:43.230"},{"id":3,"name":"ki","age":34,"create_time":"2020-09-01T11:12:50.987"},{"id":4,"name":"李四a","age":33,"create_time":"2020-09-01T11:12:50.987"} 结果3：[{"uniqueId":1,"name":"ki","info":{"name":"ki"}},{"uniqueId":2,"name":"李四","info":{"name":"李四"}},{"uniqueId":3,"name":"ki","info":{"name":"ki"}},{"uniqueId":4,"name":"李四a","info":{"name":"李四a"}}]
上述方式虽然可以直接建立子json，但是对数组个数不明确的数据无法转换。这时可以使用关联表的方式建立，如果数据都在一张表中，需要使用自连接。通过别名确定json对象数的名称。
注：结尾必须是auto不能是path
另外，对于固定值如（'1' as taskFinishStatus 和 'pcs' as masureUnit,），写在哪一级json字段后面，生成json后就会在哪一级。
--json auto，结尾必须是auto不能是path DECLARE @json varchar(max) = '' set @json = (select distinct a.msg_id as msgId, a.order_no as orderNo, a.order_type as orderType, a.order_level as orderLevel, '1' as taskFinishStatus, goodsList.line_no as [lineNo], goodsList.shipper_id as shipperId, goodsList.goods_id as goodsId, goodsList.goods_name as goodsName, goodsList.barcode as barCode, sortPortList.equipment_id as equipmentId, sortPortList.sort_port_id as sortPortId, 'pcs' as masureUnit, sortPortList.mas_unit_qty_sorted as masUnitQty, '1' as minUnitQty from wms_to_wcs_task_ii as a join wms_to_wcs_task_ii as goodsList on a.msg_id=goodsList.msg_id join wms_to_wcs_task_ii as sortPortList on goodsList.goods_id=sortPortList.goods_id where a.msg_id='msgId111' for json auto,WITHOUT_ARRAY_WRAPPER) select @json
结果：
{"msgId":"msgId111","orderNo":"00000","orderType":"00000","orderLevel":"00000","taskFinishStatus":"1","goodsList":[{"lineNo":"0","shipperId":"0000","goodsId":"00232300","goodsName":"一年级数学","barCode":"9787107291029","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"10","masureUnit":"pcs","masUnitQty":0,"minUnitQty":"1"},{"equipmentId":"equipmentId","sortPortId":"8","masureUnit":"pcs","masUnitQty":0,"minUnitQty":"1"}]},{"lineNo":"0","shipperId":"0000","goodsId":"003300","goodsName":"大学数学","barCode":"9787519117429","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"18","masureUnit":"pcs","masUnitQty":0,"minUnitQty":"1"},{"equipmentId":"equipmentId","sortPortId":"20","masureUnit":"pcs","masUnitQty":21,"minUnitQty":"1"}]},{"lineNo":"0","shipperId":"0021","goodsId":"0023421","goodsName":"一年级语文","barCode":"9787107291029","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"4","masureUnit":"pcs","masUnitQty":0,"minUnitQty":"1"},{"equipmentId":"equipmentId","sortPortId":"6","masureUnit":"pcs","masUnitQty":0,"minUnitQty":"1"}]},{"lineNo":"0","shipperId":"0021","goodsId":"0033321","goodsName":"大学语文","barCode":"9787519117429","sortPortList":[{"equipmentId":"equipmentId","sortPortId":"14","masureUnit":"pcs","masUnitQty":0,"minUnitQty":"1"},{"equipmentId":"equipmentId","sortPortId":"16","masureUnit":"pcs","masUnitQty":0,"minUnitQty":"1"}]}]}
end、上方实例中使用到的表资源
student表数据：
/* Navicat Premium Data Transfer Source Server : localSqlServer Source Server Type : SQL Server Source Server Version : 14001000 Source Host : 127.0.0.1:1433 Source Catalog : custom Source Schema : dbo Target Server Type : SQL Server Target Server Version : 14001000 File Encoding : 65001 Date: 19/01/2021 17:03:20 */ -- ---------------------------- -- Table structure for student -- ---------------------------- IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[student]') AND type IN ('U')) DROP TABLE [dbo].[student] GO CREATE TABLE [dbo].[student] ( [id] int IDENTITY(1,1) NOT NULL, [name] varchar(5) COLLATE Chinese_PRC_CI_AS NULL, [age] int NULL, [create_time] datetime NULL ) GO ALTER TABLE [dbo].[student] SET (LOCK_ESCALATION = TABLE) GO -- ---------------------------- -- Records of student -- ---------------------------- SET IDENTITY_INSERT [dbo].[student] ON GO INSERT INTO [dbo].[student] ([id], [name], [age], [create_time]) VALUES (N'1', N'ki', N'34', N'2020-09-01 11:12:43.230') GO INSERT INTO [dbo].[student] ([id], [name], [age], [create_time]) VALUES (N'2', N'李四', N'33', N'2020-09-01 11:12:43.230') GO INSERT INTO [dbo].[student] ([id], [name], [age], [create_time]) VALUES (N'3', N'ki', N'34', N'2020-09-01 11:12:50.987') GO INSERT INTO [dbo].[student] ([id], [name], [age], [create_time]) VALUES (N'4', N'李四a', N'33', N'2020-09-01 11:12:50.987') GO SET IDENTITY_INSERT [dbo].[student] OFF GO -- ---------------------------- -- Auto increment value for student -- ---------------------------- DBCC CHECKIDENT ('[dbo].[student]', RESEED, 6) GO -- ---------------------------- -- Primary Key structure for table student -- ---------------------------- ALTER TABLE [dbo].[student] ADD CONSTRAINT [PK__student__3213E83F18AD2C2B] PRIMARY KEY CLUSTERED ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
wms_to_wcs_task_ii 表数据：
/* Navicat Premium Data Transfer Source Server : localSqlServer Source Server Type : SQL Server Source Server Version : 14001000 Source Host : 127.0.0.1:1433 Source Catalog : custom Source Schema : dbo Target Server Type : SQL Server Target Server Version : 14001000 File Encoding : 65001 Date: 09/03/2021 14:04:02 */ -- ---------------------------- -- Table structure for wms_to_wcs_task_ii -- ---------------------------- IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[wms_to_wcs_task_ii]') AND type IN ('U')) DROP TABLE [dbo].[wms_to_wcs_task_ii] GO CREATE TABLE [dbo].[wms_to_wcs_task_ii] ( [id] int IDENTITY(1,1) NOT NULL, [msg_id] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [order_no] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [order_type] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [order_level] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [line_no] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [shipper_id] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [goods_id] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [goods_name] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [barcode] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [equipment_id] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [sort_port_id] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [mas_unit_qty] int NULL, [mas_unit_qty_wcs_sorted] int DEFAULT ((0)) NULL, [mas_unit_qty_sorted] int DEFAULT ((0)) NULL, [creatime] datetime DEFAULT (getdate()) NULL, [update_time] datetime DEFAULT (getdate()) NULL ) GO ALTER TABLE [dbo].[wms_to_wcs_task_ii] SET (LOCK_ESCALATION = TABLE) GO -- ---------------------------- -- Records of wms_to_wcs_task_ii -- ---------------------------- SET IDENTITY_INSERT [dbo].[wms_to_wcs_task_ii] ON GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'1', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0021', N'0021', N'一年级语文', N'9787107291029', N'equipmentId', N'4', N'40', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'2', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0021', N'0021', N'一年级语文', N'9787107291029', N'equipmentId', N'6', N'30', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'3', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0000', N'0000', N'一年级数学', N'9787107291029', N'equipmentId', N'8', N'10', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'4', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0000', N'0000', N'一年级数学', N'9787107291029', N'equipmentId', N'10', N'20', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'5', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0021', N'0021', N'大学语文', N'9787519117429', N'equipmentId', N'14', N'49', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'6', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0021', N'0021', N'大学语文', N'9787519117429', N'equipmentId', N'16', N'39', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'7', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0000', N'0000', N'大学数学', N'9787519117429', N'equipmentId', N'18', N'18', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'8', N'msgId666', N'00000', N'00000', N'00000', N'0', N'0000', N'0000', N'大学数学', N'9787519117429', N'equipmentId', N'20', N'21', N'0', N'0', N'2021-01-25 01:33:37.673', N'2021-01-25 01:33:37.673') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'9', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0021', N'0023421', N'一年级语文', N'9787107291029', N'equipmentId', N'4', N'40', N'0', N'0', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'10', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0021', N'0023421', N'一年级语文', N'9787107291029', N'equipmentId', N'6', N'30', N'0', N'0', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'11', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0000', N'00232300', N'一年级数学', N'9787107291029', N'equipmentId', N'8', N'10', N'0', N'0', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'12', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0000', N'00232300', N'一年级数学', N'9787107291029', N'equipmentId', N'10', N'20', N'0', N'0', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'13', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0021', N'0033321', N'大学语文', N'9787519117429', N'equipmentId', N'14', N'49', N'0', N'0', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'14', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0021', N'0033321', N'大学语文', N'9787519117429', N'equipmentId', N'16', N'39', N'0', N'0', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'15', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0000', N'003300', N'大学数学', N'9787519117429', N'equipmentId', N'18', N'18', N'0', N'0', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO INSERT INTO [dbo].[wms_to_wcs_task_ii] ([id], [msg_id], [order_no], [order_type], [order_level], [line_no], [shipper_id], [goods_id], [goods_name], [barcode], [equipment_id], [sort_port_id], [mas_unit_qty], [mas_unit_qty_wcs_sorted], [mas_unit_qty_sorted], [creatime], [update_time]) VALUES (N'16', N'msgId111', N'00000', N'00000', N'00000', N'0', N'0000', N'003300', N'大学数学', N'9787519117429', N'equipmentId', N'20', N'21', N'0', N'21', N'2021-01-25 02:39:05.170', N'2021-01-25 02:39:05.170') GO SET IDENTITY_INSERT [dbo].[wms_to_wcs_task_ii] OFF GO -- ---------------------------- -- Auto increment value for wms_to_wcs_task_ii -- ---------------------------- DBCC CHECKIDENT ('[dbo].[wms_to_wcs_task_ii]', RESEED, 16) GO -- ---------------------------- -- Primary Key structure for table wms_to_wcs_task_ii -- ---------------------------- ALTER TABLE [dbo].[wms_to_wcs_task_ii] ADD CONSTRAINT [PK__wms_to_w__3213E83FCB118655] PRIMARY KEY CLUSTERED ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
就算这个世道烂成一堆粪坑，那也不是你吃屎的理由
