--trySELECT CompanyName FROM Company ORDER BY CASE WHEN EndedDate > CONVERT(CHAR(10), GETDATE(), 120) THEN 1 ELSE 0 END, (VoteA*2+VoteB*1+VoteD*(-1)+VoteE*(-2)) DESC
SELECT CompanyName FROM Company ORDER BY case when EndedDate>GETDATE() then 1 else 0 end --是否到期 ,VoteA desc,VoteB desc,VoteC desc,VoteD desc
SELECT CompanyName FROM Company ORDER BY case when EndedDate>GETDATE() then 1 else 0 end --是否到期 ,right(10000+VoteA,4) ---不超过4位数也可以这样写 +right(10000+VoteB,4) +right(10000+VoteC,4) +right(10000+VoteD,4) desc
多谢邹建,强烈支持您创办MS-SQL Server杂志!
VoteA - VoteE 字段值的大小要看投票的票数多少,一开始是0,最多可能增加到几百,不会超过一千吧。 另外,我想在SELECT语句中生成一个bit类型的字段BeMember (即根据到期日期判断是否是会员),是否可以这样写: SELECT CompanyName, case (when EndedDate>GETDATE() then 1 else 0 end ) AS BeMember FROM Company
按照SELECT CompanyName FROM Company ORDER BY case when EndedDate>GETDATE() then 1 else 0 end --是否到期 ,right(10000+VoteA,4) ---不超过4位数也可以这样写 +right(10000+VoteB,4) +right(10000+VoteC,4) +right(10000+VoteD,4) desc如果甲会员的得票是: VoteA VoteB VoteC VoteD VoteE 0 0 0 0 0 乙会员的得票是: 0 0 0 0 1本来甲会员要排到乙会员前面,因为乙会员得到了很差的评价,但是按上面的语句,可能正好相反。
ORDER BY CASE WHEN EndedDate > CONVERT(CHAR(10), GETDATE(), 120) THEN 1 ELSE 0 END,
(VoteA*2+VoteB*1+VoteD*(-1)+VoteE*(-2)) DESC
ORDER BY case when EndedDate>GETDATE() then 1 else 0 end --是否到期
,VoteA desc,VoteB desc,VoteC desc,VoteD desc
ORDER BY case when EndedDate>GETDATE() then 1 else 0 end --是否到期
,right(10000+VoteA,4) ---不超过4位数也可以这样写
+right(10000+VoteB,4)
+right(10000+VoteC,4)
+right(10000+VoteD,4) desc
另外,我想在SELECT语句中生成一个bit类型的字段BeMember (即根据到期日期判断是否是会员),是否可以这样写:
SELECT CompanyName, case (when EndedDate>GETDATE() then 1 else 0 end ) AS BeMember FROM Company
ORDER BY case when EndedDate>GETDATE() then 1 else 0 end --是否到期
,right(10000+VoteA,4) ---不超过4位数也可以这样写
+right(10000+VoteB,4)
+right(10000+VoteC,4)
+right(10000+VoteD,4) desc如果甲会员的得票是: VoteA VoteB VoteC VoteD VoteE
0 0 0 0 0
乙会员的得票是: 0 0 0 0 1本来甲会员要排到乙会员前面,因为乙会员得到了很差的评价,但是按上面的语句,可能正好相反。