在线等,关于mysql语句转sqlite语句的问题

[aweinet40]

SELECT a1.FileID AS FileID, SUM( a1.Count + a2.Count + a3.Count + a4.Count ) AS Count
FROM gmpTableWordList AS a1
INNER JOIN gmpTableWordList AS a2
INNER JOIN gmpTableWordList AS a3
INNER JOIN gmpTableWordList AS a4 ON a1.FileID = a2.FileID
AND a2.FileID = a3.FileID
AND a3.FileID = a4.FileID
AND a1.Word = "Tempest"
AND a2.Word = "EMC"
AND a3.Word = "shielding"
AND a4.Word = "glass"
GROUP BY FileID
ORDER BY Count DESC 
LIMIT 0 , 30

这在MYSQL下能顺利执行，但在SQLite下不行，搜索了一下相关文档，应该是sqlite不支持这种查询方式。请高手帮帮忙，把以上代码转成sqlite能执行的语句，其中a1,a2,a3.....的多少是跟据Word的多少来决定的，目前只是Tempest,EMC,shielding,glass.谢谢了！在线等.

[ACMAIN_CHM]

应该支持啊。你的表 gmpTableWordList 的建表语句是什么？贴出来大家可以测试一下。

[WWWWA]

提示什么

[aweinet40]

[Quote=引用 1 楼 acmain_chm 的回复:]
应该支持啊。你的表 gmpTableWordList 的建表语句是什么？贴出来大家可以测试一下。
[/Quote]
建表语句是这个：

CREATE TABLE gmpTableWordList (Word varchar(50) NOT NULL, FileID int(10) NOT NULL, Count INTEGER UNSIGNED)") or die("创建表失败！");

[aweinet40]

[Quote=引用 2 楼 wwwwa 的回复:]
提示什么
[/Quote]
sqlite版本应该是2.8的。我在sqlitemanager中运行的，提示是：

Error 1 :	SQL logic error or missing database
 	ambiguous column name: Count

[WWWWA]

提示什么信息？SQLIT3支持LIMIT

[aweinet40]

当时怀疑过Count是sqlite的保留字，但其它查询也是这样查的，没问题呢。就这句有问题。

[ACMAIN_CHM]

你的语句SQLite 中测试没有任何问题。

sqlite> CREATE TABLE gmpTableWordList (Word varchar(50) NOT NULL, FileID int(10) NOT NULL, Count INTEGER UNSIGNED);
sqlite> SELECT a1.FileID AS FileID, SUM( a1.Count + a2.Count + a3.Count + a4.Count ) AS Count
   ...> FROM gmpTableWordList AS a1
   ...> INNER JOIN gmpTableWordList AS a2
   ...> INNER JOIN gmpTableWordList AS a3
   ...> INNER JOIN gmpTableWordList AS a4 ON a1.FileID = a2.FileID
   ...> AND a2.FileID = a3.FileID
   ...> AND a3.FileID = a4.FileID
   ...> AND a1.Word = "Tempest"
   ...> AND a2.Word = "EMC"
   ...> AND a3.Word = "shielding"
   ...> AND a4.Word = "glass"
   ...> GROUP BY FileID
   ...> ORDER BY Count DESC
   ...> LIMIT 0 , 30;
sqlite>

[ACMAIN_CHM]

象楼上一样，贴出你的错误信息。

[aweinet40]

[Quote=引用 7 楼 acmain_chm 的回复:]
你的语句SQLite 中测试没有任何问题。

SQL codesqlite>CREATETABLE gmpTableWordList (Wordvarchar(50)NOTNULL, FileIDint(10)NOTNULL,CountINTEGER UNSIGNED);
sqlite>SELECT a1.FileIDAS FileID,SUM( a1.Count+ a2.Count+ a3.Count+ a4.Count )ASCount
...>FROM gmpTableWordListAS a1
...>INNERJOIN gmpTableWordListAS a2
...>INNERJOIN gmpTableWordListAS a3
...>INNERJOIN gmpTableWordListAS a4ON a1.FileID= a2.FileID
...>AND a2.FileID= a3.FileID
...>AND a3.FileID= a4.FileID
...>AND a1.Word= "Tempest"
...>AND a2.Word= "EMC"
...>AND a3.Word= "shielding"
...>AND a4.Word= "glass"
...>GROUPBY FileID
...>ORDERBYCountDESC
...> LIMIT0 ,30;
sqlite>
[/Quote]


