有一张表 二个字段 (表数据如下)
区域(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查询出这样的结果呢
区域(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查询出这样的结果呢
解决方案 »
- 简单Update语句,但是效率忽快忽慢~~
- 求SQL语句
- SQL求差語句:想建立一個視圖為每條記錄与前一記錄的差値
- 这样的能不能通过一条语句完成
- 主机64位 win7 但virtualbox是32位win7
- 数据导入时出现的问题
- 怎样修改ORACLE的internal的密码为其它或者失效?
- 如何将FRXPRO的数据倒入到ORALCE中
- 问高人存储过程每月执行一次,运用的是带参数的过程如何更改。特急!
- 在写动态SQL时,DBMS_SQL.COLUMN_VALUET和DBMS_SQL.VARIALBE_VALUE有什么区别和联系?
- 电商用什么数据比较好
- oracle在配置Data Guard时,复制数据库时报ORA-12541: TNS: 无监听程序错误,求大神指点迷津
from t
group by name, type
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;
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
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;有重复数据这么写试试
结果再与编码表外连接
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;
from t
group by name, type
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;
我验证了一下上面的确实能查出想要的数据但不是依据表查出来的
=================
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')
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')
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