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.谢谢了!在线等.
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.谢谢了!在线等.
CREATE TABLE gmpTableWordList (Word varchar(50) NOT NULL, FileID int(10) NOT NULL, Count INTEGER UNSIGNED)") or die("创建表失败!");
Error 1 : SQL logic error or missing database
ambiguous column name: Count
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>
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
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;