SELECT q.c_id, q.c_userid, q.c_title, q.c_createtime, q.c_isprivate, q.c_acceptid, q.c_deleted, count(t.c_id) answerNum FROM t_ext_question q left join t_comment t on t.c_pid = q.c_id where q.c_deleted = 0 AND lower( q.c_title ) LIKE '%123%' AND q.c_userid = 'ADMIN' AND DATE_FORMAT( q.c_createtime, '%Y-%m-%d' ) BETWEEN '2023-05-01' AND '2023-05-14' GROUP BY q.c_id, q.c_userid, q.c_title, q.c_createtime, q.c_isprivate, q.c_acceptid, q.c_deleted HAVING count(t.c_id)=0 ORDER BY c_createtime desc

这是一个 SQL 查询，查询的是一个问题和它的答案数量。具体的查询条件包括：

- `q.c_deleted = 0`：问题没有被删除
- `lower( q.c_title ) LIKE '%123%'`：问题标题中包含字符串 "123"（不区分大小写）
- `q.c_userid = 'ADMIN'`：问题是由用户 "ADMIN" 提交的
- `DATE_FORMAT( q.c_createtime, '%Y-%m-%d' ) BETWEEN '2023-05-01' AND '2023-05-14'`：问题创建时间在 2023 年 5 月 1 日和 5 月 14 日之间
- `count(t.c_id)=0`：问题没有答案
- `ORDER BY c_createtime desc`：按照问题创建时间的倒序排序

查询的结果包括问题的 ID、用户 ID、标题、创建时间、是否为私有问题、被接受的答案 ID、是否被删除以及答案的数量。

相关问题

declare @startTime date declare @endTime date declare @Uid int SELECT TOP 10 * FROM ( select ROW_NUMBER() OVER (ORDER BY o.CreateTime) AS rownumber, oid.OrderNo,o.CreateMainUser,su1.ContactPerson,IsNULL((su2.No+'-'+su2.Name),'') as CmUser,su1.Name,IsNULL((su3.No+'-'+su3.Name),'') as OpreaCmUser,o.CreateTime,o.OrderMoney,o.PayAmount from Platform_OrderIdInfo oid with(nolock) inner join Platform_InsOrders o with(nolock) on o.OrderNo=oid.OrderNo inner join Sys_User su1 with(nolock) on o.CreateMainUser=su1.Id inner join Sys_User su2 with(nolock) on su1.CM_UserId=su2.Id inner join Sys_User su3 with(nolock) on oid.BaoyUserId=su3.Id where BaoyUserId>0 and o.OrderStatus=100 and (o.CreateTime >= COALESCE(@startTime, o.CreateTime) OR @startTime IS NULL) and (o.CreateTime < COALESCE(@endTime, o.CreateTime) OR @endTime IS NULL) order by o.CancelTime ) AS t WHERE t.rownumber > 0;错误除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

错误信息提示 ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，这是因为在子查询中使用了 ORDER BY 语句，但是子查询是没有排序概念的，所以会报错。

解决方法一般有两种：

1. 去掉子查询中的 ORDER BY 语句，将 TOP 10 放到子查询外面。

2. 将子查询转换为一个临时表或表变量，然后在临时表或表变量上使用 ORDER BY 语句。

第一种解决方法的示例代码如下：

declare @startTime date 
declare @endTime date 
declare @Uid int 

SELECT TOP 10 * FROM (
    select ROW_NUMBER() OVER (ORDER BY o.CreateTime) AS rownumber, 
           oid.OrderNo,
           o.CreateMainUser,
           su1.ContactPerson,
           IsNULL((su2.No+'-'+su2.Name),'') as CmUser,
           su1.Name,
           IsNULL((su3.No+'-'+su3.Name),'') as OpreaCmUser,
           o.CreateTime,
           o.OrderMoney,
           o.PayAmount 
    from Platform_OrderIdInfo oid with(nolock) 
    inner join Platform_InsOrders o with(nolock) on o.OrderNo=oid.OrderNo 
    inner join Sys_User su1 with(nolock) on o.CreateMainUser=su1.Id 
    inner join Sys_User su2 with(nolock) on su1.CM_UserId=su2.Id 
    inner join Sys_User su3 with(nolock) on oid.BaoyUserId=su3.Id 
    where BaoyUserId>0 and o.OrderStatus=100 
          and (o.CreateTime >= COALESCE(@startTime, o.CreateTime) OR @startTime IS NULL) 
          and (o.CreateTime < COALESCE(@endTime, o.CreateTime) OR @endTime IS NULL)
) AS t 
WHERE t.rownumber > 0
ORDER BY t.CreateTime;

在上述代码中，我们将 TOP 10 放到子查询外面，然后在最后使用 ORDER BY 语句对结果进行排序，这样就可以避免错误了。

第二种解决方法的示例代码如下：

declare @startTime date 
declare @endTime date 
declare @Uid int 

