MYSQL 未正确显示唯一值(不同) [英] MYSQL Not Displaying Unique Value (Distinct) Properly

问题描述

样本数据集:

DROP TABLE IF EXISTS user_info;

CREATE TABLE user_info
(id SERIAL PRIMARY KEY
,firstname VARCHAR(20) NOT NULL
,status VARCHAR(20) NOT NULL
,nature_of_work VARCHAR(20) NOT NULL
,level VARCHAR(20) NOT NULL
,course_or_section VARCHAR(20) NOT NULL
);

INSERT INTO user_info VALUES
(1,'John' ,'Employee'    ,'Call Center','N/A'    ,'N/A'),
(2,'Raul' ,'Grade School','BES'        ,'Grade 6','Nara'),
(3,'Brian','Employee'    ,'BPSU'       ,'2'      ,'BSCS');

DROP TABLE daily_report;
CREATE TABLE daily_report
(id SERIAL PRIMARY KEY 
,survey_at_wat_blh VARCHAR(20) NOT NULL
,date_opened DATETIME NOT NULL
,userid INT NOT NULL
);

INSERT INTO daily_report VALUES
(1,'tor'          ,'2021-03-12 09:54:54',1),
(2,'DFS'          ,'2021-03-12 14:44:06',2),
(3,'DFS'          ,'2021-03-12 10:03:02',2),
(4,'Cupang West'  ,'2021-03-12 10:05:25',3),
(5,'Cupamg Proper','2021-03-12 10:05:59',3);

所以，

SELECT * FROM user_info;
+----+-----------+--------------+----------------+---------+--------------------+
| id | firstname | status       | nature_of_work | level   | course_or_section |
+----+-----------+--------------+----------------+---------+--------------------+
|  1 | John      | Employee     | Call Center    | N/A     | N/A                |
|  2 | Raul      | Grade School | BES            | Grade 6 | Nara               |
|  3 | Brian     | Employee     | BPSU           | 2       | BSCS               |
+----+-----------+--------------+----------------+---------+--------------------+

SELECT * FROM daily_report;
+----+-------------------+---------------------+--------+
| id | survey_at_wat_blh | date_opened         | userid |
+----+-------------------+---------------------+--------+
|  1 | tor               | 2021-03-12 09:54:54 |      1 |
|  2 | DFS               | 2021-03-12 14:44:06 |      2 |
|  3 | DFS               | 2021-03-12 10:03:02 |      2 |
|  4 | Cupang West       | 2021-03-12 10:05:25 |      3 |
|  5 | Cupamg Proper     | 2021-03-12 10:05:59 |      3 |
+----+-------------------+---------------------+--------+

所以这是我的查询及其正确显示，这正是我想要的

So here is My Query and its Displaying correct and that is what exactly I want

SET @row := 0;
SELECT CONCAT(@row := @row + 1,' ',ui.firstname) AS 'FullName',
       d.survey_at_what_blh AS Place, ui.status, ui.nature_of_work, 
       ui.level, ui.course_or_section
FROM (SELECT DISTINCT survey_at_what_blh , userid FROM daily_report) d 
  JOIN userinfo ui ON d.userid = ui.id

<头>

全名	地点	状态	nature_of_work	级别	course_or_section
约翰一书	Tor	员工	呼叫中心	不适用	不适用
2 劳尔	DFS	小学	贝斯	6 年级	奈良
3 布赖恩	Cupang Proper	员工	BPSU	2	BSCS
4 布赖恩	古邦西	员工	BPSU	2	BSCS

当我在这里添加列d.date_opened"(日期时间数据类型)时，地点"会发生什么?列不会明显显示相同的值这是我添加d.date_opned"的代码

And when I added the column "d.date_opened"(DateTime Datatype) here what happens the "Place" column is not going distinct its displaying same value here is the code where I added the "d.date_opned"

SET @row := 0;
SELECT CONCAT(@row := @row + 1,' ',ui.firstname) AS 'FullName',
       d.survey_at_what_blh, ui.status, ui.nature_of_work, ui.level, 
       ui.course_or_section,  date(d.date_opened)
