select *，count(*)as num where num>10 order by age

这个SQL语句是有问题的，因为在查询中使用了聚合函数count(*)作为一个字段并命名为num，但是在where子句中使用了该num字段，这是不允许的。正确的写法应该是使用having子句进行筛选：

SELECT *, count(*) as num
FROM table_name
GROUP BY some_column
HAVING num > 10
ORDER BY age;

上面的SQL语句会按照age字段进行排序，同时只返回num大于10的记录。请注意，由于使用了分组聚合函数，查询结果中只会出现指定的字段和聚合函数计算后的结果，所以这里的*实际上指定的是查询的所有字段。

相关问题

请写出以下 SQL 语句 (I）各省人员表 user_info (id, name, age, phone, province, id_ card),写出各省年龄在 30 以上的人员数并升序排序的 sal 语句； select province, count (id) num from user info where age > 30 group by province order by num asc; (2）成绩表 student_ score (id, name, score) 姓名 学科 成绩 LiLei 英语 78 LiLei 数学 103 ZhangKe 物理 97 ZhangKe 数学 99 姓名 Litei ZhangKe 英语 78 0 数学 103 99 物理 0 97 如何实现左图到右图的转换？

这是一个数据透视表的转换，可以使用 SQL 中的 PIVOT 或者自连接实现。以下是使用自连接的 SQL 语句示例：

SELECT
  s1.name,
  s1.英语 AS 英语,
  s1.数学 AS 数学,
  s1.物理 AS 物理
FROM
  (SELECT name, score, 学科 FROM student_score) AS s
  PIVOT (MAX(score) FOR 学科 IN ([英语], [数学], [物理])) AS p
  JOIN (SELECT name, 英语, 数学, 物理 FROM (SELECT name, score, 学科 FROM student_score) AS s PIVOT (MAX(score) FOR 学科 IN ([英语], [数学], [物理])) AS p) AS s1 ON s.name = s1.name

注意，不同的数据库系统可能有不同的语法实现数据透视表，上述语句是针对 SQL Server 的实现。

SELECT CUST_ID agentId, AGE_NAME agentName, countReleased, dayAmountReleased, CASE o.agelimitType WHEN '1' THEN TO_CHAR(o.agelimitNum) ELSE TO_CHAR(o.issueamtlimit) END countLimit, CASE o.agelimitType WHEN '1' THEN '不限' ELSE TO_CHAR(o.daylimitamount) END dayMaxCount FROM (SELECT m.CUST_ID, m.AGE_NAME, ad.countReleased, t.dayAmountReleased, d.daylimitamount, d.issueamtlimit, (SELECT AGE_LIMIT_NUM FROM P_ACT_ALLOW_MANAGER WHERE ACTIVITY_ID = #{activityId} AND rownum = 1 ) AS agelimitNum, (SELECT AGE_LIMIT_NUM_TYPE FROM P_ACT_MANAGER_ACTIVITY WHERE ACTIVITY_ID = #{activityId} ) AS agelimitType FROM (SELECT c1.CUST_ID, c1.AGE_NAME FROM (SELECT CUST_ID, AGE_NAME FROM PM_AGET_INFO START WITH CUST_ID = #{agetId} CONNECT BY PRIOR CUST_ID = FATH_CUST_ID ) c1 INNER JOIN (SELECT DISTINCT CUST_ID, AGE_NAME FROM PM_AGET_INFO START WITH CUST_ID in (SELECT CUST_ID FROM P_ACT_ALLOW_MANAGER WHERE ACTIVITY_ID = #{activityId} ) CONNECT BY PRIOR CUST_ID = FATH_CUST_ID ) c2 ON c1.CUST_ID = c2.CUST_ID ) m LEFT JOIN (SELECT CUST_ID, count(RECORD_ID) countReleased FROM P_ACT_MANAGER_RECORD WHERE ACTIVITY_ID = #{activityId} AND RECORD_STATUS NOT IN (2, 5) AND to_date(CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') BETWEEN to_date(#{beginTime}, 'yyyyMMddhh24miss') AND to_date(#{endTime}, 'yyyyMMddhh24miss') GROUP BY CUST_ID ) ad ON m.CUST_ID = ad.CUST_ID LEFT JOIN (SELECT AGE_ID cust_id, DAY_LIMIT_AMOUNT daylimitAmount, ISSUE_AMT_LIMIT issueAmtLimit FROM P_ACT_MANAGER_LIMIT_ALLOT WHERE ACT_ID =#{activityId} ) d ON m.CUST_ID = d.CUST_ID LEFT JOIN (SELECT CUST_ID, count(RECORD_ID) dayAmountReleased FROM P_ACT_MANAGER_RECORD WHERE ACTIVITY_ID =#{activityId} AND RECORD_STATUS NOT IN (2, 5) AND to_date(CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') BETWEEN to_date(#{todayBegin}, 'yyyyMMddhh24miss') AND to_date(#{todayend}, 'yyyyMMddhh24miss') GROUP BY CUST_ID ) t ON m.CUST_ID = t.CUST_ID) o WHERE 1 = 1 AND o.CUST_ID = #{agentId} AND o.AGE_NAME = #{agentName} ORDER BY o.countReleased DESC NULLS LAST 如何优化此段sql代码,使得更加简洁？

这段 SQL 代码可以进行以下优化：

1. 使用 WITH 子句来代替嵌套查询，增加可读性。

2. 使用 LEFT JOIN 和 COALESCE 函数来代替子查询，避免多次查询。

3. 使用 CASE WHEN THEN ELSE END 语句来代替 IF 函数。

优化后的 SQL 代码如下：

WITH
cte1 AS (
SELECT
CUST_ID,
AGE_NAME
FROM
PM_AGET_INFO
START WITH
CUST_ID = #{agetId}
CONNECT BY
PRIOR CUST_ID = FATH_CUST_ID
),
cte2 AS (
SELECT
DISTINCT CUST_ID,
AGE_NAME
FROM
PM_AGET_INFO
START WITH
CUST_ID IN (
SELECT
CUST_ID
FROM
P_ACT_ALLOW_MANAGER
WHERE
ACTIVITY_ID = #{activityId}
)
CONNECT BY
PRIOR CUST_ID = FATH_CUST_ID
),
cte3 AS (
SELECT
CUST_ID,
count(RECORD_ID) countReleased
FROM
P_ACT_MANAGER_RECORD
WHERE
ACTIVITY_ID = #{activityId}
AND RECORD_STATUS NOT IN (2, 5)
AND to_date(CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') BETWEEN to_date(#{beginTime}, 'yyyyMMddhh24miss') AND to_date(#{endTime}, 'yyyyMMddhh24miss')
GROUP BY
CUST_ID
),
cte4 AS (
SELECT
CUST_ID,
count(RECORD_ID) dayAmountReleased
FROM
P_ACT_MANAGER_RECORD
WHERE
ACTIVITY_ID =#{activityId}
AND RECORD_STATUS NOT IN (2, 5)
AND to_date(CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') BETWEEN to_date(#{todayBegin}, 'yyyyMMddhh24miss') AND to_date(#{todayend}, 'yyyyMMddhh24miss')
GROUP BY
CUST_ID
),
cte5 AS (
SELECT
AGE_LIMIT_NUM
FROM
P_ACT_ALLOW_MANAGER
WHERE
ACTIVITY_ID = #{activityId}
AND rownum = 1
),
cte6 AS (
SELECT
AGE_LIMIT_NUM_TYPE
FROM
P_ACT_MANAGER_ACTIVITY
WHERE
ACTIVITY_ID = #{activityId}
),
cte7 AS (
SELECT
AGE_ID cust_id,
DAY_LIMIT_AMOUNT daylimitAmount,
ISSUE_AMT_LIMIT issueAmtLimit
FROM
P_ACT_MANAGER_LIMIT_ALLOT
WHERE
ACT_ID =#{activityId}
)
SELECT
m.CUST_ID agentId,
m.AGE_NAME agentName,
COALESCE(ad.countReleased, 0) countReleased,
COALESCE(t.dayAmountReleased, 0) dayAmountReleased,
CASE
WHEN o.agelimitType = '1' THEN TO_CHAR(o.agelimitNum)
ELSE TO_CHAR(o.issueamtlimit)
END countLimit,
CASE
WHEN o.agelimitType = '1' THEN '不限'
ELSE TO_CHAR(o.daylimitamount)
END dayMaxCount
FROM
cte1 m
INNER JOIN cte2 c ON m.CUST_ID = c.CUST_ID AND m.AGE_NAME = c.AGE_NAME
LEFT JOIN cte3 ad ON m.CUST_ID = ad.CUST_ID
LEFT JOIN cte4 t ON m.CUST_ID = t.CUST_ID
LEFT JOIN cte5 ON 1 = 1
LEFT JOIN cte6 ON 1 = 1
LEFT JOIN cte7 d ON m.CUST_ID = d.CUST_ID
LEFT JOIN (
SELECT
COALESCE(AGE_LIMIT_NUM, 0) agelimitNum,
COALESCE(AGE_LIMIT_NUM_TYPE, '1') agelimitType
FROM
dual
) o ON 1 = 1
WHERE
m.CUST_ID = #{agentId}
AND m.AGE_NAME = #{agentName}
ORDER BY
COALESCE(ad.countReleased, 0) DESC NULLS LAST

这样可以使得代码更加简洁易读，并且避免了重复查询和嵌套查询的问题。