SQL statements to consult-CodePudding

There are two tables
Userdb user table

Id name
Zhang SAN 1

2, dick, and harry3 fifty

The log log in log table

Id name logdate
1 zhang 20200101
2 bill 20201201
3 threes 20200102

Implementing such statistics

Id name counts
1 zhang SAN 2
1
2, dick, and harry3 fifty and 0

Actual number is logged in, no show 0

CodePudding user response:

The SELECT Anderson D, A.N AME, ISNULL (COUNT (*), 0) AS COUNTS
The FROM USERDB A
JOIN the LOG ON B A.N AME=B.N AME
GROUP BY Anderson, D, A.N AME

CodePudding user response:

 
With userdb (id, name) as (

Select 1, 'zhang' union all 
Select 2, 'bill' union all 
Select 3, 'Cathy' 
) 
, the log (id, name, logdate) as 
(

Select 1, 'zhang', '20200101' union all 
Select 2, 'bill', '20201201' union all 
Select 3, 'zhang', '20200102' 
) 
Select a t1. The name, the count (t2) name) as log_count 
The from userdb t1 
Left the join the log t2 on t1. Name=t2. The name 
Group by t1. Name

Results:
The name log_count
Zhang SAN 2
Li si 1
Fifty and 0

CodePudding user response:

reference 1st floor RINK_1 response:

SELECT Anderson D, A.N AME, ISNULL (COUNT (*), 0) AS COUNTS
The FROM USERDB A
JOIN the LOG ON B A.N AME=B.N AME
GROUP BY Anderson, D, A.N AME

You run results is:
ID NAME COUNTS
1 zhang SAN 2
1, 2, dick, and harry

CodePudding user response:

refer to sunny day on the second floor 8 response:

 
With userdb (id, name) as (

Select 1, 'zhang' union all 
Select 2, 'bill' union all 
Select 3, 'Cathy' 
) 
, the log (id, name, logdate) as 
(

Select 1, 'zhang', '20200101' union all 
Select 2, 'bill', '20201201' union all 
Select 3, 'zhang', '20200102' 
) 
Select a t1. The name, the count (t2) name) as log_count 
The from userdb t1 
Left the join the log t2 on t1. Name=t2. The name 
Group by t1. Name

Results:
The name log_count
Zhang SAN 2
Li si 1
Fifty and 0

Sorry to ask, your statement I ran down is
1 zhang SAN 2
1
2, dick, and harry
But I did not use with that piece, because there are a lot of records in the database

CodePudding user response:

 
The select t1 PeopleName, count (t2) PeopleName) from Table1 t1 left join Table2 t2 on t1. PeopleName=t2. PeopleName group by t1. PeopleName

CodePudding user response:

reference 4 floor zestsnake response:

Quote: refer to the second floor sunny day 8 response:

With userdb (id, name) as (

Select 1, 'zhang' union all
Select 2, 'bill' union all
Select 3, 'Cathy'
)
, the log (id, name, logdate) as
(

Select 1, 'zhang', '20200101' union all
Select 2, 'bill', '20201201' union all
Select 3, 'zhang', '20200102'
)
Select a t1. The name, the count (t2) name) as log_count
The from userdb t1
Left the join the log t2 on t1. Name=t2. The name
Group by t1. Name

Results:
The name log_count
Zhang SAN 2
Li si 1
Fifty and 0

Sorry to ask, your statement I ran down is
1 zhang SAN 2
1
2, dick, and harry
But I did not use with that piece, because there are many records in the database

The same code you see upstairs, and I, is successful, the core is left you want join, what you write is the join, so no

CodePudding user response:

sunny day 8 reference 3 floor response:

Quote: refer to 1st floor RINK_1 response:
SELECT Anderson D, A.N AME, ISNULL (COUNT (*), 0) AS COUNTS
The FROM USERDB A
JOIN the LOG ON B A.N AME=B.N AME
GROUP BY Anderson, D, A.N AME

You run results is:
ID NAME COUNTS
1 zhang SAN 2
2 li si 1

Made a mistake, should be LEFT to JOIN

CodePudding user response:

refer to 7th floor RINK_1 response:

Quote: sunny day 8 reference 3 floor response:

Quote: refer to 1st floor RINK_1 response:
SELECT Anderson D, A.N AME, ISNULL (COUNT (*), 0) AS COUNTS
The FROM USERDB A
JOIN the LOG ON B A.N AME=B.N AME
GROUP BY Anderson, D, A.N AME

You run results is:
ID NAME COUNTS
1 zhang SAN 2
2 li si 1

Made a mistake, should be LEFT JOIN

Well, you are right, also forget NULL will participate in the COUNT (*), should be as direct as you use the COUNT (B.N AME)