我有一個大寫字母和首字母縮寫的名字串列。我已經洗掉了 initails 并洗掉了大寫字母，但 distinct 仍然顯示它們是分開的（最好去掉名稱末尾的 s。
SQL甲骨文
SELECT DISTINCT (upper
( REGEXP_SUBSTR(NAME, '(\S )$'))) as NAMEZ,
count(case when daysout is NULL then 1 else null end) as home,
count(case when daysout is NOT NULL then 1 else null end) as away,
count(*) as daystotal
FROM peoplein
所以我從中得到的是
johns
johns
john
Jack
jack
在我以前得到的 reg 和 upper 之前
Johns
JOHNS
John
Jack
JACK
我只想讓他們成為
john
jack
謝謝您的幫助
uj5u.com熱心網友回復：
DISTINCT不是函式；它是適用于所有列的關鍵字。在這種情況下，您需要一個GROUP BY子句而不是使用DISTINCT：
SELECT UPPER( REGEXP_SUBSTR(NAME, '(\s|^)(\S ?)s?$', 1, 1, NULL, 2)) as name,
COUNT(case when daysout is NULL then 1 else null end) as home,
COUNT(case when daysout is NOT NULL then 1 else null end) as away,
COUNT(*) as daystotal
FROM peoplein
GROUP BY
UPPER( REGEXP_SUBSTR(NAME, '(\s|^)(\S ?)s?$', 1, 1, NULL, 2))
或者，使用簡單（更快）的字串函式：
SELECT TRIM(
TRAILING 'S' FROM
UPPER( SUBSTR(NAME, INSTR(NAME, ' ', -1) 1) )
) as name,
COUNT(case when daysout is NULL then 1 else null end) as home,
COUNT(case when daysout is NOT NULL then 1 else null end) as away,
COUNT(*) as daystotal
FROM peoplein
GROUP BY
TRIM(
TRAILING 'S' FROM
UPPER( SUBSTR(NAME, INSTR(NAME, ' ', -1) 1) )
)
（雖然后一個查詢會S從末尾洗掉多個字符；但是，這是可以修復的。）
其中，對于樣本資料：
CREATE TABLE peoplein(name, daysout) AS
SELECT 'Abbot Alice', NULL FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 'Abbot Alice', 1 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 'Baron Betty', 1 FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT 'Count Chris', NULL FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT 'Count Chris', 1 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'Doris', 1 FROM DUAL CONNECT BY LEVEL <= 6;
兩種輸出：
| 姓名 | 家 | 離開 | 總計 |
|---|---|---|---|
| 愛麗絲 | 3個 | 2個 | 5個 |
| 貝蒂 | 0 | 4個 | 4個 |
| 中國人權倡議 | 5個 | 1個 | 6個 |
| 多麗 | 0 | 6個 | 6個 |
注意：您確定S要從某個人的名字中洗掉結尾嗎？
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/533989.html
標籤：数据库甲骨文