mysql - phpMyAdmin:加入三列,统计每个值存在的次数,并显示每个值所引用的PK
问题描述
我有许多表,其中两个是诊断(test_id,test_name)和评估(evaluation_id,test_1,test_2,test_3)。Test_1、2、3 是引用 Diagnostics.test_id 的外键。我需要计算每个测试的执行次数(在所有 3 个测试列中),只显示已经执行 3 次或更多次的测试,并给出相应的 test_name。
我目前的查询是:
SELECT test, count(*) Total
FROM
(SELECT test_1 AS test FROM Evaluation
UNION ALL
SELECT test_2 FROM Evaluation
UNION ALL
SELECT test_3 FROM Evaluation) d
GROUP BY test
HAVING COUNT(Total) > 2
ORDER BY test
这对三列中的每个测试进行分组和计数,但不会将测试转换为相应的测试名称。我在 phpMyAdmin 相对较新,所以任何帮助都会很棒!
解决方案
尝试这个:
SELECT test, test_name, count(*) Total
FROM
(
SELECT test_1 as test, test_name FROM Evaluation e
join Diagnostics di on e.test_1 = di.test_id
UNION ALL
SELECT test_2 as test, test_name FROM Evaluation e
join Diagnostics di on e.test_2 = di.test_id
UNION ALL
SELECT test_3 as test, test_name FROM Evaluation e
join Diagnostics di on e.test_3 = di.test_id
) d
GROUP BY test, test_name
HAVING COUNT(*) > 2
ORDER BY test [or test_name if preferred]
请注意,UNION 中的每个选择都完全独立于其他选择。
推荐阅读
- javascript - click event is triggered by scrolling
- javascript - 有没有办法在 HTML 元素中获取 javascript 变量的值?
- php - DOMPDF takes time when it generates a PDF with Images
- c# - Getting error while decrypting data through AES/ECB/PKCS7 algorithm - "Specified key is not a valid size for this algorithm"
- flutter - Another exception was thrown: Error: Could not find the correct Provider
above this History Widget flutter - kotlin - 为什么我在 Kotlin 中出现块体错误的函数中需要“返回”表达式?
- react-native - How to send bearer token as parameter and retrieve it in another screen in React Native
- docker - Docker: Error response from daemon - why is it not showing in docker container ls?
- sql - How to import a JSON file into postgresql databse?
- javascript - JQuery Input Field clickable again after using the blur() method