查询当天或者前一天的数据
select * form table where date between DATEADD(DAY,-1,GETDATE()) AND GETDATE();
查范围之内
用between…and…
select * form goods where price between 1000 and 3000
不在范围之内
用not…between…and
select * form goods where price between 1000 and 3000
模糊查询
查出含有mr字符的数据
where 列名 Like '%mr%'
查询出以mr字符开头的数据
where 列名 Like 'mr%'
查询出以mr字符结尾的数据
where 列名 Like '%mr'
查询出某列前两个字符为mr,后一个字符为随意字符的数据
where 列名 Like 'mr_'
查询出某列前一个为任意字符,后两个字符为mr的数据
where 列名 Like '_mr'
查询出某列中不是以a~e之间的字符开头的数据
where 列名 Like '[^a-e]%'
-------------------------------------------------------------2019-10-22-------------------------------------------------------------------
SQL使用in查询按条件顺序返回
--根据In条件顺序排序
Sqlserver:
SELECT id,OrderNo FROM T_Express WHERE OrderNo in
('4997204024991853704','4885613398959024576','5581738235205824010',
'5562763472934261354','5505736706596514515','4897808779901361047','4805840510538887848')
ORDER BY
charindex (OrderNo,'4997204024991853704,4885613398959024576,5581738235205824010,
5562763472934261354,5505736706596514515,4897808779901361047,4805840510538887848')
Oracle
SELECT id,OrderNo FROM T_Express WHERE OrderNo in
('4997204024991853704','4885613398959024576','5581738235205824010',
'5562763472934261354','5505736706596514515','4897808779901361047','4805840510538887848')
ORDER BY
"decode" (OrderNo,'4997204024991853704,4885613398959024576,5581738235205824010,
5562763472934261354,5505736706596514515,4897808779901361047,4805840510538887848')
MYSQL
SELECT id,OrderNo FROM T_Express WHERE OrderNo in
('4997204024991853704','4885613398959024576','5581738235205824010',
'5562763472934261354','5505736706596514515','4897808779901361047','4805840510538887848')
ORDER BY
FIND_IN_SET (OrderNo,'4997204024991853704,4885613398959024576,5581738235205824010,
5562763472934261354,5505736706596514515,4897808779901361047,4805840510538887848')
结果:
-------------------------------------------------------------2019-12-31-------------------------------------------------------------------
把省市区 详细地址合并 以及去掉不必要的双引号的查询
SELECT p.TelNumber,convert(varchar(10),a.Province) + '_' + convert(varchar(10),a.City)+ '_' + convert(varchar(10),a.District)+ '_' + convert(varchar(100),replace(a.DetailAddress, '"', ''))
FROM T_Shop_Order so
left join T_Shop_OrderInfo info on so.Id=info.shop_orderId
left join T_Address a on a.Id=so.rec_AddressID
inner join T_Person p on p.Id=a.PersonID
-------------------------------------------------------------2021-06-09-------------------------------------------------------------------
查多个字段重复 添加where条件要在groupby前
SELECT r.Code,r.Name,r.ShortName,MIN(r.CreateDate) as CreateDate
FROM BusiTradeMain b
JOIN BaseReceiverMain r on r.Code=b.CustomerCode and r.CompanyCode='hsdz'
GROUP BY r.Code,r.Name,r.ShortName
查groupby 后 查询数据的条数
select count(1) from (SELECT r.Code,r.Name,r.ShortName,MIN(r.CreateDate) as CreateDate
FROM BusiTradeMain b
JOIN BaseReceiverMain r on r.Code=b.CustomerCode and r.CompanyCode='hsdz'
GROUP BY r.Code,r.Name,r.ShortName) a