34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT 年, 月, COUNT(*) AS a FROM (
SELECT 年, 月, 日, COUNT(*) AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(m AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
SELECT 年, 月, COUNT(*) AS b FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(t AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
SELECT 年, 月, COUNT(*) AS c FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(p AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
SELECT 年, 月, COUNT(*) AS d FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(q AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
SELECT 年, 月, COUNT(*) AS e FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(s AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
年 月 a
2014 11 4
2014 12 1
2015 2 1
2015 9 1
年 月 b
2014 4 2
2014 5 11
2014 6 16
2014 7 13
2014 8 13
2014 9 7
2014 10 3
2014 11 1
2015 1 1
2015 2 2
2015 3 11
2015 4 16
2015 5 13
2015 6 13
2015 7 18
2015 8 17
2015 9 7
年 月 c
2014 4 2
2014 5 8
2014 6 5
2014 7 6
2014 8 5
2014 9 2
2014 10 1
2015 3 1
2015 4 3
2015 5 1
2015 7 3
2015 8 2
2015 9 2
年 月 d
2014 4 2
2014 5 23
2014 9 20
2014 10 10
2014 11 5
2014 12 5
2015 1 5
2015 2 10
2015 3 27
2015 4 21
2015 5 24
2015 6 19
2015 7 22
2015 8 23
2015 9 12
年 月 e
2014 4 2
2014 5 24
2014 6 20
2014 7 21
2014 8 21
2014 9 20
2014 10 11
2014 11 7
2014 12 18
2015 1 11
2015 2 14
2015 3 27
2015 4 21
2015 5 25
2015 9 13
年 月 a b c d e
2014 4 2 2 2 2
2014 5 11 8 23 24
2014 6 16 5 20
2014 7 13 6 21
2014 8 13 5 21
2014 9 7 2 20 20
2014 10 3 1 10 11
2014 11 4 1 5 7
2014 12 1 5 18
2015 1 1 5 11
2015 2 1 2 10 14
2015 3 11 1 27 27
2015 4 16 3 21 21
2015 5 13 1 24 25
2015 6 13 19
2015 7 18 3 22
2015 8 17 2 23
2015 9 1 7 2 12 13
select
t.年, t.月,
t1.a as a, t2.a as b,t3.a as c,t4.a as d,t5.a as e
from
(
SELECT 年, 月 FROM TQXX_M_Z_51747 GROUP BY 年, 月
) as t
left join
(
SELECT 年, 月, COUNT(*) AS a FROM TQXX_M_Z_51747
WHERE (CAST(m AS int) > '80') GROUP BY 年, 月
) as t1
on t.年=t1.年 and t.月=t1.月
left join
(
SELECT 年, 月, COUNT(*) AS a FROM TQXX_M_Z_51747
WHERE (CAST(t AS int) > '80') GROUP BY 年, 月
) as t2
on t.年=t2.年 and t.月=t2.月
left join
(
SELECT 年, 月, COUNT(*) AS a FROM TQXX_M_Z_51747
WHERE (CAST(p AS int) > '80') GROUP BY 年, 月
) as t3
on t.年=t3.年 and t.月=t3.月
left join
(
SELECT 年, 月, COUNT(*) AS a FROM TQXX_M_Z_51747
WHERE (CAST(q AS int) > '80') GROUP BY 年, 月
) as t4
on t.年=t4.年 and t.月=t4.月
left join
(
SELECT 年, 月, COUNT(*) AS a FROM TQXX_M_Z_51747
WHERE (CAST(s AS int) > '80') GROUP BY 年, 月
) as t5
on t.年=t5.年 and t.月=t5.月
--像这样 其实用between and 也可以
sum(case when (CAST(m AS int) > '80') AND (CAST(m AS int) <= '100') AND (CAST(l AS int) < '750') AND (CAST(l AS int) > '0') then 1 else 0 end)
SELECT 年, 月, COUNT(*) AS l次数 FROM (
SELECT 年, 月, 日, COUNT(*) AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(m AS int) > '80') AND (CAST(m AS int) <= '100') AND (CAST(l AS int) < '750') AND (CAST(l AS int) > '0')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
消息 170,级别 15,状态 1,第 1 行
第 1 行: ';' 附近有语法错误。
消息 170,级别 15,状态 1,第 9 行
第 9 行: ',' 附近有语法错误。
消息 170,级别 15,状态 1,第 17 行
第 17 行: ',' 附近有语法错误。
消息 170,级别 15,状态 1,第 25 行
第 25 行: ',' 附近有语法错误。
消息 170,级别 15,状态 1,第 33 行
第 33 行: ',' 附近有语法错误。
消息 170,级别 15,状态 1,第 41 行
第 41 行: ',' 附近有语法错误。
消息 170,级别 15,状态 1,第 49 行
第 49 行: ',' 附近有语法错误。
SELECT 年, 月
sum(case when m > 80 then 1 else 0 end) as a,
sum(case when t > 80 then 1 else 0 end) as b,
sum(case when p > 80 then 1 else 0 end) as c,
sum(case when q > 80 then 1 else 0 end) as d,
sum(case when s > 80 then 1 else 0 end) as e
FROM TQXX_M_Z_51747
GROUP BY 年, 月
;with tb01 as
(
SELECT 年, 月, COUNT(*) AS a FROM (
SELECT 年, 月, 日, COUNT(*) AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(m AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
)
, tb02 as
(
SELECT 年, 月, COUNT(*) AS b FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(t AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
)
, tb03 as
(
SELECT 年, 月, COUNT(*) AS c FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(p AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
)
, tb04 as
(
SELECT 年, 月, COUNT(*) AS d FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(q AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
)
, tb05 as
(
SELECT 年, 月, COUNT(*) AS e FROM (
SELECT 年, 月, 日, COUNT(*)AS Expr1 FROM TQXX_M_Z_51747
WHERE (CAST(s AS int) > '80')
GROUP BY 年, 月, 日
) AS a GROUP BY 年, 月
)
select
t1.年, t1.月,
t1.a, t2.b, t3.c, t4.d, t5.e
from tb01 as t1
left join tb02 as t2
on t1.年=t2.年 and t1.月=t2.月
left join tb03 as t3
on t1.年=t3.年 and t1.月=t3.月
left join tb04 as t4
on t1.年=t4.年 and t1.月=t4.月
left join tb05 as t5
on t1.年=t5.年 and t1.月=t5.月