科脉御商软件常用语句

from bi_t_item_cls,bi_t_item_i
where bi_t_item_cls.item_clsid = bi_t_item_i.item_clsid
--查询‘bi_t_item_cls’表中‘item_clsname字段’ 和‘bi_t_item_i’表中‘item_name ’字段
update bi_T_item_e set sx_flag='N' (sx_flag=生鲜称重标志,N=普通,Z=称重,J=计件)
---更改商品属性（称重标志）
select *from bi_t_item_e_b where brhc = '0008' and po_mode != 1
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_406', '1', '3', '0000000000' )
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_409', '1', '3', '0000000000' )
drop table asd
--删除‘asd’表
select top 20 *from A
--查询‘A’表中前20列数据
alter table A disable trigger all
alter table A enable trigger all
--关闭，打开A表触发器
*****************************************************************************************
UPDATE sys_t_func_branch SET fValue = '1111111110' WHERE fFuncId = 'm_13' AND fType = '1' AND fObject = '3'
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_sh', '1', '3', '0000000000' )
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_casher_sheet', '1', '3', '0000000000' )
select distinct
select *from pos_t_daysum_pre where up_flag=2
--查询分店上传数据情况，
在分店数据库执行（flag=0:未上传数据 flag=2:已上传数据）
update A set a=1 where b in(select top 20 b from A)
--将A表中 a列前20行全部改为1 并以b列作为筛选条件（b列必须为所有行唯一标识符，即无重复，是唯一查询条件；如不加入该条件，则在执行时会影响表中所有包含在前20列的 数据：比如：前20列中第3列=3，执行后20列外所有=3的数值都会被1替换掉！）
---修改门店交换数据后，后台操作员无法修改密码问题的SQL语句
*****************************************************************************************
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_430', '1', '3', '0000000000' )
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_403', '1', '3', '0000000000' )
UPDATE sys_t_func_branch SET fValue = '1111111110' WHERE fFuncId = 'm_266' AND fType = '1' AND fObject = '3'
UPDATE sys_t_func_branch SET fValue = '1111111110' WHERE fFuncId = 'm_336' AND fType = '1' AND fObject = '3'
***************************************************************************************
select a.item_no,a.item_subno,a.item_name,b.sale_price from bi_T_item_i a,bi_T_item_price b
--查询 ‘bi_t_item_i’表中‘item_name,item_subno,unit_no’字段，其中‘item_subno’字段长度=5
select * from bi_t_item_i where right(item_subno,3)='236'
--查询‘bi_t_item_i’表中‘item_subno’字段从右边数3位等于236的数字 （从字段左边开始查询同理）
--查询‘bi_t_item_i ’表中‘item_subno 字段’='020349'和'041689207113'的数据（字段中具体数据）
select * into asd from bi_t_item_i
--查询出‘bi_t_item_i’表中所有数据 并写入‘asd’这张临时表中
where a.item_id = b.item_id and b.branch_id = '7' and len(a.item_subno) = '5'
union all
select item_no,item_subno,item_name,sale_price from bi_T_item_i where item_id not in (select a.item_id from bi_T_item_i a,bi_T_item_price b
UPDATE sys_t_func_branch SET fValue = '1111111110' WHERE fFuncId = 'm_272' AND fType = '1' AND fObject = '3'
UPDATE sys_t_func_branch SET fValue = '1111111110' WHERE fFuncId = 'm_273' AND fType = '1' AND fObject = '3'
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_402', '1', '3', '0000000000' )
INSERT INTO sys_t_func_branch ( fFuncId, fType, fObject, fValue ) VALUES ( 'm_cgfh', '1', '3', '0000000000' )
select substring(item_subno,2,3) from bi_t_item_i
--查询‘bi_t_item_i’表中‘item_subno’字段的从第二位开始，长度为3的数值
select bi_t_item_cls.item_clsname, bi_t_item_i.item_name