select [姓名],[01],(case 02 when 1 then '啊' when 2 then '不' when 3 then '成' when 4 then '的') as ['02'],…… from ( select datepart(DD,Duty.date) as daydate,DutyPeople.name as '姓名',DutyState.ID as statename FROM Duty,DutyDepart,DutyPeople,DutyState where Duty.peopleID=DutyPeoPle.ID and Duty.stateID=DutyState.ID and DutyPeoPle.DepartID=DutyDepart.ID --and CONVERT(varchar(7), date, 121)='2012-11' ) qty pivot( sum(statename) for daydate in( [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ) ) as pvt
[其他解释] 在你的那个子查询里,连接你的状态表,用文字当作statename不用Id 我看你的那个dutyState可能就是状态表,把dutyState.Id as statename改成dutyState.name as statename
[其他解释] 定义个字典,数字为key,value为中文,你根据数字找字典不就行了,还可以在数据库中定义一个表,去表中查询,或者sql查询时,就两表联合或用when case语句专为中文 [其他解释] sql帮助查 CASE 语句
select * from ( select datepart(DD,Duty.date) as daydate,DutyPeople.name as '姓名',DutyState.ID as statename FROM Duty,DutyDepart,DutyPeople,DutyState where Duty.peopleID=DutyPeoPle.ID and Duty.stateID=DutyState.ID and DutyPeoPle.DepartID=DutyDepart.ID --and CONVERT(varchar(7), date, 121)='2012-11' ) qty pivot( sum(statename) for daydate in( [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ) ) as pvt
那个不是只查询,是吧行 转换为列,就是把 日期的天 统计成 每月的一个情况,导出excle 的时候好用。 那个 sum(statename) 还不能用 navchar 只支持 int [其他解释] select * from ( select datepart(DD,Duty.date) as daydate,DutyPeople.name as '姓名',DutyState.state as statename FROM Duty,DutyDepart,DutyPeople,DutyState where Duty.peopleID=DutyPeoPle.ID and Duty.stateID=DutyState.ID and DutyPeoPle.DepartID=DutyDepart.ID --and CONVERT(varchar(7), date, 121)='2012-11' ) qty pivot( max(statename) for daydate in( [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ) ) as pvt
大神们看我自己怎么解决的 sum 不能用于计算字符串,然后就让 max /min 等能计算字符串的 的聚合函数来 计算,就把传入回来的 状态的 中文直接传递过来了 然后一切都ok 了