0

## 關於Oracle資料庫語法!!(Count)

qhair1234 iT邦新手 5 級 ‧ 2016-03-17 09:15:48 檢舉

qhair1234 iT邦新手 5 級 ‧ 2016-03-18 10:00:18 檢舉

### 1 個回答

8

iT邦超人 1 級 ‧ 2016-03-16 21:34:46

``````&lt;pre class="c" name="code">Where (DECODE(Field1, 'Y', 1, 0) +
DECODE(Field2, 'Y', 1, 0) +
DECODE(Field3, 'Y', 1, 0) +
DECODE(Field4, 'Y', 1, 0) +
DECODE(Field5, 'Y', 1, 0)) > 1
``````

qhair1234 iT邦新手 5 級 ‧ 2016-03-17 09:14:30 檢舉

count(case when ((DECODE(Field1, 'Y', 1, 0) + DECODE(Field2, 'Y', 1, 0))>1 then 1 end) as YY_Total

Select COUNT(*)
From Table1
Where (DECODE(Field1, 'Y', 1, 0) +
DECODE(Field2, 'Y', 1, 0) +
DECODE(Field3, 'Y', 1, 0) +
DECODE(Field4, 'Y', 1, 0) +
DECODE(Field5, 'Y', 1, 0)) > 1

``````&lt;pre class="c" name="code">Select COUNT(*)
From Table1
Where (DECODE(Field1, 'Y', 1, 0) +
DECODE(Field2, 'Y', 1, 0) +
DECODE(Field3, 'Y', 1, 0) +
DECODE(Field4, 'Y', 1, 0) +
DECODE(Field5, 'Y', 1, 0)) > 1
``````
qhair1234 iT邦新手 5 級 ‧ 2016-03-17 10:32:00 檢舉

simon大大 很謝謝你的教學&幫助，但是可以告訴我為何我的想法與case when語法 是不行的嗎??

qhair1234 iT邦新手 5 級 ‧ 2016-03-17 10:34:26 檢舉

``````&lt;pre class="c" name="code">create table ithelp160317a(
id int not null
,  col1 char(1) not null
,  col2 char(1) not null
,  col3 char(1) not null
,  col4 char(1) not null
,  col5 char(1) not null
);

insert all
into ithelp160317a values(1, 'N', 'N','Y','N','N')
into ithelp160317a values(2, 'Y', 'N','N','N','N')
into ithelp160317a values(3, 'Y', 'N','Y','N','N')
into ithelp160317a values(4, 'Y', 'N','N','N','N')
SELECT 1
FROM DUAL;

--
SELECT id
FROM ithelp160317a
WHERE length(replace(col1 || col2 || col3 || col4 || col5, 'N', '')) > 1;

ID
----------
3

SELECT count(1)
FROM ithelp160317a
WHERE length(replace(col1 || col2 || col3 || col4 || col5, 'N', '')) > 1;

COUNT(1)
----------
1
``````

Wow...小雨大太專業了...

qhair1234提到：

qhair1234 iT邦新手 5 級 ‧ 2016-03-17 11:35:40 檢舉

union 那部分,你再發問,把整個問題描述清楚.

UNION是把多個獨立的SELECT聯集。

``````&lt;pre class="c" name="code">SELECT COUNT(1) 總數 FROM TABLE1 WHERE ....
UNION
SELECT COUNT(1) 總數 FROM TABLE2 WHERE ....
UNION
SELECT COUNT(1) 總數 FROM TABLE3 WHERE ....
UNION
....
``````

oracle 用 count(*) 是比較好的,

count(*), 與 * 實際操作上不同!
count(*) 就是都要count,指定欄位反而不好,手殘挑到沒index的....

qhair1234 iT邦新手 5 級 ‧ 2016-03-17 11:54:13 檢舉