@manhnguyenv

Welcome to my blog!

SQL Tips

June 30, 2017 17:55

WITH CUBE

http://chiencong.com/thong-ke-trong-sql-server-bang-pivot-table/

SQL (Structured Query Language)

 

declare @Department as table (DeptID INT IDENTITY(1,1) PRIMARY KEY,DeptName NVARCHAR(50))
declare @Employee as table (EmpID INT IDENTITY(1,1) PRIMARY KEY,DeptID INT, EmpName NVARCHAR(50),Salary bigint)

insert into @Department(DeptName) select 'Developer' union all select 'Accountant' union all select 'Tester'
insert into @Employee(DeptID,EmpName,Salary) select 1,'Manh',2000 union all select 1,'Chinh',1000 union all select 1,'Van',800 union all select 1,'Linh',1500 union all select 1,'Thu',1500 union all select 2,'Mai',500 union all select 2,'Vuong',1500 union all select 2,'Lien',1200 union all select 2,'Huong',800 union all select 2,'Phi',1500 union all select 3,'Dau',1500 union all select 3,'Chien',1200 union all select 3,'Cuong',800

--Chọn ra những department nào có ít nhất 3 employee salary > 1000
select d.DeptName from @Employee e join @Department d on e.DeptID = d.DeptID
where e.Salary > 1000 group by e.DeptID, d.DeptName having count(e.DeptID) >= 3

Categories

Recent posts