sql语句基本用法

//单位表
select *from unit




//主表
select*from ORDERIDX where billid=6
//供应商表
select* from clients
//仓库表
select *from storages
//职员表
select *from employees
//部门表
select * from department


//商品表
select * from products


//通泰实业有限公司食品视图
select *from VW_S_Products
// 从表
select*from ORDERBILL where smb_id=24


//草稿订单查询视图
select *from  VW_X_BillDraftIDX
//草稿订单表
select *from billdraftidx


//资产表
select *from account


//属于从表订单分别为主表序号1和2的从表内容
select *from buymanageBilldrf
//不加密解析
exec sp_helptext Ts_b_InsertBillDetailDraft
//加密解析
exec sp_decrypt Ts_b_InsertBillDetailDraft




//主表左连接基础列表
select * from (


select m.*,c.name ClientName,emp.name EmpName,dep.serial_number DepNumber,s.name StorageName
From ORDERIDX m
left join clients c on m.c_id=c.client_id
left join employees emp on m.e_id=emp.emp_id
left join department dep on m.department_id=dep.departmentId
left join storages s on m.sin_id=s.storage_id


where billid=6


)asdf




//从表连接基础列表
select *from(
select a.*,b.name Fool,c.name Unit 
From ORDERBILL a


left join products b on a.p_id=b.PRODUCT_ID
left join unit c on a.unitid=c.unit_id


where smb_id=12
)asdf


//草稿订单从表左连接
select*from(
select a.bill_id,b.name Fool,c.name Unit,a.quantity,a.totalmoney
From buymanageBilldrf a
left join products b on a.p_id=b.PRODUCT_ID
left join unit c on a.unitid=c.unit_id




)a


//草稿订单主表连接基础列表
select *from(
select m.billid 序列号,m.billnumber 订单号,c.name ClientName,emp.name EmpName,dep.serial_number DepNumber,s.name StorageName
From billdraftidx m
left join clients c on m.c_id=c.client_id
left join employees emp on m.e_id=emp.emp_id
left join department dep on m.department_id=dep.departmentId
left join storages s on m.sin_id=s.storage_id
where billid=1
)s




//查询10到15行数据
select   b.*   from 
( 
select   top   5   a.*   from   
( 
select   top   10   *   from   products   order   by  product_id   asc 
)   a   order   by   a.product_id   desc 
) b
order   by   b.product_id   




//查询最后一行
select top 1 * from billdraftidx order by billid desc


//查出某个字段某个值
select * from billdraftidx where billnumber like '%05%'


//查出某个字段最大值+1

select isnull(max(Replace(billnumber,'CG-2015-12-12-','')),0)+1 from billdraftidx where billnumber like 'CG-2015-12-12-____'