想了许久无解的问题：一条SELECT语句能否搞定查询和统计？

Question

有两个表tb_Goods和tb_GoodsCategoryCREATE TABLE tb_Goods(
    GoodsID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   -- 商品标识
    GoodsName VARCHAR(200) NOT NULL DEFAULT '', -- *商品名称
    PRIMARY KEY (GoodsID),    
    UNIQUE KEY(GoodsName)
)ENGINE=InnoDB DEFAULT CHARSET=gb2312;+---------+-------------------+
| GoodsID | GoodsName         |
+---------+-------------------+
|       1 | 设计模式          |
|       2 | 设计思路整理      |
|       3 | 设计思路记录      |
|       4 | Ajax实战          |
|       5 | Ajax玩具          |
|       6 | 设计玩具          |
+---------+-------------------+
--R:商品所属分类信息表，一个商品可以属于多个分类
CREATE TABLE tb_GoodsCategory(
    GoodsID BIGINT UNSIGNED NOT NULL DEFAULT '0',   -- 商品标识
    CategoryID BIGINT UNSIGNED NOT NULL DEFAULT '0',  -- 所属分类
    PRIMARY KEY(GoodsID, CategoryID)
)ENGINE=InnoDB DEFAULT CHARSET=gb2312;+---------+-------------------+
| GoodsID | CategoryID        |
+---------+-------------------+
|       1 | 1                 |
|       2 | 1                 |
|       3 | 1                 |
|       4 | 1                 |
|       4 | 2                 |
|       5 | 2                 |
|       6 | 1                 |
+---------+-------------------+搜索GoodsName中含有‘Ajax’的商品，并统计在每个类别（CategoryID）搜到的记录数，例如输出结果大致为：+---------+-------------------+
| GoodsID | GoodsName         |
+---------+-------------------+
|       4 | Ajax实战          |
|       5 | Ajax玩具          |
+---------+-------------------+同时能得到分类1中有1个结果（商品4），分类2中有两个结果（商品4和5）。
能否用一条SELECT语句搞定？（可以使用用户变量）注意：
1、会使用GROUP BY GoodsID，因为有重复纪录
2、可能对GoodsID排序（升序和降序都可能）
3、同时结果要用LIMIT（因为记录数量多）

Answer 1

当然可以[align=center]==== 思想重于技巧 ====
[/align]

Answer 2

同时能得到分类1中有1个结果（商品4），分类2中有两个结果（商品4和5）。 怎么个同时法，建议你给出你想要的格式及结果。[align=center]==== 思想重于技巧 ====
[/align]

Answer 3

+---------+-------------------+-------+-------+ 
¦ GoodsID  ¦ GoodsName        ¦Count1 ¦Count2 ¦
+---------+-------------------+-------+-------+
¦       4  ¦ Ajax实战           ¦     1 ¦     2 ¦
¦       5  ¦ Ajax玩具           ¦     1 ¦     2 ¦
+---------+-------------------+-------+-------+ 
Count1是类别1搜到的记录数
Count2是类别2搜到的记录数Count1和Count2存放到变量也可以。

Answer 4

你一个产品，最多只在两类中？¦ GoodsID  ¦ GoodsName        ¦Count1 ¦Count2 ¦会不会出现count3,4,5?[align=center]==== 思想重于技巧 ====
[/align]

Answer 5

如果只有固定的分类，一个SQL尚可实现，如果无固定的上限，则一个SQL语句则难以实现。[align=center]==== 思想重于技巧 ====
[/align]

Answer 6

固定的上限 是 多少 ？ ¦ GoodsID  ¦ GoodsName        ¦Count1 ¦Count2 ¦  ¦Count3 ¦Count4 ¦Count5 ¦...  ¦CountN  ¦ [align=center]==== 思想重于技巧 ====
[/align]

Answer 7

select g.GoodsID,g.GoodsName,c1.Count1,c2.Count2
from tb_Goods as g ,
(select count(*) as Count1
from tb_GoodsCategory
where CategoryID=1
and GoodsID in (select GoodsID from GoodsName like '%Ajax%')
) as c1,
(select count(*) as Count2
from tb_GoodsCategory
where CategoryID=1
and GoodsID in (select GoodsID from GoodsName like '%Ajax%')
) as c2
where g.GoodsName like '%Ajax%'[align=center]==== 思想重于技巧 ====
[/align]

Answer 8

