Question

多表一起查询时的排序问题,sql高手请入

//User_Value,User_Nums,User_DateTime 这3个表的 UserID 与 User_Name.UserID 同步
//

//查询虚拟逻辑条件为: 是否可以发布信息 = 'true' AND 用户金钱 >= 1
string SQL_查找符合条件的数据 = @"
SELECT User_Name.UserID
FROM

(((User_Name
LEFT JOIN User_Value ON User_Value.UserID = User_Name.UserID)
LEFT JOIN User_Nums ON User_Nums.UserID = User_Name.UserID)
LEFT JOIN User_DateTime ON User_DateTime.UserID = User_Name.UserID)

WHERE (User_Value.TypeID = 7 AND User_Value.Value = 'true')
AND (User_Nums.TypeID = 3 AND User_Nums.Nums >= 0)

GROUP BY User_Name.UserID
";

//查询上述结果并且按照虚拟逻辑排序条件: ORDER BY 注册时间 DESC
string SQL_查找符合条件的数据_并排序 = @"

SELECT User_DateTime.UserID FROM
(
SELECT User_Name.UserID AS 符合条件的用户ID
FROM

(((User_Name
LEFT JOIN User_Value ON User_Value.UserID = User_Name.UserID)
LEFT JOIN User_Nums ON User_Nums.UserID = User_Name.UserID)
LEFT JOIN User_DateTime ON User_DateTime.UserID = User_Name.UserID)

WHERE (User_Value.TypeID = 7 AND User_Value.Value = 'true')
AND (User_Nums.TypeID = 3 AND User_Nums.Nums >= 0)

GROUP BY User_Name.UserID
)
,User_DateTime
WHERE User_DateTime.UserID = 符合条件的用户ID
AND User_DateTime.TypeID = 8
ORDER BY User_DateTime.DateTime DESC

";

//主要想实现根据(User_Value,User_Nums,User_DateTime).TypeID = 某ID 的行 (User_Value|User_Nums|User_DateTime).(Value|Nums|DateTime) = 'XXX' 进行查询
//并且根据(User_Value,User_Nums,User_DateTime).TypeID = 某ID 的行 (User_Value|User_Nums|User_DateTime).(Value|Nums|DateTime) = 'XXX' 进行排序
//User_Name 的数据并不一定具有所有的(User_Value,User_Nums,User_DateTime)的值,就是并不对应

//上述语句已经可以实现根据某些字段进行排序的逻辑,但是效率奇差,10万行数据(User_Value,User_Nums,User_DateTime),至少要运行10秒以上
//本屌丝做了一些小测试,猜测主要是因为数据的行数过多(User_Value,User_Nums,User_DateTime),在 LEFT JOIN 后判断每行的数据 (User_Value.TypeID = 7 AND User_Value.Value = 'true') 以及 (User_Nums.TypeID = 3 AND User_Nums.Nums >= 0) 比较浪费时间
//并且 GROUP BY User_Name.UserID 这个分组也挺耗时
//本屌SQL语句纯属小白级别,有情各位大神帮忙修改一下SQL语句
//ACCESS数据库.........

Answer 1

大哥，你的用户表怎么连个主键都没有呢，建议你加个主键吧！

追问

有的,UserID 这个是