1、最近由于工作需要玩起来Sql,庆幸大学的时候数据库没逃课;
select *from table where table.t=1 order by desc ----查询单个表里面的数据
select *from table1,table2 where table1.id=table2.id -----查询多个表格需要的数据
2、实战
a. 需求1:查询两个表里面的数据,在WEB端的PM端发message给store.、affiliate。查看数据库里面的显示是否正确
select Message,Type,sms.CommunicationType.Id,MessageFrom,MessageTo,CreateDate
from sms.CommunicationType,sms.Communication //做笛卡尔积
where sms.CommunicationType.Id=sms.Communication.[Type] //因为type为关键字,所以需加[]
order by CreateDate desc //按时间降序排序
b. 需求2:由于web端页面中的数据过多,测试dashboard中的图形中难以判断统计数据是否正确,则使用数据库里面查询
按月统计各种状态的数量:
select callrequestnum,LocationId, CreateDate,(select count(*) from sms.Call where callstatus=0) as IncomingOpenCall,
(select count(*) from sms.Call where callstatus=1 and sms.Call.CreateDate >'2014-06-01') as InProgress, //查询inprogress状态在六月份之前的数量
(select count(*) from sms.Call where callstatus=2 and sms.Call.CreateDate >'2014-06-01') as ETAPending, //查询ETA状态在六月份之前的数量
(select count(*) from sms.Call where callstatus=3 and sms.Call.CreateDate >'2014-06-01') as Completed
from sms.Call where sms.Call.CreateDate >'2014-06-01'
order by CreateDate desc