如何根据输出编写mysql查询？

My table should be like this:

slno      sheetno   time_stamp
1         101       2018-05-27 11:16:58 
2         103       2018-05-27 11:20:18 
3         102       2018-05-27 11:24:39 
4         105       2018-05-27 11:27:44 
5         106       2018-05-27 11:34:03 
6         107       2018-05-27 11:51:32 
7         108       2018-05-27 12:41:34 
8         109       2018-05-27 12:48:13 
9         110       2018-05-27 12:52:40 
10        111       2018-05-27 13:18:59 
11        112       2018-05-27 14:13:07 
12        113       2018-05-27 14:18:04 
13        114       2018-05-27 14:21:12 
14        115       2018-05-27 14:54:01 
15        117       2018-05-27 14:55:14 
16        118       2018-05-27 14:57:31 
17        119       2018-05-27 15:05:30 
18        120       2018-05-27 15:30:44 
19        121       2018-05-27 15:46:38 
20        122       2018-05-27 16:02:16 
21        123       2018-05-28 11:32:10 
22        124       2018-05-28 11:38:18 
23        125       2018-05-28 11:49:45 
24        126       2018-05-28 11:52:09 
25        127       2018-05-28 12:21:00 
26        128       2018-05-28 12:24:33 
27        129       2018-05-28 13:04:56 
28        130       2018-05-28 14:10:07 
29        140       2018-05-28 14:18:47 
30        141       2018-05-28 14:22:09 
31        142       2018-05-28 14:28:04 
32        143       2018-05-28 14:37:53 
33        144       2018-05-28 15:06:27 
34        145       2018-05-28 15:33:05 
35        146       2018-05-28 15:35:44 
36        147       2018-05-28 15:53:41 
37        148       2018-05-28 16:13:16 
38        149       2018-05-28 16:27:51 
39        150       2018-05-28 16:38:54 
40        151       2018-05-28 16:44:54

My output should be like this:

From           To            Total No of days   FN   AN
27-05-2018     28-05-2018    2                  2    2

This is my query:

SELECT
    DATE_FORMAT(MIN(time_stamp), '%Y-%m-%d') AS min,
    DATE_FORMAT(MAX(time_stamp), '%Y-%m-%d') AS max,
    COUNT(DISTINCT DATE(time_stamp)) AS cnt,
    COUNT(CASE WHEN  TIME(time_stamp) < '12:00:00' THEN 1 END) AS FN,
    COUNT(CASE WHEN TIME(time_stamp) >= '12:00:00' THEN 1 END) AS AN 
FROM master_tab;

The output of 'FN' ,'AN' should be grouped. How to solve this? I've tried to put DISTINCT in front of case it shows i value.

You seem to be on the right track:

SELECT
    DATE_FORMAT(MIN(time_stamp), '%Y-%m-%d') AS min,
    DATE_FORMAT(MAX(time_stamp), '%Y-%m-%d') AS max,
    COUNT(DISTINCT DATE(time_stamp)) AS cnt,
    COUNT(CASE WHEN TIME(time_stamp) < '12:00:00' THEN 1 END) AS FN,
    COUNT(CASE WHEN TIME(time_stamp) >= '12:00:00' THEN 1 END) AS VA
FROM master_tab;

enter image description here

Demo

Notes:

  • To select the min/max dates, there is no sense in doing an extra cast to date, because DATE_FORMAT will already these values as dates only.
  • Same is true for the distinct date count; note that DISTINCT is not a function in MySQL, but rather a keyword
  • The conditional counts for FN (morning) and VA (evening) times count the number 1 when there is a match, and count NULL otherwise. Note that NULL is ignored by COUNT, so we only tally matching records. The ELSE condition, which is omitted, defaults to NULL.

You can try following query:

SELECT
    DATE_FORMAT(MIN(time_stamp), '%Y-%m-%d') AS min,
    DATE_FORMAT(MAX(time_stamp), '%Y-%m-%d') AS max,
    COUNT(DISTINCT DATE(time_stamp)) AS cnt,
    COUNT(DISTINCT CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) < '12:00:00' THEN 1 END))) AS FN,
    COUNT(DISTINCT CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) >= '12:00:00' THEN 1 END))) AS VA
FROM master_tab;

And the output

min         max         cnt FN  VA
2018-05-27  2018-05-28  2   2   2

Demo