我正在嘗試根據列列印文本。我的想法是將每個職業的所有計數結合起來，并用一個案例來展示它。但我不確定這段代碼有什么問題。由于 sCount 不在欄位串列中，因此出現錯誤。任何幫助表示贊賞。
SELECT CASE
WHEN Occupation = "doctor" THEN
CONCAT("There are a total of ", dCount, " " , Occupation, "s.")
WHEN Occupation = "singer" THEN
CONCAT("There are a total of ", sCount, " " , Occupation, "s.")
END
FROM (
SELECT * FROM (
SELECT COUNT(Occupation) AS dCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Doctor'
UNION
SELECT COUNT(Occupation) AS sCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Singer'
) AS s
) AS m;
// this didnt work either
SELECT CASE
WHEN Occupation = "doctor" THEN
CONCAT("There are a total of ", dCount, " " , Occupation, "s.")
WHEN Occupation = "singer" THEN
CONCAT("There are a total of ", sCount, " " , Occupation, "s.")
END
FROM (
SELECT COUNT(Occupation) AS dCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Doctor'
UNION
SELECT COUNT(Occupation) AS sCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Singer'
) AS m;
uj5u.com熱心網友回復：
你在嘗試這樣的事情嗎：
CREATE TABLE OCCUPATIONS (
Occupation varchar(100) );
INSERT INTO OCCUPATIONS VALUES ('doctor'),('doctor'),
('singer'),('singer'),
('doctor'),('doctor'),
('singer'),('singer'),
('doctor'),('singer'),
('singer'),('singer');
SELECT CASE
WHEN Occupation = "doctor" THEN
CONCAT("There are a total of ", tbl.dCount, " " , Occupation, "s.")
WHEN Occupation = "singer" THEN
CONCAT("There are a total of ", tbl.sCount, " " , Occupation, "s.")
END as total_count
FROM (
SELECT COUNT(Occupation) AS dCount,null as sCount, Lower(Occupation) AS Occupation
FROM OCCUPATIONS
WHERE Occupation = 'doctor'
group by Occupation
union
SELECT null as tst, COUNT(Occupation) AS sCount, Lower(Occupation) AS Occupation
FROM OCCUPATIONS
WHERE Occupation = 'singer'
group by Occupation
)
as tbl ;
基于我的演示的結果將是：
總數
總共有5名醫生。
共有7位歌手。
演示：https : //www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/28
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/322103.html
上一篇：洗掉錨標簽上的確認訊息
下一篇：更新資料透視表