create table t1 (c1 int,c2 int,c3 int);
|
insert into t1 values (1,3,1),(2,1,1),(3,0,1),(4,0,1),(5,0,1),(6,0,1);
|
|
MariaDB [test]> SELECT count(*) cnt,
|
-> count(*)/((SELECT count(*) FROM t1 b WHERE b.c3 = a.c3)/100) "%"
|
-> FROM t1 a WHERE c1 < 5 GROUP BY c3;
|
+-----+---------+
|
| cnt | % |
|
+-----+---------+
|
| 4 | 66.6667 |
|
+-----+---------+
|
1 row in set (0.01 sec)
|
|
MariaDB [test]> create view v1 as
|
-> SELECT count(*) cnt,
|
-> count(*)/((SELECT count(*) FROM t1 b WHERE b.c3 = a.c3)/100) "%"
|
-> FROM t1 a WHERE c1 < 5 GROUP BY c3;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [test]> select * from v1 ;
|
+-----+------------+
|
| cnt | % |
|
+-----+------------+
|
| 4 | 0.00666700 |
|
+-----+------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> show create view v1;
|
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
| View | Create View | character_set_client | collation_connection |
|
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select count(0) AS `cnt`,count(0) / (select count(0) from `t1` `b` where `b`.`c3` = `a`.`c3`) / 100 AS `%` from `t1` `a` where `a`.`c1` < 5 group by `a`.`c3` | utf8 | utf8_general_ci |
|
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
1 row in set (0.00 sec)
|