SQLQuery5.sql
/**----
select * from KuChun
select * from Sell
select sum(SellNum)as Sumd,SellID,ProductType from Sell group by SellID,ProductType
select *
from KuChun t1 left join (select SellID, sum(sellnum)as snum,ProductType from Sell group by SellID,ProductType)t2
on t1.ShopID=t2.SellID and t1.ProductType=t2.ProductType
order by t1.ShopID
SELECT T1.SHOPID, T1.PRODUCTTYPE, T1.KNUM, (select ISNULL(sum(t2.sellnum),0) from Sell t2 where t2.sellid = t1.shopid and t2.producttype= t1.productType) sumsell
FROM KuChun T1
order by t1.shopid;
select T1.SHOPID, T1.PRODUCTTYPE, T1.KNUM, ISNULL(sum(t2.sellnum),0) sellnum
from KuChun t1
left join Sell t2
on t2.sellid = t1.shopid and t2.producttype= t1.productType
group by T1.SHOPID, T1.PRODUCTTYPE, T1.KNUM
order by t1.shopid;
select T1.SHOPID, T1.PRODUCTTYPE, sum(T1.KNUM) knum, ISNULL(sum(t2.sellnum),0) sellnum
from KuChun t1
left join Sell t2
on t2.sellid = t1.shopid and t2.producttype= t1.productType
group by T1.SHOPID, T1.PRODUCTTYPE
order by t1.shopid;
select c.*,a.s_score as '01课程 score',b.s_score as '02课程 score' from score a,score b
left join student c on b.s_id = c.s_id
where a.s_id = b.s_id and a.c_id = '01' and b.c_id = '02' and a.s_score > b.s_score;
**/
---ALTER TABLE KuChun ALTER COLUMN Knum int;
select * from student t2
select * from Course t3
select * from Teacher t4
select * from Score t1
-- 50、查询下月过生日的学生
select * from(
select ss.* ,datename(mm,ss.s_birth) as wk,datename(mm,getdate()) as wk1 from Student ss )tt
where tt.wk=tt.wk1+1
-- 49、查询本月过生日的学生
select ss.*,datename(mm,ss.s_birth) from Student ss where datename(mm,getdate())=datename(mm,ss.s_birth)
-- 48、查询下周过生日的学生
select * from(
select ss.*,datename(wk,ss.s_birth)as'周数', cast( datename(wk,ss.s_birth) as decimal)- datename(wk,getdate()) as wk from Student ss
)tt
where tt.wk=1
-- 47、查询本周过生日的学生
select ss.* from Student ss where datename(wk,ss.s_birth)=datename(wk,get
RSS订阅
650