FROM (SELECT DISTINCT survey_at_what_blh, date_opened, userid FROM daily_report) d 
INNER JOIN userinfo ui ON d.userid = ui.id    
WHERE date(d.date_opened) BETWEEN '2021-03-11' AND '2021-03-12'

而上面代码的结果是这样的

and the result of the above code is this

<头>

全名	地点	状态	nature_of_work	级别	course_or_section	日期
约翰一书	Tor	员工	呼叫中心	不适用	不适用	2021-03-12
2 劳尔	DFS	小学	贝斯	6 年级	奈良	2021-03-12
3 劳尔	DFS	小学	贝斯	6 年级	奈良	2021-03-12
3 布赖恩	Cupang Proper	员工	BPSU	2	BSCS	2021-03-12
4 布赖恩	古邦西	员工	BPSU	2	BSCS	2021-03-12

因此，当我添加 d.date_opened 时，您可以看到位置"列显示相同的值.那么如何让它只显示唯一值呢?你们能给我一些提示或提示吗?

so as you can see the 'Place' columns is display the Same Value when I added the d.date_opened. So how do I make it display unique value only? Can you guys give me some tips or hint?

这是我想要的输出

<头>

全名	地点	状态	nature_of_work	级别	course_or_section	日期
约翰一书	Tor	员工	呼叫中心	不适用	不适用	2021-03-12
2 劳尔	DFS	小学	贝斯	6 年级	奈良	2021-03-12
3 布赖恩	Cupang Proper	员工	BPSU	2	BSCS	2021-03-12
4 布赖恩	古邦西	员工	BPSU	2	BSCS	2021-03-12

推荐答案

请考虑以下事项:架构(MySQL v8.0)

CREATE TABLE user_info
(id SERIAL PRIMARY KEY
,firstname VARCHAR(20) NOT NULL
,status VARCHAR(20) NOT NULL
,nature_of_work VARCHAR(20) NOT NULL
,level VARCHAR(20) NOT NULL
,course_or_section VARCHAR(20) NOT NULL
);

INSERT INTO user_info VALUES
(1,'John' ,'Employee'    ,'Call Center','N/A'    ,'N/A'),
(2,'Raul' ,'Grade School','BES'        ,'Grade 6','Nara'),
(3,'Brian','Employee'    ,'BPSU'       ,'2'      ,'BSCS');

CREATE TABLE daily_report
(id SERIAL PRIMARY KEY 
,survey_at_wat_blh VARCHAR(20) NOT NULL
,date_opened DATETIME NOT NULL
,userid INT NOT NULL
);

INSERT INTO daily_report VALUES
(1,'tor'          ,'2021-03-12 09:54:54',1),
(2,'DFS'          ,'2021-03-12 14:44:06',2),
(3,'DFS'          ,'2021-03-12 10:03:02',2),
(4,'Cupang West'  ,'2021-03-12 10:05:25',3),
(5,'Cupamg Proper','2021-03-12 10:05:59',3);

---

查询 #1

SELECT ROW_NUMBER() OVER(ORDER BY x.id) row_num
     , x.firstname
     , x.survey_at_wat_blh 
     , x.status
     , x.nature_of_work
     , x.level
     , x.course_or_section
     , x.date_opened 
  FROM 
     ( SELECT DISTINCT u.id
                     , u.firstname
                     , r.survey_at_wat_blh 
                     , u.status
                     , u.nature_of_work
                     , u.level
                     , u.course_or_section
                     , DATE(r.date_opened) date_opened 
                  FROM daily_report r
                  JOIN user_info u 
                    ON u.id = r.userid 
                 WHERE r.date_opened >= '2021-03-11 00:00:00' 
                   AND r.date_opened < '2021-03-13 00:00:00'
     ) x
 ORDER
    BY x.id;

<头>

row_num	名字	survey_at_wat_blh	状态	nature_of_work	级别	course_or_section	date_opened
1	约翰	tor	员工	呼叫中心	不适用	不适用	2021-03-12
2	劳尔	DFS	小学	贝斯	6 年级	奈良	2021-03-12
3	布莱恩	古邦西	员工	BPSU	2	BSCS	2021-03-12
4	布莱恩	Cupamg 正确	员工	BPSU	2	BSCS	2021-03-12

---

查看 DB Fiddle

这篇关于MYSQL 未正确显示唯一值(不同)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！