简单的SQL语句！！

大概几天将要过生日的人

select customername   from customer   where datepart(month,getdate())=datepart(month,birthday) and
datepart(day,getdate()+2)=datepart(day,birthday)

光取年月日

select CONVERT (varchar(10),birthday,112) from customer
100---114 全是日期的样式
102 是 1985.10.18
111 是 1985/10/18


光取月日
select substring(Convert(varchar(10),birthday,102),6,5) from customer---------12.22
select substring(Convert(varchar(10),birthday),1,5) from customer       ---------12 22

两张表简单的连接
select customername from customer  a   join users  b  on  a.userid=b.userid   
where b.realname='张倩'  and  a.typeid=4

条件SQL语句！！

   string sqlCommand = " Select g.CustomerID,  g.CustomerName,g.PhoneNo,g.ZmoneNo,g.School,g.birthday,sd.LeagueName,g.create_dt,ww.realname,t.storename  from Customer g  join Store t on g.StoreID=t.StoreID join Users ww on g.ManagerID=ww.UserID join Users k on g.UserID=k.UserID join League sd on g.LeagueID=sd.LeagueID where {where}";
      

        string WHERE = "1=1";
        if (!string.IsNullOrEmpty(d.PhoneNo))
            WHERE += " and g.PhoneNo='" + d.PhoneNo + "'";
        if (!string.IsNullOrEmpty(d.Interest))
            WHERE += " and g.Interest like '%" + d.Interest + "%'";
        if (d.StoreID != 0)
            WHERE += " and g.StoreID='" + d.StoreID + "'";
        if (d.LeagueID != 0)
            WHERE += " and g.LeagueID='" + d.LeagueID + "'";
        DateTime date = new DateTime(1900, 1, 1);
        if (!string.IsNullOrEmpty(d.ZmoneNo))
            WHERE += " and g.ZmoneNo='" + d.ZmoneNo + "'";
        if (d.create_dt > date)
            WHERE += " and g.create_dt>='" + d.create_dt + "'";
        if (d.endTime > date)
            WHERE += " and g.create_dt<='" + d.endTime + "'";

        WHERE += "  order by g.CreateTime asc";
     
  
        sqlCommand = sqlCommand.Replace("{where}",WHERE);
       
        
       SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText =sqlCommand ;
        SqlDataAdapter sdr = new SqlDataAdapter(cmd);
        con.Close();
        return sdr;

 

简单sql函数用法

select datediff(day{month，year},开始时间参数，结束时间参数) from table  两个时间段所隔的天数  返回Int类型

 

 

两个时间段内，将要过生日的人数，多表连接
select customername,gender,phoneno,interest,storename ,address,substring(Convert(varchar(10),birthday,102),6,5) birthday from customer a join  store b on a.storeid=b.storeid
where (datepart(month,Convert(varchar(10),'2008-07-10',102))<datepart(month,birthday) or
datepart(month,Convert(varchar(10),'2008-07-10',102))=datepart(month,birthday)
and datepart(day,Convert(varchar(10),'2008-07-10',102))<=datepart(day,birthday))

and (datepart(month,Convert(varchar(10),'2008-10-18',102))>datepart(month,birthday)  or
datepart(month,Convert(varchar(10),'2008-10-18',102))=datepart(month,birthday)
and datepart(day,Convert(varchar(10),'2008-10-18',102))>=datepart(day,birthday)

and storename=(select storename  from  store,users where store.storeid=users.storeid and username='13992068309') )