sql语句

***********削除重复行**********************************************
select DISTINCT username from Student

************Between查询生日的sql，使用substring***************
select b.stuffname,a.corname,b.age,b.qq,b.email,b.mp from tempclient a ,tempclientstuff b where a.corno = b.corno and a.userid=1 and (substring(AGE,6,(6-4))='06' and substring(AGE,9,10) BETWEEN 19 and 40)

***********************查询信息，过滤空的字段********************
select mp,usernamecn,groupid from vMobileMsg WHERE (mp IS NOT NULL) and mp<>'' ORDER BY ordertag

****************************获取当前最大ID************************
 sqlStr="select (isnull(max(CORNO),0) +1) as CC from TEMPCLIENT";
 pdb.getQuery(sqlStr,1);
 if(pdb.next(1))
 {
      TClientNO=Integer.parseInt(pdb.getString("CC",1));
 }
 pdb.close();

*****************************统计百分比************************
*****物资
SELECT EquipmentClass, sum(isnull(shuliang,0)) as Num, convert(numeric(8,2),round(sum(OrValue),2))
as TOrValue, convert(numeric(8,2),round(sum(NowValue),2)) as TNowValue,
convert(numeric(8,2),round(((sum(NowValue)/100) * 100),2)) as NUMRate FROM TEquipment
GROUP BY EquipmentClass order by EquipmentClass

select isnull(sum(isnull(NowValue,0)),0)  as CCC from TEquipment

******商机
select a.busitypename ,count(b.busitypeid) as count,
convert(float,round(((count(b.busitypeid)/2.0) * 100),2)) as
percoent from Tbusinesstype a left join TOPP b on  a.busitypeid=b.busitypeid
group by  a.busitypename
**********************************************************************************
*****************客户列表的查询sql*************************
 String sql = "select top "+pagesize+" CORNO,CORNAME,customtypeid,IndyCodid,PHONE,ADDRESS,USERID from TEMPCLIENT where  (USERID in ("+curUid+") or CORNO in (select CORNO from TCORRIGHT where UserId in ("+curUid+")))and CORNO not in (select top "+pagesize*(currpage - 1)+" CORNO from TEMPCLIENT where  USERID in("+curUid+") or CORNO in (select CORNO from TEMPCLIENT where USERID in ("+curUid+")) order by CORNO  desc) order by CORNO desc";
*************SQL语句中使用substring截取字段****************************************
select b.stuffname,a.corname,b.age,b.qq,b.email,b.mp from tempclient a ,tempclientstuff b where a.corno = b.corno and a.userid=1 and substring(AGE,9,10)  = '04'

/**注释**/
substring(AGE,9,10)  = '04'  ----截取2007-03-25  第9位和第10位为日“25”，这和java的不同。他是从1开始计数的。
************************怎样截取sql server的datetime类型数据*****************************************
select convert(varchar(8),getdate(),112)   
--------    
 结果： 20021203  
   
  select   convert(varchar(10),getdate(),120)   
----------    
 结果： 2002-12-03  
-----------------------------------------------------------------
//更详细的操作
select count(*) from TEMPCLIENT

//按日期统计
select creatdt,count(*) as count,convert(float,round(((count(*)/10.0)* 100),1))
as percoent from TEMPCLIENT group by creatdt

//按月统计（实际应用）
select substring(convert(varchar(10),creatdt,120),1,7) as strdate,
count(*) as count,convert(float,round(((count(*)/10.0)* 100),1))
as percoent from TEMPCLIENT group by  substring(convert(varchar(10),creatdt,120),1,7)

//将datetime类型的数据转换成字符串后再截取 哈哈----"creatdt"是数据库中的字段，类型是datetime的，通过convert(varchar(10),creatdt,120)方法将其转成string类型，
//再用substring方法将其截取1-7，街区后的结果是"2007-06"
select substring(convert(varchar(10),creatdt,120),1,7) as strdate
from TEMPCLIENT group by creatdt

select datepart(yyyy,creatdt) as a,datepart(mm,creatdt) as b,count(*) as count,
convert(float,round(((count(*)/10.0)* 100),1)) as percoent from TEMPCLIENT
group by creatdt

//取日期的某一部分
datepart(yyyy,getdate())---获取当前日期的年
datepart(mm,getdate())---获取当前日期的月
datepart(dd,creatdt) ---获取当前日期的日
select datepart(mm,creatdt) from TEMPCLIENT group by creatdt

************************************--修改表，添加字段***********************
alter table tempclientstuff add timetask varchar(14)
********************************************************************************

**************查SQL server当前的链接数（不过还不是很准确，不知到还有没有更好的方法）*************
SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses

select login_time from master..sysprocesses

select @@CONNECTIONS

select @@MAX_CONNECTIONS

SP_WHO 'active' //当前活动的sql用户

SP_WHO 'sa'

*****使用函数***********
//select @@CONNECTIONS