Table 1
ID dept visit people Vidate content
1 huashan sent guo jing 2018/6/20 through wudang tips
2 huashan sent guo jing 2018/6/20 shaolin classic
3 huashan sent guo jing 2018/6/21 yi jin jing
4 huashan sent guo jing 2018/6/22 sunflower treasure dian
Five huashan sent guo jing 2018/6/22 shaolin classic
6 huashan sent guo jing 2018/6/24 copying wudang tips
7 huashan sent guo jing 2018/6/25 shaolin classic
8 huashan sent huang2 rong2 2018/6/20 put yi jin jing
Huang2 rong2 huashan sent 2018/6/22 peep shaolin classic
10 huashan huang2 rong2 2018/6/22 into the sutra depository, copy books
11 huashan sent huang2 rong2 2018/6/24 refer to wudang tips
12 huashan sent huang2 rong2 2018/6/25 sunflower treasure dian
13 huashan sent Jiang Dongguo 2018/6/20 yi jin jing
14 huashan sent Jiang Dongguo 2018/6/21 shaolin classic
15 huashan sent Jiang Dongguo 2018/6/20 yi jin jing
16 huashan sent Jiang Dongguo 2018/6/20 yi jin jing
17 huashan sent Jiang Dongguo 2018/6/22 shaolin classic
18 huashan sent Jiang Dongguo 2018/6/24 yi jin jing
19 huashan sent Jiang Dongguo 2018/6/25 copying shaolin classic
20 huashan sent kai-tai li 2018/6/20 wudang tips
21 huashan sent kai-tai li 2018/6/21 peep shaolin classic
22 huashan sent kai-tai li 2018/6/22 wudang tips
23 huashan sent kai-tai li 2018/6/23 recite wudang tips
24 huashan sent kai-tai li 2018/6/24 shaolin classic
25 huashan sent kai-tai li 2018/6/25 sunflower treasure dian
This time, I hope to get everyone's visit total number and total number of days (the number of visits a day may be 0, 1, may also repeatedly)
It is concluded that the total number is very simple, the statement is:
SELECT dept, to visit people, Count (visit) the AS visit total number
FROM table 1
GROUP BY dept, visit;
Get the total number:
Table 1 query 2
Dept visit people total number
Huashan sent guo jing 7
Huashan sent huang2 rong2 5
Huashan sent Jiang Dongguo 7
Kai-tai li hua shan sent 6
How to get the total number of days?
I now is the first step in step two queries, count the number per person per day of the visit, query generation 1,
The second again to query 1 count, generate each visit days
Is there a way, an SQL statement, generate the total number of days?
Preferably, an SQL statement, at the same time produce each person's visit total number and total number of days,
Just a query output, can?
CodePudding user response:
You can write in SQL server, use the COUNT (DISTINCT d)SELECT dept, BFRS AS [to visit], Count (BFRS) AS total number] [visit,
D (SELECT COUNT (DISTINCT) FROM t AS b WHERE b.b fr=a. fr) AS [to visit the total number of days]
The FROM t AS a
GROUP BY dept, eliminated BFRS;
CodePudding user response:
Access does not support the COUNT (DISTINCT date), so there is another player so write:The SELECT dept
And BFRS AS [the visit]
, and Count (BFRS) AS total number] [visit
, (SELECT COUNT (1) the FROM (SELECT AS DISTINCT FROM t d b WHERE b.b fr=a. fr) AS tt) AS [to visit the total number of days]
The FROM t AS a
GROUP BY dept, eliminated BFRS;
The result is this statement of results: visit the total number of days a list of all 0
Really don't know in the Access, should write, how to come to consult everybody a great god
Thank you very much!
CodePudding user response:
GROUP BY Vidate not with respect to OK?