请教一个关于视图的问题

[wasuka]

无限级分类表结构（“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`



查到语句被修改了，导致结果不正确。请教如何实现上述功能？

[wasuka]

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

谢谢撒

[懒得去死]

不知道你的系统是什么？

还有MYSQL的版本是什么?

这个我系统上的测试：
MYSQL5.1
WINDOWS: VISTA

SET 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 

[懒得去死]

两个结果是一样的。

[wasuka]

XindowsXP

mysql> SELECT VERSION();
+---------------------+
| VERSION() |
+---------------------+
| 5.0.27-community-nt |
+---------------------+


REPLACE(tr, '_', '\_')，里面的参数没有空格

你SHOW CREATE VIEW一下，看看SQL被修改成什么样了？

[wasuka]

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

[懒得去死]

LZ。是你说你的结果不正确的啊

[yifuzhiming]

晕晕忽忽