SQL Server 子查询语句综合归纳

/*
高级查询：
统计查询：sum max min count avg 要么单独使用 要么和group by搭配使用
联表查询：内联inner join 左联left 右联right 全联full
*/
--a:123  左联：123  全联：1235
--b:125  右联：125  内联：12

--caiwu

--2、查询所有家庭成员工
select * from tb_users

--3、查询收支项目表
select * from tb_inoutfield

--4、查询日常收支表
select * from tb_inoutInfo

--模糊查询
--5、查询所有梁姓家庭成员（like）
select * from tb_users where uname like '梁%'

--6、查询日常收支表中所有rmenu即备注含‘去’字的记录
select * from tb_inoutInfo where rmenu like '%去%'

--7、查询日常收支表中2月到3月之间的收支情况（between）
select * from tb_inoutInfo where MONTH(rdate) between 2 and 2

--8、查询日常收支表中1000到5000之间金额的收支记录
select * from tb_inoutInfo where rmoney between 1000 and 5000

--9、查询日常收支表中工资和奖金记录，即xid为1，2的记录
select * from tb_inoutInfo where xid in(1,2)
select xid from tb_inoutfield where xname = '奖金' or xname = '工资'

--聚合函数
--10、求存款额，即对金额求和
select SUM(rmoney) from tb_inoutInfo

--11、求总支出，即为金额为负数的求和
select SUM(rmoney) from tb_inoutInfo where rmoney<0

--12、求梁山伯今年发了几次工资，即为uid为1且xid为1的记录记数
select COUNT(*) 次数 from tb_inoutInfo where xid = 1 and uid = 1

--13、最大收入额，即求最大值
select MAX(rmoney) from tb_inoutInfo 

--分组
--14、求每个人的财务金额和，即根据uid分组后求rmoney的和
select uid,SUM(rmoney) from tb_inoutInfo group by uid

--15、求每个人的支出金额，即条件为rmoney为负数，
--根据uid分组求rmoney的和
select uid,SUM(rmoney) from tb_inoutInfo where rmoney<0 group by uid

--16、求每个人的收入金额，但只显示超过10000元，
--即条件为rmoney大于0, 根据uid分组求和，并有having筛选大于10000的
select uid from tb_inoutInfo where rmoney>0
group by uid having SUM(rmoney)>10000

--17、求收入支出项目个数，即在项目表中按收支类型分组，再计数
select xtype,COUNT(*) from tb_inoutfield group by xtype

--联表查询
--18、在收支项目表和日常收支表中查询项目编号，项目名称，
--收支类型，收支金额
select b.xid,b.xname,b.xtype,a.rmoney from tb_inoutInfo as a,tb_inoutfield as b
where a.xid=b.xid

select b.xid,b.xname,b.xtype,a.rmoney from tb_inoutInfo as a
inner join tb_inoutfield as b
on a.xid = b.xid

--19、在成员表和日常收支表中查询家庭角色，姓名，金额，操作日期
select a.upart,a.uname,b.rmoney,b.rdate from tb_users as a ,
tb_inoutInfo as b
where a.uid=b.uid

--20、在收支项目表和日常收支表，成员表中查询姓名，项目名称，
--收支金额
select c.uname,a.xname,b.rmoney from tb_inoutfield as a, tb_inoutInfo as b, tb_users as c
where a.xid=b.xid and b.uid=c.uid 

---高级子查询
select * from stuInfo
select * from stuMarks

--查询年龄比李斯文大的学生姓名
--使用子查询时，如果使用了比较运算符 子查询的结果就必须是单行单列
select stuName from stuInfo where stuAge>(
    select stuAge from stuInfo where stuName = '李斯文'
)

--查询笔试成绩大于70分的学生姓名
--①联表
select a.stuName from stuInfo as a,stuMarks as b
where a.stuNo=b.stuNo and b.writtenExam>70

--②in子查询
select stuName from stuInfo where stuNo in(
    select stuNo from stuMarks where writtenExam>70
)

--子查询的嵌套
--查询笔试成绩大于全班平均笔试分的学生姓名
select stuName from stuInfo where stuNo in(
    select stuNo from stuMarks where writtenExam>(
        select AVG(writtenExam) from stuMarks
    )
)

--exists子查询
--如果班上存在笔试不及格的情况，就打印'存在'
--否则 打印'不存在'
if exists(select * from stuMarks where LabExam<60)
begin
  print '存在'
end
else
begin
  print '不存在'
end

----补充内容----
--1.复制表及其数据
select * into bbb from stuInfo
select * from bbb
--2.复制表 不要数据 只要其结构 恒不等式
select * into ccc from stuInfo where 1=2
select * from ccc
--3.union 
--4.如果年龄为null，就用0替换 isnull(a,b):如果a是null 就用b替换
select sname,isnull(sage,0) from tb_stu

use kefang
--查询所有学生的考试情况 如果score为null 就替换为'缺考'
select a.SName,isnull(cast(b.Score as varchar),'缺考') from tb_stu as a
left join
tb_score as b
on a.SCode = b.StudentID

--可以把查询出来的结果集当做是一个新的表查询
use caiwu
select * from(
    select * from stuInfo where stuNo in(
        select stuNo from stuMarks where writtenExam>70
    )
) as a where stusex = '男'

建立表
--1.应到人数 实到人数 缺考人数
select x 应到人数,y 实到人数,x-y 缺考人数 from(
    select COUNT(*) as x from stuInfo
) as a
inner join
(
    select COUNT(*) as y from stuMarks
) as b
on 1=1

--2.总人数 通过人数 通过率
select x 总人数,y 通过人数,cast(y*100/x as varchar)+'%' 通过率 from(
    select COUNT(*) as x from stuInfo
) as a
inner join
(
    select COUNT(*) as y from stuMarks where LabExam>=60 and writtenExam>=60
) as b
on 1=1