最多也就255个吧。顺便说下：对tb_Goods只能搜索一次，因为数据库记录可能上百万条，同时还有多个字段的字符查找，此处为简化省略掉了。
多谢了！

Answer 9

try:
select * ,
(
select count(*) from (
SELECT a.* from tta a left join ttb b on a.GoodsID=b.GoodsID
  where instr(GoodsName,'Ajax')>0 and b.CategoryID=1 )),
(
select count(*) from (
SELECT a.* from tta a left join ttb b on a.GoodsID=b.GoodsID
  where instr(GoodsName,'Ajax')>0 and b.CategoryID=2 ))
from tta where instr(GoodsName,'Ajax')>0

Answer 10

select *, 
(SELECT count(*) from tta a left join ttb b on a.GoodsID=b.GoodsID
  where instr(a.GoodsName,'Ajax')>0 and b.CategoryID=2),
(SELECT count(*) from tta a left join ttb b on a.GoodsID=b.GoodsID
  where instr(a.GoodsName,'Ajax')>0 and b.CategoryID=1)
from tta bbwhere instr(bb.GoodsName,'Ajax')>0

Answer 11

最多也就255个吧。顺便说下：对tb_Goods只能搜索一次，因为数据库记录可能上百万条，同时还有多个字段的字符查找，此处为简化省略掉了。  其实根本不建议你用一句SQL来实现，人为地制造复杂。 建议在程序中实现效率比较高，也比较有逻辑。 [align=center]==== 思想重于技巧 ====
[/align]

Answer 12

对tb_Goods只能搜索一次 倒不是问题，相同的SQL语句再第二次，几乎没有什么执行时间。 
但这种设计不好，只是可以满足一个SQL语句而已。[align=center]==== 思想重于技巧 ====
[/align]

Answer 13

另外你的结果，似乎可以优化一下，假设有10行，每行后面的统计数据都是相同的。 很少有这么显示的。你最终想得到的报表是什么样的？然后根据这个设计你的程序和查询[align=center]==== 思想重于技巧 ====
[/align]

Answer 14

Count1和Count2单独存放是可以的或单独成表都可以，其实我关心的是性能问题：在查询结果同样的情况下，如何高效实现，是不是一条SQL语句不是很重要。 

Answer 15

你用的什么开发环境？
ASP / PHP / VB.C / .NET ?其实建议两个SQLselect GoodsID, GoodsName
from tb_Goods
where GoodsName like '%Ajax%'这个查询因为用的 like, 注定效率不会太高，INSTR也一样，没什么优化的余地。输出结果的同时生成 IN 字符串 GoodsID + ',' + GoodsID ...
IN 字符串 = "4,5"执行SQL2
select CategoryID,count(*)
from tb_GoodsCategory
where GoodsID in IN 字符串
group by CategoryID当然你可以把IN 转成 OR或许会有效率上的略微提高。[align=center]==== 思想重于技巧 ====
[/align]

Answer 16

注意，还有LIMIT，因此得到的IN字符串不全。
看来只能使用临时表搞定了，好像MySQL中临时表要预先定义，不能使用一个变量存储，比较麻烦。

Answer 17

我的开发环境Eclipse+Servlet/JSP+MySQL

Answer 18

用TEMP 表应该也可以，不过我喜欢在程序中实现。数据库只做数据库该做的，业务和逻辑由程序来完成。 否则移植的时候比较烦。[align=center]==== 思想重于技巧 ====
[/align]

Answer 19

我的一个解决办法：
SET @count1=0;
SET @count2=0;
SELECT *,@count1:=@count1+(CategoryID=1),@count2:=@count2+(CategoryID=2) FROM tb_Goods,tb_GoodsCategory WHERE GoodsName LIKE 'Ajax' AND tb_GoodsCategory.GoodsID=tb_Goods.GoodsID GROUP BY tb_Goods.GoodsID ORDER BY tb_Goods.GoodsID LIMIT 0,1
此时输出查询结果
+---------+-------------------+ 
¦ GoodsID  ¦ GoodsName        ¦ 
+---------+-------------------+ 
¦       4  ¦ Ajax实战           ¦ 
¦       5  ¦ Ajax玩具           ¦ 
+---------+-------------------+ 
然后使用
SELECT @count1;和SELECT @count2;查询统计数据但是@count1和@count2取出的是SELECT...LIMIT语句查询结果中的最后一条记录的值，还是不对！

Answer 20

你主要想用LIMIT 实现什么？[align=center]==== 思想重于技巧 ====
[/align]