This statement should be how to write?

CodePudding user response:

; WITH cte AS (
SELECT deptno, 
AVG (age) AS ageAvg 
The FROM t 
GROUP BY 
Deptno 
) 
SELECT * 
The FROM t 
WHERE the EXISTS (
SELECT 1 
The FROM t AS b 
WHERE a. d. eptno=b.d eptno 
AND a.a ge & gt; B.a geAvg 
) 

CodePudding user response:

Fix:

; WITH cte AS (
SELECT deptno, 
AVG (age) AS ageAvg 
The FROM t 
GROUP BY 
Deptno 
) 
SELECT * 
The FROM t AS a 
WHERE the EXISTS (
SELECT 1 
The FROM cte AS b 
WHERE a. d. eptno=b.d eptno 
AND a.a ge & gt; B.a geAvg 
) 

CodePudding user response:

If object_id (' employees') is not null drop table employee

The create table employee
(
Employee name nvarchar (10) not null,
Employee groups nvarchar (10) not null,
Workers age smallint not null
)

Insert into staff values
'(' zhang', 'thugs group, 18),
'(' bill', 'thugs group, 19),
'(' Cathy', 'thugs group, 20),
Poet li bai (' ', 'group', 21),
'(' du fu', 'poets group, 22),
Poet bai juyi '(',' group ', 23),
'(' liu bei', 'the three group, 24),
(" guan yu ", the three groups, 25),
(' zhang fei ', 'the three groups', 26)

Select a. * from employees a
Inner join
(select staff categories, avg (employee age) as the average age of
The from staff group by staff group) b
On a. staff category=b. staff group
And a. employees age & gt; B. the average age of

/*
Employee name group employee age
Fifty and gangsters group 20
Zhang fei in The Three Kingdoms group of 26
The poet bai juyi group 23
*/