有一張表 二個欄位 （表資料如下）
區域(name) 注冊型別(type)
A區 1
B區 2
C區 3
假設存在3個區，有三種注冊型別，想查詢出來的預想結果為
區域 注冊型別 統計量
A區 1 1
A區 2 0
A區 3 0
B區 1 0
B區 2 1
B區 3 0
C區 1 0
C區 2 0
C區 3 1
如果通過SQL查詢出這樣的結果呢
uj5u.com熱心網友回復：
select name, type,count(*)
from t
group by name, type
uj5u.com熱心網友回復：
這樣查詢的得不到統計量為0的結果呢uj5u.com熱心網友回復：
with t as (
select 'A區' name,1 type from dual union all
select 'B區' name,2 type from dual union all
select 'C區' name,3 type from dual)
select a.name, b.type,count(case when a.type=b.type then 1 end) aaa
from t a,t b
group by a.name, b.type
order by 1,2;
uj5u.com熱心網友回復：
with t as (
select 'A' as name1,1 as type1 from dual
union all
select 'B' as name1,2 as type1 from dual
union all
select 'C' as name1,3 as type1 from dual
)
select a.name1,b.type1,sum(decode(b.type1,a.type1,1,0))
from t a ,t b
group by a.name1,b.type1
order by a.name1,b.type1
uj5u.com熱心網友回復：
資料是對的，但是記錄數不對， select AAB301 ,REGTYPE ,count(0) as value from wsbs_person group by(AAB301,REGTYPE) 這是我寫的統計
uj5u.com熱心網友回復：
學習 學習，謝謝uj5u.com熱心網友回復：
with t as (
select 'A區' name,1 type from dual union all
select 'A區' name,1 type from dual union all
select 'A區' name,1 type from dual union all
select 'A區' name,1 type from dual union all
select 'B區' name,2 type from dual union all
select 'B區' name,2 type from dual union all
select 'B區' name,2 type from dual union all
select 'C區' name,3 type from dual)
select a.name, b.type,count(case when a.type=b.type then 1 end) aaa
from (select distinct * from t) a,t b
group by a.name, b.type
order by 1,2;
有重復資料這么寫試試
uj5u.com熱心網友回復：
如果實際業務中是編碼表+資料表（前面給的表），應該是資料表先直接GROUP BY結果再與編碼表外連接
uj5u.com熱心網友回復：
with t as (select 'A區' name,1 type from dual union all
select 'B區' name,2 type from dual union all
select 'C區' name,3 type from dual)
select name, a.type, nvl2(b.type, 1, 0)
from (select distinct type from t) a
left join t b partition by(b.name)
on a.type = b.type;
uj5u.com熱心網友回復：
還是沒找到合適的SQL，感覺有點難通過 一個SQL搞定，區域是動態的，
uj5u.com熱心網友回復：
沒明白你啥意思，加我Q：212646490
uj5u.com熱心網友回復：
select name, type,count(*)from t
group by name, type
uj5u.com熱心網友回復：
我驗證了一下上面的確實能查出想要的資料但不是依據表查出來的
with t as (
select 'A區' name,1 type from dual union all
select 'B區' name,2 type from dual union all
select 'C區' name,3 type from dual)
select a.name, b.type,count(case when a.type=b.type then 1 end) aaa
from t a,t b
group by a.name, b.type
order by 1,2;
=================
uj5u.com熱心網友回復：
這樣應該可以：
select name,type, count(*) from t group by name,type
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '1')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '2')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '3')
UNION ALL
SELECT 'B區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'B區' AND TYPE = '1')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '2')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '3')
UNION ALL
SELECT 'C區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'C區' AND TYPE = '1')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '2')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '3')
uj5u.com熱心網友回復：
額，前面有點錯
select name,type, count(*) from t group by name,type
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '1')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '2')
UNION ALL
SELECT 'A區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'A區' AND TYPE = '3')
UNION ALL
SELECT 'B區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'B區' AND TYPE = '1')
UNION ALL
SELECT 'B區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'B區' AND TYPE = '2')
UNION ALL
SELECT 'B區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'B區' AND TYPE = '3')
UNION ALL
SELECT 'C區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'C區' AND TYPE = '1')
UNION ALL
SELECT 'C區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'C區' AND TYPE = '2')
UNION ALL
SELECT 'C區',0 FROM DUAL WHERE 0 = (SELECT COUNT(*) FROM t WHERE name= 'C區' AND TYPE = '3')
uj5u.com熱心網友回復：
--假設資料如下：with t as (
select 'A區' name,1 type from dual union all
select 'B區' name,2 type from dual union all
select 'C區' name,3 type from dual)
--以下是查詢陳述句
SELECT a.NAME, b.TYPE, SUM(CASE WHEN a.TYPE=b.TYPE THEN 1 ELSE 0 END) TYPE_SUM
FROM t a
CROSS JOIN t b
GROUP BY a.NAME, b.TYPE
ORDER BY a.NAME
uj5u.com熱心網友回復：
0％
uj5u.com熱心網友回復：
我也覺得實際業務中應該是這樣，或者沒有的話，也應該按照這個思路，先select distinct出臨時的編碼表，再外關聯，別人閱讀SQL時會好理解一點
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/87763.html
標籤：開發
上一篇：通過何種方式可以 獲取 SQL中的select 的欄位
下一篇：oracle行轉列SQL