现在有表格式如下:
我用sql 语句
select type,count(1) as count,sum(case when is_read = 2 then 1 else 0 end) as wd from mytable
where corp_id=5 group by type order by type asc
求出的结果如下:
因为数据里面到时候type 分别有很多,例如type 从 1-8的,我想用一条sql 能整出来所有包括type为1-8
的类型的所有数据,上图因为数据库中只有1,2,4,如果我要把剩下没有的3,5,6,7,8都虚拟一个
查出来,没有的,就用0替代,这样的sql怎么写呢,请大神指点。
例如变成:
type count wd
1 2 1
2 2 1
3 0 0
4 2 1
5 0 0
6 0 0
7 0 0
8 0 0
我用sql 语句
select type,count(1) as count,sum(case when is_read = 2 then 1 else 0 end) as wd from mytable
where corp_id=5 group by type order by type asc
求出的结果如下:
因为数据里面到时候type 分别有很多,例如type 从 1-8的,我想用一条sql 能整出来所有包括type为1-8
的类型的所有数据,上图因为数据库中只有1,2,4,如果我要把剩下没有的3,5,6,7,8都虚拟一个
查出来,没有的,就用0替代,这样的sql怎么写呢,请大神指点。
例如变成:
type count wd
1 2 1
2 2 1
3 0 0
4 2 1
5 0 0
6 0 0
7 0 0
8 0 0
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([type] int,[count] int,[wd] int)
Insert #T
select 1,1,1 union all
select 2,2,2 union all
select 4,2,1
Go
--测试数据结束
SELECT sv.number AS [type],
ISNULL(count,0) AS count,
ISNULL(wd,0) AS wd
FROM [master].dbo.spt_values sv
LEFT JOIN #T
ON number = #T.type
WHERE sv.[type] = 'P'
AND sv.number BETWEEN 1 AND 8;
我用的是mysql 哦,这个写法不行吧
我用的是mysql 哦,这个写法不行吧
那你发到mssql版,不发mysql。方法想法样,实现方式换换
我用的是mysql 哦,这个写法不行吧
那你发到mssql版,不发mysql。方法想法样,实现方式换换不好意思,是发错了,我转回到mysql板块了
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([type] int,[count] int,[wd] int)
Insert #T
select 1,1,1 union all
select 2,2,2 union all
select 4,2,1
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([type] int)
Insert #T1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8
Go
--测试数据结束
SELECT #T1.[type],
ISNULL(count,0) AS count,
ISNULL(wd,0) AS wd
FROM #T1
LEFT JOIN #T
ON #T1.[type] = #T.type
SELECT T1.A,IFNULL(T2.B,0) FROM (
SELECT 1 A FROM DUAL UNION ALL
SELECT 2 A FROM DUAL UNION ALL
SELECT 3 A FROM DUAL UNION ALL
SELECT 4 A FROM DUAL UNION ALL
SELECT 5 A FROM DUAL ) T1
LEFT JOIN
(
SELECT 1 A,11 B FROM DUAL UNION ALL
SELECT 2 A,22 B FROM DUAL UNION ALL
SELECT 3 A,33 B FROM DUAL ) T2 ON T2.A=T1.A ;
LEFT JOIN
(SELECT t2.`type`,COUNT(1) AS `count` FROM mytalbe t2 GROUP BY t2.`type`) t21
ON t11.type=t21.type;至于你的is_read字段 统计到这里面不合理吗?
WHEN 2 THEN 1
ELSE 0
END) AS wd
FROM mytable
GROUP BY type
UNION
SELECT *
FROM (
SELECT 1 AS type, 0 AS counts, 0 AS wd
UNION
SELECT 2, 0, 0
UNION
SELECT 3, 0, 0
UNION
SELECT 4, 0, 0
UNION
SELECT 5, 0, 0
UNION
SELECT 6, 0, 0
UNION
SELECT 7, 0, 0
UNION
SELECT 8, 0, 0
) t1
WHERE type NOT IN (
SELECT DISTINCT type
FROM mytable
)) t2 order by type;