你的SQLite是什么版本啊，我的是用PHP创建的，应该是2.8的版本吧。

[WWWWA]

SELECT a1.FileID AS FileID, SUM( a1.Count + a2.Count + a3.Count + a4.Count ) AS Count
FROM gmpTableWordList AS a1
INNER JOIN gmpTableWordList AS a2
INNER JOIN gmpTableWordList AS a3
INNER JOIN gmpTableWordList AS a4 ON a1.FileID = a2.FileID
AND a2.FileID = a3.FileID
AND a3.FileID = a4.FileID
AND a1.Word = "Tempest"
AND a2.Word = "EMC"
AND a3.Word = "shielding"
AND a4.Word = "glass"
GROUP BY FileID &&什么表的字段
ORDER BY Count DESC &&修改为2
LIMIT 0 , 30

[ACMAIN_CHM]

改成这个

SELECT a1.FileID AS FileID, SUM( a1.Count + a2.Count + a3.Count + a4.Count ) AS Count
FROM gmpTableWordList AS a1
	INNER JOIN gmpTableWordList AS a2
	INNER JOIN gmpTableWordList AS a3
	INNER JOIN gmpTableWordList AS a4 ON a1.FileID = a2.FileID
	AND a2.FileID = a3.FileID
	AND a3.FileID = a4.FileID
	AND a1.Word = "Tempest"
	AND a2.Word = "EMC"
	AND a3.Word = "shielding"
	AND a4.Word = "glass"
GROUP BY a1.FileID
ORDER BY 2 DESC
LIMIT 0 , 30;

[aweinet40]

[Quote=引用 11 楼 acmain_chm 的回复:]
改成这个


SQL codeSELECT a1.FileIDAS FileID,SUM( a1.Count+ a2.Count+ a3.Count+ a4.Count )ASCountFROM gmpTableWordListAS a1INNERJOIN gmpTableWordListAS a2INNERJOIN gmpTableWordListAS a3INNERJOIN gmpTableWordListAS a4ON a1.FileID= a2.FileIDAND a2.FileID= a3.FileIDAND a3.FileID= a4.FileIDAND a1.Word= "Tempest"AND a2.Word= "EMC"AND a3.Word= "shielding"AND a4.Word= "glass"GROUPBY a1.FileIDORDERBY2DESC
LIMIT0 ,30;
[/Quote]
有效果！不过搜索的结果不太合适呢，内容里明明有的但搜索无结果。

[ACMAIN_CHM]

[Quote]不过搜索的结果不太合适呢，内容里明明有的但搜索无结果。[/Quote]

提供一个测试用记录，也就是应该有但没有搜索出来的哪几条记录。

[WWWWA]

[Quote=引用 12 楼 aweinet40 的回复:]
引用 11 楼 acmain_chm 的回复:
改成这个


SQL codeSELECT a1.FileIDAS FileID,SUM( a1.Count+ a2.Count+ a3.Count+ a4.Count )ASCountFROM gmpTableWordListAS a1INNERJOIN gmpTableWordListAS a2INNERJOIN gmpTableWordListAS a3INNERJOIN gmpTableWordListAS a4ON a1.FileID= a2.FileIDAND a2.FileID= a3.FileIDAND a3.FileID= a4.FileIDAND a1.Word= "Tempest"AND a2.Word= "EMC"AND a3.Word= "shielding"AND a4.Word= "glass"GROUPBY a1.FileIDORDERBY2DESC
LIMIT0 ,30;

有效果！不过搜索的结果不太合适呢，内容里明明有的但搜索无结果。
[/Quote]
贴记录及要求结果出来看看，呵呵

[aweinet40]

这样的结果我已经很满意了，可能是我库里边真没这个内容，多试了几个，暂时没什么问题，如有我再来请教大家了。这分怎么给呢，两位都这么热心，每人20分吧。

[aweinet40]

最终找到了问题的原因：sqlite和mysql在对待大小写上有区别。我把搜索关键字用strtolower转一下，结果就完全正常了！