sql学习记录

SQL语句 记录！

查找同个值：=’ 某个值’

查找存在于、包含值 ：like ‘%某个值%’

Distinct 查找不同的值 重复的只显示一次

Group by 列名 把某列分组

Between 存在值1 与 值2之间的所有值： between ‘值1’and ‘值2’

Exists 存在

Not exists 不存在

最大值：max 最小值：min 汇总 总和：sum 计算：count 平均：avg

select * from u_store_m a where a.busno='001' and not exists(select * from u_store_m b where a.wareid=b.wareid and b.busno='002')

例：

select * from u_ware where waresimname like '%阿莫%' and producer like'%白云%' and maxunit = '胶囊剂'

查找商品资料 商品名包含阿莫 产地包含白云 剂型等于胶囊剂

ORDER BY ：排序！ Desc 从大到小

truncate table 表名

删除 delete 表名

convert(varchar(10),a.createtime（列名）,120) =‘某天’ 更改变量 查找某天

判断 当满足某个条件

case when 条件 then 值1

when 条件2 then 值2

else

end

select a.bankcardno as 编码,a.cardbalance as 基本信息余额,b.saleamount as 消费金额,b.cardbalance as 余额 from u_bankcard_base a , u_bankcard_sale b where

a.bankcardno=b.bankcardno

and

convert(varchar(10),b.createtime,120)='2011-05-26'

and a.cardbalance<>b.cardbalance

select d.wareid as 商品编码 ,d.warename as 商品名称 ,d.warespec as 商品规格 ,d.producer as 生产企业 ,a.sumqty as 库存数量 ,d.purstatus as 采购状态 ,c.classcode as 类别编码,

c.classname as 类别名称

from u_store_m a ,v_ware_class c , u_ware_q d where a.wareid=c.wareid

and sumqty>0 and a.busno='001'

and not exists (select * from u_store_m b where a.wareid=b.wareid and b.busno='1015')

and a.wareid=d.wareid

and parentcode='29'

and classcode='10' --

and d.purstatus='1'

b.busno=dbo.f_sys_ini('1001')) 代表就是取当前业务机构编码。

create view v_wjy

as select a.busno,d.wareid as 商品编码 ,d.warename as 商品名

称 ,d.warespec as 商品规格 ,d.producer as 生产企业 ,a.sumqty as 库存数量 ,d.purstatus as 采购状态

from u_store_m a ,u_ware_q d where

sumqty>0 and a.busno='001'

and not exists (select * from b.busno=dbo.f_sys_ini('1001'))

and a.wareid=d.wareid

and d.purstatus='1'

where a.wareid=b.wareid and u_store_m b