sqlclass05_2.sql

SQL高级技巧

select * from books
select * from goods

select * from studentmessage
select * from classmessage
select * from class_student

select s.studentid,s.[name],c.classname,c.manager
from studentmessage as s
        left join class_student as ct on s.studentid=ct.studentid
        left join classmessage as c on c.classid=ct.classid

select * from resultmessage


select s.[name],c.coursesname,r.result 
from resultmessage as r
         join studentmessage as s on s.studentid = r.studentid
         join coursesmessage as c on c.coursesid=r.coursesid


select s.[name],cc.classname,c.coursesname,r.result 
from resultmessage as r
         join studentmessage as s on s.studentid = r.studentid
         join coursesmessage as c on c.coursesid=r.coursesid
         join class_student as ct on ct.studentid=s.studentid
         join classmessage as cc on cc.classid=ct.classid
where 
         cc.classname='T18'


select 1 as 类型, books.bookname as 商品名称,books.price as 单价, books.qty as 数量,price*qty as 总金额 from books
union
select 3 ,goodsname,price  ,qty,price*qty  from goods 
union
select 2,'--book count--',NULL,sum(qty),sum(price*qty) from books
union
select 4,'--goods count--',NULL,sum(qty),sum(price*qty) from goods
union
       select 9,'--count--',NULL,sum(数量),sum(总金额) 
       from 
	       (select 1 as 类型, books.bookname as 商品名称,books.price as 单价, books.qty as 数量,price*qty as 总金额 from books
	       union
	       select 3 ,goodsname,price  ,qty,price*qty  from goods ) as a
      
order by   类型 ,单价 desc

select [name] from table1
union all
select [name] from table2

select classname from table3 group by classname

select DISTINCT classname from table3