DECLARE @tempTable TABLE (
    rownumber INT,
    OrderNo VARCHAR(50),
    CreateMainUser INT,
    ContactPerson VARCHAR(50),
    CmUser VARCHAR(100),
    Name VARCHAR(50),
    OpreaCmUser VARCHAR(100),
    CreateTime DATETIME,
    OrderMoney MONEY,
    PayAmount MONEY
)

INSERT INTO @tempTable
select ROW_NUMBER() OVER (ORDER BY o.CreateTime) AS rownumber, 
       oid.OrderNo,
       o.CreateMainUser,
       su1.ContactPerson,
       IsNULL((su2.No+'-'+su2.Name),'') as CmUser,
       su1.Name,
       IsNULL((su3.No+'-'+su3.Name),'') as OpreaCmUser,
       o.CreateTime,
       o.OrderMoney,
       o.PayAmount 
from Platform_OrderIdInfo oid with(nolock) 
inner join Platform_InsOrders o with(nolock) on o.OrderNo=oid.OrderNo 
inner join Sys_User su1 with(nolock) on o.CreateMainUser=su1.Id 
inner join Sys_User su2 with(nolock) on su1.CM_UserId=su2.Id 
inner join Sys_User su3 with(nolock) on oid.BaoyUserId=su3.Id 
where BaoyUserId>0 and o.OrderStatus=100 
      and (o.CreateTime >= COALESCE(@startTime, o.CreateTime) OR @startTime IS NULL) 
      and (o.CreateTime < COALESCE(@endTime, o.CreateTime) OR @endTime IS NULL)

SELECT TOP 10 * FROM @tempTable WHERE rownumber > 0 ORDER BY CreateTime;

在上述代码中，我们使用了一个表变量 @tempTable，将子查询的结果插入到该表变量中，然后在表变量上使用 ORDER BY 语句对结果进行排序并取出 TOP 10 数据。

帮我优化一下这个sql select e.id, e.mobile, e.encoded, CASE e.is_echo WHEN 1 THEN '已回传' else '未回传' END AS isEcho , e.order_no AS orderNo, e.pay_amount AS payAmount, e.operator_id AS operatorId, e.operator_name AS operatorName, e.operator_time AS operatorTime, e.remarks AS remarks, e.`status`, CASE p.pay_status WHEN 1 THEN '支付成功' WHEN 2 THEN '支付失败' END AS payStatus , CASE e.status WHEN 1 THEN '待支付' WHEN 2 THEN '已支付' WHEN 3 THEN '已退款' WHEN 4 THEN '订单关闭' WHEN 5 THEN '退款中' WHEN 6 THEN '退款关闭' END AS statusName, e.create_time AS createTime, u.id AS userId, p.pay_channel AS payChannel, CASE p.pay_channel WHEN 1 THEN '支付宝' WHEN 2 THEN '微信' END AS payChannelName , p.out_trade_no AS outTradeNo, e.third_party_channel AS thirdPartyChannel, info.return_amount AS returnAmount, info.return_phone AS returnPhone, info.return_name AS returnName from equity.equity_order_info e left join equity.user_info u on e.mobile = u.user_photo LEFT JOIN ( SELECT * FROM equity.pay_message GROUP BY order_no ) AS p ON p.order_no = e.order_no left join equity.refund_info AS info ON info.order_no=e.order_no <where> <if test="mobile != null and mobile != ''">and e.mobile = #{mobile}</if> <if test="orderNo != null and orderNo != ''">and e.order_no = #{orderNo}</if> <if test="payAmount != null ">and e.pay_amount = #{payAmount}</if> <if test="thirdPartyChannel != null and thirdPartyChannel != ''">and e.third_party_channel = #{thirdPartyChannel} </if> <if test="outTradeNo != null and outTradeNo != ''">and p.out_trade_no = #{outTradeNo} </if> <if test="startTime != null and startTime != '' and endTime != null and endTime != ''"> and DATE_FORMAT(e.create_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startTime},'%Y-%m-%d') AND DATE_FORMAT(#{endTime},'%Y-%m-%d') </if> <if test="status != null and status > 0">and e.status = #{status}</if> <if test="userId != null ">and u.id = #{userId}</if> </where> ORDER BY e.create_time DESC

#{mobile}</if>
<if test="startTime != null">and e.create_time &gt;= #{startTime}</if>
<if test="endTime != null">and e.create_time &lt;= #{endTime}</if>
<if test="status != null">and e.`status` = #{status}</if>
<if test="payStatus != null">and p.pay_status = #{payStatus}</if>
<if test="payChannel != null">and p.pay_channel = #{payChannel}</if>
</where>
ORDER BY e.create_time DESC, e.id DESC
LIMIT #{start},#{length}</code></pre>

优化建议：
1. 尽可能避免使用SELECT *
2. group by 关键字比其他的聚合函数效率低，尽量避免使用
3. 减少使用CASE WHEN，可以使用join来加快速度
4. 尽量减少WHERE子句中的嵌套IF条件，可以使用join来加快速度
5. 为需要检索的列创建索引，如E.mobile、P.pay_channel、E.`status`等