select month([d_cs_date]), [1]=case when day(d_cs_date)=1 then i_cs_clas end, [2]=case when day(d_cs_date)=2 then i_cs_clas end, [3]=case when day(d_cs_date)=3 then i_cs_clas end, .......... [31]=case when day(d_cs_date)=31 then i_cs_clas end, from tb group by month([d_cs_date])
服务器: 消息 8120,级别 16,状态 1,行 1 列 'Classeat.i_cs_clas' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。 服务器: 消息 8120,级别 16,状态 1,行 1 列 'Classeat.d_cs_date' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。不行啊
select month([d_cs_date]), [1]=max(case when day(d_cs_date)=1 then i_cs_clas end), [2]=max(case when day(d_cs_date)=2 then i_cs_clas end), [3]=max(case when day(d_cs_date)=3 then i_cs_clas end), .......... [31]=max(case when day(d_cs_date)=31 then i_cs_clas end), from tb group by month([d_cs_date])
select month([d_cs_date]), sum(case when day(d_cs_date)=1 then b_cs_stop else 0 end) '1日', sum(case when day(d_cs_date)=2 then b_cs_stop else 0 end) '2日', sum(case when day(d_cs_date)=3 then b_cs_stop else 0 end) '3日', .......... sum(case when day(d_cs_date)=1 then b_cs_stop else 0 end) '1日' from tb where year([d_cs_date])=2012 and i_cs_clas='某个班次' group by month([d_cs_date]) 2012和某个班次 你自己代入
select month([d_cs_date]), sum(case when day(d_cs_date)=1 then b_cs_stop else 0 end) '1日', sum(case when day(d_cs_date)=2 then b_cs_stop else 0 end) '2日', sum(case when day(d_cs_date)=3 then b_cs_stop else 0 end) '3日', .......... sum(case when day(d_cs_date)=29 then b_cs_stop else null end) '29日' sum(case when day(d_cs_date)=30 then b_cs_stop else null end) '30日' sum(case when day(d_cs_date)=31 then b_cs_stop else null end) '31日' from tb where year([d_cs_date])=2012 and i_cs_clas='某个班次' group by month([d_cs_date])这样的话 用 NULL表示 可以不
改为NULL后提示 sum or average aggregate 运算不能以 bit 数据类型作为参数。
将 语句里 每个b_cs_stop 换成 CAST(b_cs_stop AS INT)
不妥额,0 变成了NULL ,我3月31号有开班的都变成NULL去了
不可能,如果 你的31号有个值 为0的话,不可能NULL
select CAST(month([d_cs_date])AS VARCHAR(2))+'月', CASE(sum(case when day(d_cs_date)=1 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '1日', CASE(sum(case when day(d_cs_date)=2 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '2日', CASE(sum(case when day(d_cs_date)=3 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '3日', CASE(sum(case when day(d_cs_date)=29 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '29日', CASE(sum(case when day(d_cs_date)=30 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '30日', CASE(sum(case when day(d_cs_date)=31 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '31日' from #1 where year([d_cs_date])=2012 and i_cs_clas='某个班次' group by month([d_cs_date])
[1]=case when day(d_cs_date)=1 then i_cs_clas end,
[2]=case when day(d_cs_date)=2 then i_cs_clas end,
[3]=case when day(d_cs_date)=3 then i_cs_clas end,
..........
[31]=case when day(d_cs_date)=31 then i_cs_clas end,
from tb group by month([d_cs_date])
列 'Classeat.i_cs_clas' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
服务器: 消息 8120,级别 16,状态 1,行 1
列 'Classeat.d_cs_date' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。不行啊
[1]=max(case when day(d_cs_date)=1 then i_cs_clas end),
[2]=max(case when day(d_cs_date)=2 then i_cs_clas end),
[3]=max(case when day(d_cs_date)=3 then i_cs_clas end),
..........
[31]=max(case when day(d_cs_date)=31 then i_cs_clas end),
from tb group by month([d_cs_date])
sum(case when day(d_cs_date)=1 then b_cs_stop else 0 end) '1日',
sum(case when day(d_cs_date)=2 then b_cs_stop else 0 end) '2日',
sum(case when day(d_cs_date)=3 then b_cs_stop else 0 end) '3日',
..........
sum(case when day(d_cs_date)=1 then b_cs_stop else 0 end) '1日'
from tb where year([d_cs_date])=2012 and i_cs_clas='某个班次' group by month([d_cs_date])
2012和某个班次 你自己代入
sum(case when day(d_cs_date)=1 then b_cs_stop else 0 end) '1日',
sum(case when day(d_cs_date)=2 then b_cs_stop else 0 end) '2日',
sum(case when day(d_cs_date)=3 then b_cs_stop else 0 end) '3日',
..........
sum(case when day(d_cs_date)=29 then b_cs_stop else null end) '29日'
sum(case when day(d_cs_date)=30 then b_cs_stop else null end) '30日'
sum(case when day(d_cs_date)=31 then b_cs_stop else null end) '31日'
from tb where year([d_cs_date])=2012 and i_cs_clas='某个班次' group by month([d_cs_date])这样的话 用 NULL表示 可以不
sum or average aggregate 运算不能以 bit 数据类型作为参数。
select CAST(month([d_cs_date])AS VARCHAR(2))+'月',
CASE(sum(case when day(d_cs_date)=1 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '1日',
CASE(sum(case when day(d_cs_date)=2 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '2日',
CASE(sum(case when day(d_cs_date)=3 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '3日',
CASE(sum(case when day(d_cs_date)=29 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '29日',
CASE(sum(case when day(d_cs_date)=30 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '30日',
CASE(sum(case when day(d_cs_date)=31 then CAST(b_cs_stop AS INT) else null end))WHEN 0 THEN '√'ELSE '×' END '31日'
from #1
where year([d_cs_date])=2012 and i_cs_clas='某个班次'
group by month([d_cs_date])
1.select CAST(month([d_cs_date])AS VARCHAR(2))+'月' 这个group by 后出错,
提示:列 'Classeat.d_cs_date' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
麻烦测试下
2.月份现在是1 2 3 4 5 6 7 8 9 10 11 12 ,能变成一 二 三 四 五大写吗?回答完立刻结贴,顺便说声THANK~