-- н¨Êý¾Ý¿â create database WMS_V01 go use WMS_V01 delete from SysRoleRight where RoleNo = '1000' select * from SysMaterials where mate.IsDel = '0' and Id = 6 select * from SysRoleRight where Isdel = '0' and RoleNo = (select RoleNo from SysRoles where IsDel = '0' and Id = 7) -- н¨SysUserInfor (Óû§±í) create table SysUserInfor ( Id int primary key identity, -- Ö÷¼üID ×ÔÔö UserNo varchar(20) not null, -- Óû§ºÅ UserName nvarchar(20) not null, -- µÇ¼Ãû³Æ RealName nvarchar(20) not null, -- ÕæʵÐÕÃû PassWord varchar(50) not null, -- ÃÜÂë MD5¼ÓÃÜ JobNo varchar(20), -- Ö°¹¤ºÅ Status varchar(3) default(0), -- ״̬ 0£ºÕý³£ 1£ºÍ£Óà 2£ºÀëÖ° Sex varchar(10), -- ÐÔ±ð Nationality nvarchar(20), -- ¹ú¼® Paper varchar(20), -- Ö¤¼þºÅ Phone varchar(11), -- ÊÖ»úºÅ Email varchar(50), -- ÓÊÏä Addres nvarchar(100), -- µØÖ· LoginTime dateTime, -- ×îºóµÇ¼ʱ¼ä SetPasswordTime dateTime, -- ÃÜÂëÉèÖÃʱ¼ä ÓÃÒÔÉèÖÃ30Ì쳬ʱ ¹¦ÄÜÉ趨 RoleNo varchar(20), -- ½ÇÉ«ºÅ DepartmentNo varchar(20), -- ²¿ÃźŠDemo nvarchar(100), -- ±¸×¢ IsDel varchar(3) not null default(0), -- ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), -- ´´½¨ÈÕÆÚ CreateUser int not null, -- ´´½¨ÈË UpdateTime dateTime, -- ¸üÐÂÈÕÆÚ UpdateUser int -- ¸üÐÂÈË ); select * from SysStorageLocat where IsDel = '0' and Layer = 1 and WareHouseNo = 'W01' and Row >= 1 and Row <= 10 and Column >= 1 and Column <= 10 -- н¨SysRoles (½ÇÉ«±í) create table SysRoles ( Id int primary key identity, -- Ö÷¼üID ×ÔÔö RoleNo varchar(20) not null, -- ½ÇÉ«ºÅ RoleName nvarchar(20) not null, -- ½ÇÉ«Ãû³Æ Demo nvarchar(100), -- ±¸×¢ IsDel varchar(3) not null default(0), -- ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), -- ´´½¨ÈÕÆÚ CreateUser int not null, -- ´´½¨ÈË UpdateTime dateTime, -- ¸üÐÂÈÕÆÚ UpdateUser int -- ¸üÐÂÈË ) select * from SysStorageLocat where IsDel = '0' and row = '1' and WareHouseNo = 'w01' order by [Layer],[column] select * from SysStorageLocat where IsDel = '0' and row = '1' and WareHouseNo = 'w03' order by [Layer],[Column] select Depth from SysStorageLocat where IsDel = '0' and WareHouseNo = 'w03' group by Depth order by Depth select TOP 1 WareHouseNo from SysWareHouse -- н¨SysRoleRight (½ÇɫȨÏÞ±í) create table SysRoleRight ( Id int primary key identity, -- Ö÷¼üID ×ÔÔö RoleNo varchar(20) not null, -- ½ÇÉ«ºÅ MenuNo varchar(20) not null, -- ²Ëµ¥ºÅ IsDel varchar(3) not null default(0), -- ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), -- ´´½¨ÈÕÆÚ CreateUser int not null, -- ´´½¨ÈË UpdateTime dateTime, -- ¸üÐÂÈÕÆÚ UpdateUser int -- ¸üÐÂÈË ) select * from SysFunctionMenu where IsDel = '0' or ParentNo = '' select * from SysRoleRight select userinfo.UserNo,userinfo.UserName,userinfo.RealName,userinfo.Status,userinfo.Sex,userinfo.Nationality,userinfo.Paper,userinfo.Phone,userinfo.Email,userinfo.Addres,userinfo.LoginTime,userinfo.SetPasswordTime,role.RoleNo,role.RoleName,menu.MenuNo,menu.MenuName,menu.ParentNo,menu.Ord,menu.Url,menu.level from SysUserInfor userinfo join SysRoles role on userinfo.RoleNo = role.RoleNo join SysRoleRight roleright on roleright.RoleNo = role.RoleNo join SysFunctionMenu menu on roleright.MenuNo = menu.MenuNo where role.Id = 7 select * from SysFunctionMenu where IsDel = '0' and ParentNo in (2100,2200,2300,2400,2500,2600) select * from SysRoleRight WHERE RoleNo = '1000' select * from SysRoleRight roleright left join SysRoles role on roleright.RoleNo = role.RoleNo where role.Id = 7 @RoleNo, @MenuNo, @IsDel, @CreateTime, @CreateUser, null, null insert into SysRoleRight values('admin','1000','0','',1,'',0) insert into SysRoleRight values('admin','1100','0','',1,'',0) insert into SysRoleRight values('admin','1200','0','',1,'',0) insert into SysRoleRight values('admin','1300','0','',1,'',0) insert into SysRoleRight values('admin','1400','0','',1,'',0) insert into SysRoleRight values('admin','2000','0','',1,'',0) insert into SysRoleRight values('admin','2100','0','',1,'',0) insert into SysRoleRight values('admin','2101','0','',1,'',0) insert into SysRoleRight values('admin','2200','0','',1,'',0) insert into SysRoleRight values('admin','2300','0','',1,'',0) insert into SysRoleRight values('admin','2400','0','',1,'',0) insert into SysRoleRight values('admin','2500','0','',1,'',0) insert into SysRoleRight values('admin','2600','0','',1,'',0) select* from SysRoleRight insert into SysRoleRight values('admin','2301','0','',1,'',0) insert into SysRoleRight values('admin','2302','0','',1,'',0) select * from SysFunctionMenu where IsDel = '0'and MenuName like '%ÉèÖÃ%' or ParentNo like '%20%' or MenuNo like '20%' select * from SysFunctionMenu where IsDel = '0' or ParentNo like '30%' or MenuNo like '30%' -- н¨SysFunctionMenu (¹¦Äܲ˵¥±í) create table SysFunctionMenu ( Id int primary key identity, -- Ö÷¼üID ×ÔÔö MenuNo varchar(20) not null, -- ²Ëµ¥ºÅ MenuName nvarchar(20), -- Ãû³Æ ParentNo varchar(20), -- ¸¸¼¶²Ëµ¥ºÅ Ord varchar(5), -- ÏÔʾ˳Ðò Url varchar(100), -- Ò³ÃæµØÖ· level varchar(3) not null, -- ²ã¼¶ 0£ºÄ£¿é 1£ºÒ³Ãæ 2£º°´Å¥ IsEnable varchar(3) not null default(0), -- ÊÇ·ñ¿ÉÓà 0£º¿ÉÓà 1£º²»¿ÉÓã¨ÏîÄ¿¿´²»µ½µÄÒ³ÃæÒþ²Øµô£© Demo nvarchar(100), -- ±¸×¢ IsDel varchar(3) not null default(0), -- ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), -- ´´½¨ÈÕÆÚ CreateUser int not null, -- ´´½¨ÈË UpdateTime dateTime, -- ¸üÐÂÈÕÆÚ UpdateUser int -- ¸üÐÂÈË ) select * from SysFunctionMenu where IsDel = '3' and IsEnable = '0' select * from SysFunctionMenu where IsDel = '0' @menuno,@menuname,@parentno,@ord,@url,@level,@isenable,@demo,@isdel,@createtime,@createuser,@updatetime,@updateuser MenuName = @menuname, ParentNo = @parentno, Ord = @ord, Url = @url, Level = @level, IsEnable = @isenable, Demo = @demo, UpdateTime = @updatetime, UpdateUser = @updateuser insert into SysFunctionMenu values('1000','É豸¹ÜÀí','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('1100','²Ö¿â¹ÜÀí','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('1200','ÇøÓò¹ÜÀí','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('1300','ÈÎÎñÒì³£¹ÜÀí','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('1400','µ¥»úͬ²½','1000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2000','ϵͳÉèÖÃ','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2100','²Ëµ¥¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2101','Ìí¼Ó²Ëµ¥','2100','','','2','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2200','Óû§¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2300','ÌõÂë¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2400','½ÇÉ«¹ÜÀí','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2500','Êý¾Ý×Öµä','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('2600','²Ù×÷ÈÕÖ¾','2000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3000','²Ö¿âÉèÖÃ','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3100','²Ö¿â¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3200','ÇøÓò¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3300','ÏïµÀ¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3400','´¢Î»¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3500','Òì³£´¦Àí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3600','ÌõÂë¹ÜÀí','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3700','´¢Î»Í¼Àý','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('3800','²Ù×÷ÈÕÖ¾','3000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('4000','»ù´¡ÐÅÏ¢','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('4100','ÎïÁϹÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('4200','°ü×°¹ÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('4300','¼ÆÁ¿µ¥Î»','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('4400','¿Í»§¹ÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('4500','ÎïÁ÷¹ÜÀí','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('4600','²Ù×÷ÈÕÖ¾','4000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('5000','ÎïÁ÷¹ÜÀí','','','','0','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('5600','²Ù×÷ÈÕÖ¾','5000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('5500','ÎïÁ÷¹ÜÀí','5000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) insert into SysFunctionMenu values('5600','²Ù×÷ÈÕÖ¾','5000','','','1','0','²âÊÔÊý¾Ý','0','',1,'',0) update SysFunctionMenu set MenuName = '½ÇɫȨÏÞ' where MenuNo = '2300' update SysFunctionMenu set MenuName = '¹¦ÄÜÉ趨' where MenuNo = '2400' select * from SysFunctionMenu where IsDel = '0' delete from SysFunctionMenu where id in (58) update SysFunctionMenu set Url = 'SystemSettings/Menumanagement.html' where MenuNo = '2100' update SysFunctionMenu set Url = 'SystemSettings/UserManage.html',upda select * from SysRoleRight where Isdel = '0' -- н¨SysDepartment (²¿Ãűí) create table SysDepartment ( Id int primary key identity, --Ö÷¼üID ×ÔÔö DepartmentNo varchar(20) not null, --²¿ÃźŠDepartmentName nvarchar(20) not null, --²¿ÃÅÃû³Æ Demo nvarchar(100), --±¸×¢ IsDel varchar(3) not null default(0), --ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate(Now())), --´´½¨ÈÕÆÚ CreateUser int not null, --´´½¨ÈË UpdateTime dateTime, --¸üÐÂÈÕÆÚ UpdateUser int --¸üÐÂÈË ) select dep.*,user1.UserName CreateUserName from SysDepartment dep left join SysUserInfor user1 on dep.CreateUser = user1.id where dep.IsDel = '0' select * from SysDepartment where IsDel = '0' select * from SysDepartment update SysDepartment set DepartmentNo = '@departmentno',DepartmentName='@departmentname',Demo = '@demo', IsDel = '@isdel',UpdateTime = '@updatetime',UpdateUser = updateuser where id = @id insert into SysDepartment values('DepartmentNo','DepartmentName','Demo','IsDel','CreateTime',CreateUser,'UpdateTime',UpdateUser) Fun001 -- н¨SysFunSetting (¹¦ÄÜÉ趨±í) create table SysFunSetting ( Id int primary key identity, --Ö÷¼üID ×ÔÔö FunSetNo varchar(20) not null, --¹¦ÄÜÉèÖñàºÅ FunSetName nvarchar(20), --¹¦ÄÜÃû³Æ SetValue nvarchar(20), --É趨ֵ FunText nvarchar(100), --¹¦ÄÜÃèÊö IsEnable varchar(3) not null, --ÊÇ·ñ¿ªÆô NO:¿ªÆô OFF:¹Ø±Õ Ord varchar(5), --ÏÔʾ˳Ðò GroupNo varchar(20), --×éºÅ ͬһ×éÖ»¿É¿ªÆôÒ»¸ö¹¦ÄÜ¡£ ×éºÅµÈÓÚ×îСµÄ¹¦ÄܱàºÅ£ºÀý:Fun001 Fun002 Fun003 ×éºÅΪ:Fun001 IsDel varchar(3) not null default(0), --ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), --´´½¨ÈÕÆÚ CreateUser int not null, --´´½¨ÈË UpdateTime dateTime, --¸üÐÂÈÕÆÚ UpdateUser int --¸üÐÂÈË ) select * from SysFunctionMenu order by MenuNo -- н¨SysDictionary (Êý¾Ý×Öµä±í) create table SysDictionary ( Id int primary key identity, --Ö÷¼üID ×ÔÔö DictNo varchar(20), --±àºÅ DictName nvarchar(20), --×ÖµäÃû³Æ ParentNo varchar(20), --¸¸¼¶×ÖµäºÅ Ord varchar(5), --ÏÔʾ˳Ðò Level varchar(3) not null, --²ã¼¶ 0£º¸ùĿ¼ 1£ºÏ¼¶Ä¿Â¼ IsPublic varchar(3), --ÊÇ·ñ¹«¿ª ÊÇ·ñÈÃÓû§¿´µ½ 0£º¹«¿ª 1£º²»¹«¿ª IsEdit varchar(3), --ÔÊÐí±à¼­ ÊÇ·ñÔÊÐíÓû§±à¼­ 0£ºÔÊÐí 1£º²»ÔÊÐí IsAdd varchar(3), --ÔÊÐíÔö¼Ó ÊÇ·ñÔÊÐíÓû§ÐÂÔö 0£ºÔÊÐí 1£º²»ÔÊÐí IsDel varchar(3) not null default(0), --ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), --´´½¨ÈÕÆÚ CreateUser int not null, --´´½¨ÈË UpdateTime dateTime, --¸üÐÂÈÕÆÚ UpdateUser int --¸üÐÂÈË ) update SysDictionary set IsDel = @de,UpdateTime = @upttime,UpdateUser = @uptuser where id = @dicid ,@de,@upttime datetime,@uptuser int,@dicid int',@de='1',@upttime='2023-03-31 11:26:48.373,@uptuser=1,@dicid=6 select * from SysDictionary insert into SysDictionary values('1000','²âÊÔ','','','0','0','0','0','0',getdate(),1,null,null) insert into SysDictionary values('@dictno','@DictName','@parentno','@ord','@level','@ispublic','@isedit','@isadd','@isdel','@createtime',@createuser,'@updatetime',@updateuser) update SysDictionary set IsDel = '0',UpdateTime = '',UpdateUser = 1 where id = 11 update SysDictionary set DictNo = '@dictno',DictName='@dictname',ParentNo='@parentno',Ord='@ord',Level='@level',IsPublic='@ispublic',IsEdit='@isedit',IsAdd='@isadd',IsDel = '@isdel',UpdateTime = '@updatetime',UpdateUser = updateuser where id = @id select dic.*,user1.UserName CreateName from SysDictionary dic left join SysUserInfor user1 on dic.CreateUser = user1.Id select dic.*,user1.UserName CreateName from SysDictionary dic left join SysUserInfor user1 on dic.CreateUser = user1.Id where dic.IsDel = '0' select * from SysDictionary -- н¨SysMaterials (ÎïÁϱàÂë±í) create table SysMaterials ( Id int primary key identity, --Ö÷¼üID ×ÔÔö SkuNo varchar(20) not null, --ÎïÁϺŠSkuName nvarchar(20) not null, --ÎïÁÏÃû³Æ Standard nvarchar(50), --¹æ¸ñ AuditStatusNo varchar(2), --ÉóºË״̬ 0:´ýÉóºË 9:ÒÑͨ¹ý£»1-8Óû§¿É×Ô¶¨Òå Type varchar(20), --ÀàÐÍ 0£ºÔ­ÁÏ 1£º°ü²Ä 2£º³ÉÆ· 3£ººÄ²Ä 4£º°ë³ÉÆ· IsControlled varchar(3) default(1), --ÊÇ·ñÊÜ¿Ø 0£ºÊÜ¿Ø 1£º·ÇÊÜ¿Ø IsInspect varchar(3) default(0), --ÊÇ·ñÃâ¼ì 0£º·ñ 1£ºÊÇ Origin nvarchar(20), --À´Ô´ UnitNo varchar(20), --µ¥Î» PackagNo varchar(20), --°ü×° Environment varchar(20), --´æ´¢»·¾³ 0£º³£Î 1£ºµÍΠ3£º¸ÉÔïͨ·ç Weight decimal(6,1), --ÀíÂÛÖØÁ¿ Warranty int, --±£ÖÊÆÚ Price decimal(8,2), --ÀíÂÛµ¥¼Û Demo nvarchar(100), --±¸×¢ IsDel varchar(3) not null default(0), --ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), --´´½¨ÈÕÆÚ CreateUser int not null, --´´½¨ÈË UpdateTime dateTime, --¸üÐÂÈÕÆÚ UpdateUser int --¸üÐÂÈË ) select * from SysPackag update SysPackag set PackagName = 'ºÐ' where id = 2 update SysMaterials set Origin= 'ERP' where id = 1 select mate.*,user1.UserName CreateUserName from SysMaterials mate left join SysUserInfor user1 on mate.CreateUser = user1.Id insert into SysMaterials values('1000','²âÊÔÏÔʾ','0.0','0','0','1','0','text','text','text','0',1.0,30,20.00,'²âÊÔÊý¾Ý','0',GETDATE(),1,'',0) update SysMaterials set IsDel = @isdel, UpdateTime = @updatetime, UpdateUser = @updateuser where Id = @id select menu1.Id,menu1.MenuNo,menu1.MenuName,menu2.MenuName ParentName,menu1.ParentNo,menu1.Ord,menu1.Url,menu1.level,menu1.IsEnable,menu1.Demo,menu1.IsDel,menu1.CreateTime,menu1.CreateUser,menu1.UpdateTime,menu1.UpdateUser,user1.UserName CreateUserName from SysFunctionMenu menu1 left join sysfunctionmenu menu2 on menu1.parentNo = menu2.MenuNo join SysUserInfor user1 on menu1.CreateUser = user1.id where menu1.IsDel = '0' and menu1.IsEnable = '0' and menu1.MenuName like '%ɾ³ý%' and menu1.ParentNo = '2000' or menu1.ParentNo in (select MenuNo from SysFunctionMenu where ParentNo = '2000') and menu1.MenuName like '%ɾ³ý%' select * from SysFunctionMenu where isdel = '0' -- н¨SysPackag (°ü×°±í) create table SysPackag ( Id int primary key identity, --Ö÷¼üID ×ÔÔö PackagNo varchar(20) not null, -- °ü×°ºÅ PackagName nvarchar(20), -- °ü×°Ãû³Æ Level int, -- °ü×°¼¶Êý ·¶Î§ 2-5 L1Num int, -- Ò»¼¶°ü×°-ÊýÁ¿ L1Name nvarchar(20), --Ò»¼¶°ü×°-ÃèÊö L2Num int, --¶þ¼¶°ü×°-ÊýÁ¿ L2Name nvarchar(20), --¶þ¼¶°ü×°-ÃèÊö L3Num int, --Èý¼¶°ü×°-ÊýÁ¿ L3Name nvarchar(20), --Èý¼¶°ü×°-ÃèÊö L4Num int, --Ëļ¶°ü×°-ÊýÁ¿ L4Name nvarchar(20), --Ëļ¶°ü×°-ÃèÊö L5Num int, --ÆäËû°ü×°-ÊýÁ¿ L5Name nvarchar(20), --ÆäËû°ü×°-ÃèÊö IsDel varchar(3) not null default(0), --ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), --´´½¨ÈÕÆÚ CreateUser int not null, --´´½¨ÈË UpdateTime dateTime, --¸üÐÂÈÕÆÚ UpdateUser int --¸üÐÂÈË ) select mate.*,user1.RealName CreateUserName,pack.PackagName PackagName,unit.UnitName UnitName from SysMaterials mate left join SysUserInfor user1 on mate.CreateUser = user1.Id left join SysPackag pack on mate.PackagNo = pack.PackagNo left join SysUnit unit on mate.UnitNo = unit.UnitNo where mate.IsDel = '0' select * from SysPackag -- н¨SysUnit (¼ÆÁ¿µ¥Î») create table SysUnit ( Id int primary key identity, --Ö÷¼üID ×ÔÔö UnitNo varchar(20) not null, --µ¥Î»±àºÅ UnitName nvarchar(20), --µ¥Î»Ãû³Æ Abbrev varchar(3), --Ëõд Kg t IsDel varchar(3) not null default(0), --ÊÇ·ñɾ³ý 0:Õý³£ 1:ÒÑɾ³ý CreateTime dateTime not null default(getdate()), --´´½¨ÈÕÆÚ CreateUser int not null, --´´½¨ÈË UpdateTime dateTime, --¸üÐÂÈÕÆÚ UpdateUser int --¸üÐÂÈË ) create table TableName ( Id int primary key identity not null, --Ö÷¼üID ×ÔÔö ExceptionNo varchar(20), --Òì³£ºÅ Type varchar(3), --Òì³£ÀàÐÍ PalletNo varchar(20), -- ÍÐÅ̺ŠExcLocatNo varchar(20), --Òì³£´¢Î» OrderNo varchar(20), --¹ØÁªµ¥ºÅ TaskNo varchar(20), --¹ØÁªÈÎÎñºÅ Status varchar(3), --״̬ Text nvarchar(100), --´¦ÀíÃèÊö Demo nvarchar(100), --±¸×¢ IsDel varchar(3) not null default('0'), --ÊÇ·ñɾ³ý CreateTime dateTime not null default(GETDATE()), --´´½¨ÈÕÆÚ CreateUser int not null, --´´½¨ÈË UpdateTime dateTime, --¸üÐÂÈÕÆÚ UpdateUser int --¸üÐÂÈË )