请教一个关于视图的问题

Question

无限级分类表结构（“PID”是分类的父级分类的ID，“tree”字段是分类的等级顺序）：CREATE TABLE `category`
(
  `ID` smallint(5) unsigned NOT NULL auto_increment,
  `PID` smallint(5) unsigned NOT NULL default '0',
  `tree` text collate utf8_unicode_ci,
  `name` varchar(20) collate utf8_unicode_ci NOT NULL default '',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `PID` (`PID`,`name`),
  KEY `tree` (`tree`(200))
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;mysql> SELECT ID, PID, tree, name FROM category;
+----+-----+----------+--------+
| ID | PID | tree     | name   |
+----+-----+----------+--------+
|  1 |   0 | 1_       | 新闻   |
|  2 |   1 | 1_2_     | 国内   |
|  3 |   1 | 1_3_     | 国际   |
|  4 |   2 | 1_2_4_   | 北京   |
|  5 |   4 | 1_2_4_5_ | 朝阳区 |
|  6 |   2 | 1_2_6_   | 上海   |
|  7 |   3 | 1_3_7_   | 美国   |
+----+-----+----------+--------+为了统计分类的子分类的数量“SubCategories”，写SQL如下：mysql> SELECT ID, PID, name, tree AS tr, (SELECT COUNT(*) FROM category WHERE tree LIKE CONCAT(REPLACE(tr, '_', '\_'), '%'))-1 AS SubCategories FROM category;
+----+-----+--------+----------+---------------+
| ID | PID | name   | tr       | SubCategories |
+----+-----+--------+----------+---------------+
|  1 |   0 | 新闻   | 1_       |             6 |
|  2 |   1 | 国内   | 1_2_     |             3 |
|  3 |   1 | 国际   | 1_3_     |             1 |
|  4 |   2 | 北京   | 1_2_4_   |             1 |
|  5 |   4 | 朝阳区 | 1_2_4_5_ |             0 |
|  6 |   2 | 上海   | 1_2_6_   |             0 |
|  7 |   3 | 美国   | 1_3_7_   |             0 |
+----+-----+--------+----------+---------------+为这个功能建立一个视图：CREATE VIEW view_category AS SELECT ID, name, tree AS `tr`, (SELECT COUNT(*) FROM category WHERE tree LIKE CONCAT(REPLACE `tr`, '_', '\_'), '%')) AS SubCategories FROM category;视图结果：mysql> SELECT * FROM view_category;
+----+--------+----------+---------------+
| ID | name   | tr       | SubCategories |
+----+--------+----------+---------------+
|  1 | 新闻   | 1_       |             7 |
|  2 | 国内   | 1_2_     |             7 |
|  3 | 国际   | 1_3_     |             7 |
|  4 | 北京   | 1_2_4_   |             7 |
|  5 | 朝阳区 | 1_2_4_5_ |             7 |
|  6 | 上海   | 1_2_6_   |             7 |
|  7 | 美国   | 1_3_7_   |             7 |
+----+--------+----------+---------------+结果不正确，检查视图：mysql> SHOW CREATE VIEW view_category\G
*************************** 1. row ***************************
       View: view_category
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_category` AS select `category`.`ID` AS `ID`,`category`.`name` AS `name`,`category`.`tree` AS `tr`,(select count(0) AS `COUNT(*)` from `category` where (`category`.`tree` like concat(replace(`category`.`tree`,_utf8'_',_utf8'\\_'),_utf8'%'))) AS `SubCategories` from `category`查到语句被修改了，导致结果不正确。请教如何实现上述功能？

Answer 1

不知道你的系统是什么？还有MYSQL的版本是什么?这个我系统上的测试：
MYSQL5.1
WINDOWS: VISTASET NAMES GBK;
CREATE   TABLE   `category`
(
    `ID`   smallint(5)   unsigned   NOT   NULL   auto_increment,
    `PID`   smallint(5)   unsigned   NOT   NULL   default   '0 ',
    `tree`   text   collate   utf8_unicode_ci,
    `name`   varchar(20)   collate   utf8_unicode_ci   NOT   NULL   default   ' ',
    PRIMARY   KEY     (`ID`),
    UNIQUE   KEY   `PID`   (`PID`,`name`),
    KEY   `tree`   (`tree`(200))
)
ENGINE=InnoDB   DEFAULT   CHARSET=utf8   COLLATE=utf8_unicode_ci;
insert into category(pid,tree,name) values
(0,'1_','新闻'),
(1,'1_2_','国内'),
(1,'1_3_','国际'),
(2,'1_2_4_','北京'),
(4,'1_2_4_5_','朝阳区'),
(2,'1_2_6_','上海'),
(3,'1_3_7_','美国');SELECT ID, PID, name, tree AS tr,
(
SELECT COUNT(*) FROM category WHERE tree LIKE CONCAT(REPLACE(tr, '_ ', '\_ '), '% ')
)-1 AS SubCategories FROM category;结果：
query result(7 records)
ID PID name tr SubCategories 
1 0 新闻 1_ -1 
2 1 国内 1_2_ -1 
3 1 国际 1_3_ -1 
4 2 北京 1_2_4_ -1 
5 4 朝阳区 1_2_4_5_ -1 
6 2 上海 1_2_6_ -1 
7 3 美国 1_3_7_ -1 
CREATE VIEW view_category AS
SELECT ID, PID, name, tree AS tr,
(
SELECT COUNT(*) FROM category WHERE tree LIKE CONCAT(REPLACE(tr, '_ ', '\_ '), '% ')
)-1 AS SubCategories FROM category;select * from view_category;结果：query result(7 records)
ID PID name tr SubCategories 
1 0 新闻 1_ -1 
2 1 国内 1_2_ -1 
3 1 国际 1_3_ -1 
4 2 北京 1_2_4_ -1 
5 4 朝阳区 1_2_4_5_ -1 
6 2 上海 1_2_6_ -1 
7 3 美国 1_3_7_ -1 

Answer 2

XindowsXPmysql> SELECT VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 5.0.27-community-nt |
+---------------------+
REPLACE(tr, '_', '\_')，里面的参数没有空格你SHOW CREATE VIEW一下，看看SQL被修改成什么样了？

Answer 3

你单独执行SQL结果不正确的原因是REPLACE()的参数多了空格

Answer 4

我的意思是同样的SQL，直接执行的结果是正确的，放在视图里就不对。你可以看一下我原帖里面的结果对比。谢谢撒