Table 1
Date name list number group
2020/12/1 zhang SAN Q123456 group 1
2020/12/1 li si F123457 two groups
2020/12/1 threes D123458 3 group
2020/12/1 two E123459 3 groups
2020/12/2 zhang SAN F123460 group 1
2020/12/2 zhang SAN W123461 group 1
2020/12/3 li si Y123462 two groups
2020/12/3 li si Q123463 two groups
2020/12/3 two E123451 3 groups
Lines containing numerous contains numerous under under lines contain numerous contains numerous under

Roster
Group name
Zhang SAN group 1
Li si two groups
Two 3 groups
The lines containing numerous contains numerous under

Requirements: every day each group is lower than the two single number of people (display the date, name of group, less than two single number of people)

CodePudding user response:

The SELECT AA. Date, AA group, the COUNT (AA) name) less than two single number
The FROM (
Name, date, SELECT a. a. a. GROUP, the COUNT (a.) orders FROM table 1 GROUP BY a. a. date, a. name, a. team
) AA WHERE AA. Single quantity & lt; 2 GROUP BY AA. Date, AA GROUP

CodePudding user response:

 
WITH CTE_1 
AS 
(name, SELECT a. b. date, a. team 
The FROM TABLE_2 A 
The JOIN (SELECT DISTINCT date AS the date the FROM TABLE_1) AS B ON 1=1), 

SELECT the date, the group, the COUNT (name) 
The FROM 
(date, name, SELECT a. a. a. team 
The FROM CTE_1 A 
LEFT the JOIN (date, SELECT GROUP, the COUNT (DISTINCT single number) AS QTY FROM TABLE_1 GROUP BY GROUP BY date, name, GROUP) AS B ON a. date=b. date AND a. name=b. name AND a. team=b. team 
WHERE ISNULL (QTY, 0) & lt; AS C=2) 
GROUP BY date, the GROUP