美文网首页
sql server 条件查询案例

sql server 条件查询案例

作者: Vergil_wj | 来源:发表于2021-07-05 06:17 被阅读0次

    查询 80 后员工信息:

    select * from People
        where PeopleBirth >= '1980-1-1' and PeopleBirth <= '1989-12-31'
    
    -- 或者使用 between
    select * from People
        where PeopleBirth between '1980-1-1' and '1989-12-31'
    
    -- 也可以使用 year()
    select * from People
        where year(PeopleBirth ) between 1980 and 1989
    

    查询 30-40 岁之间,并且工资在 15000-30000之间的员工信息

    select * from People
        where (year(getDate())-year(PeopleBirth) between 30 and 40)
        and (PeopleSalary between 15000 and 3000)
    
    • getDate() :获取当前时间

    查询星座是巨蟹座的员工信息(6.22-7.22)

    select * from People
        where (month(PeopleBirth)=6 and day(PeopleBirth) >= 22)
        or  (month(PeopleBirth)=7 and day(PeopleBirth) <= 22)
    

    查询出工资比赵云高的人的信息

    select * from People
      where PeopleSalary > (select PeopleSalary from People where PeopleName = '赵云')
    

    查询出生肖是鼠的人员信息
    取一个属鼠的年份,如 1984,年数除以 12 根据余数判断即可。
    鼠4、牛5、虎6、兔7、龙8、蛇9、马10、羊11、猴0、鸡1、狗2、猪3

    select * from People where year(PeopleBirth)%12 = 4
    

    查询所有员工信息,添加一列,显示生肖

    select *  
      case year(PeopleBirth)%12
          when 4 then '鼠'
          when 5 then '牛'
          when 6 then '虎'
          when 7 then '兔'
          when 8 then '龙'
          when 9 then '蛇'
          when 10 then '马'
          when 11 then '羊'
          when 0 then '猴'
          when 1 then '鸡'
          when 2 then '狗'
          when 3 then '猪'
      end 生肖
      from People
    

    查询姓刘的员工信息,名字是两个字

    -- 使用 like
    select * from People where PeopleName like '刘_'
    
    -- 也可以使用函数sbustring()
    select * from People 
        where SUBSTRING(PeopleName,1,1) = '刘'  and len(PeopleName)=2
    

    查询电话号码 138开头,第四位是 7 或 8,最后一个号码是 5:

    select * from People
        where PeoplePhone like '138[7,8]%5'
    

    查询电话号码 138开头,第四位是 2 到 5 之间,最后一个号码不是 2 和 3:

    select * from People
        where PeoplePhone like '138[2-5]%[^2,3]'
    

    相关文章

      网友评论

          本文标题:sql server 条件查询案例

          本文链接:https://www.haomeiwen.com/subject/sufgyltx.html