SQL Server Count排序问题
问题1:A表与B表关联,Count A表某字段,结果直接使用 ORDER BY排序,直接给我报错 ORDER BY附近语法有错误。
解决思路:Count语句不支bai持where和order by,把他先整理成一个查询,再去排列筛选就好了。
解决语句:SELECT * FROM
(SELECT
COUNT(A.字段) AS AXXX,
B.name
FROM
A表 AS Abiao
LEFT JOIN B表 AS Bbiao ON Bbiao.name = Abiao.name
GROUP BY B.name) as AAA
ORDER BY AXXX DESC
问题2:当我们数据量特别庞大的时候,需要取前面几位关键数据。语法稍微改动一下
解决思路:增加一个Top语法。
解决语句:SELECT
top.字段
FROM
(SELECT
COUNT(A.字段) AS AXXX,
B.name
FROM
A表 AS Abiao
LEFT JOIN B表 AS Bbiao ON Bbiao.name = Abiao.name
GROUP BY B.name) as AAA
ORDER BY AXXX DESC
问题3:解决SQL Server 根据当天时间查询数据
解决方案:DateDiff(dd,字段,getdate())=0
问题4:> Msg 102, Level 15, State 1, Server WIN-OTGR1H6FUIL, Procedure , Line 0
')' 附近有语法错误。
> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]')' 附近有语法错误。 (102)
解决方案:这个问题他妈的能把我气死,正常的的SQL 为啥就报错!
说白了这就是微软搞得一个手段。只要你用了ORDER BY还要他妈的去验证是否正确。
添加这个就好了:TOP (100) PERCENT 字段,
SELECT * FROM
(SELECT
TOP (100) PERCENT 字段,
COUNT(A.字段) AS AXXX,
B.name
FROM
A表 AS Abiao
LEFT JOIN B表 AS Bbiao ON Bbiao.name = Abiao.name
GROUP BY B.name) as AAA
ORDER BY AXXX DESC