m0_37908418 2018-12-27 16:20 采纳率: 70.2%

# fastr report 中数据与sql查出数据不同

fast report中变成了几个数45+44+44相加的和133 显示,而不是分开显示45,44,44 不知什么原因

select

zenbu.Workshop,
zenbu.qty,
case when
zenbu.Card#=zenbu.Card# and zenbu.Diffhours>23 and zenbu.Diffhours then zenbu.Card# end as "卡",
zenbu.Diffhours as "小時",
zenbu.PO,
zenbu.[Style No],
zenbu.Diffday,
case when zenbu.Diffhours 0 and zenbu.Diffhours>23 and zenbu.Diffhours<49
then zenbu.Workshop end AS "小組",
zenbu.Intime,
zenbu.Outime

/*max(case when
zenbu.out2timecard=zenbu.out2timecard
OR zenbu.out3timecard =zenbu.out3timecard
OR zenbu.out4timecard =zenbu.out4timecard
OR zenbu.out5timecard =zenbu.out5timecard
OR zenbu.out6timecard =zenbu.out6timecard
then ltrim(rtrim(zenbu.Workshop)) end )as outWorkshop,
ltrim(rtrim(CONVERT(VARCHAR(19),zenbu.Intime,120)))as cintime,
ltrim(rtrim(CONVERT(VARCHAR(19),zenbu.Outime,120)))as coutime*/
/*
max(case when zenbu.Diffhours>2 and zenbu.Diffhours=3 then zenbu.Card# end )as Exceed2Hours,
max(case when zenbu.Diffhours=4 and zenbu.Diffhours max(case when zenbu.Diffhours>6 and zenbu.Diffhours max(case when zenbu.Diffhours>8 and zenbu.Diffhours max(case when zenbu.Diffhours>25 and zenbu.Diffhours max(case when zenbu.Diffhours>48 and zenbu.Diffhours<73 then zenbu.Card# end )as Exceed3day*/
/*case when zenbu.Diffday=24 and zenbu.Diffday is not null or zenbu.Diffday<36 then zenbu.Card# end as Exceed1day*/

/*case when DATEDIFF(HOUR,zenbu.Intime,zenbu.Outime)=2 and DATEDIFF(HOUR,zenbu.Intime,zenbu.Outime)<3
then zenbu.Card# end as Exceed2hours,*/

/*case when DATEDIFF(HOUR,zenbu.Intime,zenbu.Outime)=4 and DATEDIFF(HOUR,zenbu.Intime,zenbu.Outime)<5
then zenbu.Card# end as Exceed4hours*/

/*case when zenbu.qty=1 and zenbu.Outime IS null and
DATEDIFF(HH,zenbu.Intime,GETDATE())>12 then zenbu.Card# end as NoOutLine12Hours,
case when zenbu.qty=1 and zenbu.Outime IS null and
DATEDIFF(D,zenbu.Intime,GETDATE())>1 then zenbu.Card# end as NoOutLine1day*/

from(
select
tall.workshop,
tall.Intime,
tall.Outime,
tall.qty,
tall.card#,
tall.PO,
tall.[Style No],

DATEDIFF(HOUR,tall.Intime,tall.Outime) as Diffhours,/*givetimescount*/
case when tall.qty=1 and tall.Outime IS null then DATEDIFF(DAY,tall.Intime,GETDATE())end as Diffday,
case when tall.qty<>0 and datediff(HOUR,tall.Intime,tall.Outime)=2 then tall.cardno end as out2timecard,
case when tall.qty<>0 and datediff(HOUR,tall.Intime,tall.Outime)=3 then tall.cardno end as out3timecard,
case when tall.qty<>0 and datediff(HOUR,tall.Intime,tall.Outime)=4 then tall.cardno end as out4timecard,
case when tall.qty<>0 and datediff(HOUR,tall.Intime,tall.Outime)=5 then tall.cardno end as out5timecard,
case when tall.qty<>0 and datediff(HOUR,tall.Intime,tall.Outime)=6 then tall.cardno end as out6timecard,

case when tall.qty<>0 and tall.Inticket=4 and tall.exitticket IS null and
datediff(DAY,tall.Intime,GETDATE())=1 OR datediff(DAY,tall.Intime,tall.Outime)=1 then tall.cardno end as out1day,
case when tall.qty<>0 and tall.Inticket=4 and tall.exitticket IS null and
datediff(DAY,tall.Intime,GETDATE())=2 OR datediff(DAY,tall.Intime,tall.Outime)=2 then tall.cardno end as out2day,
case when tall.qty<>0 and tall.Inticket=4 and tall.exitticket IS null and
datediff(DAY,tall.Intime,GETDATE())=3 OR datediff(DAY,tall.Intime,tall.Outime)=3 then tall.cardno end as out3day,
case when tall.qty<>0 and tall.Inticket=4 and tall.exitticket IS null and
datediff(DAY,tall.Intime,GETDATE())=4 OR datediff(DAY,tall.Intime,tall.Outime)=4 then tall.cardno end as out4day,
case when tall.qty<>0 and tall.Inticket=4 and tall.exitticket IS null and
datediff(DAY,tall.Intime,GETDATE())=5 OR datediff(DAY,tall.Intime,tall.Outime)=5 then tall.cardno end as out5day,
case when tall.qty<>0 and tall.Inticket=4 and tall.exitticket IS null and
datediff(DAY,tall.Intime,GETDATE())=7 OR datediff(DAY,tall.Intime,tall.Outime)=7 then tall.cardno end as out7day,
case when tall.qty<>0 and tall.Inticket=4 and tall.exitticket IS null and
datediff(DAY,tall.Intime,GETDATE())>7 OR datediff(DAY,tall.Intime,tall.Outime)>7 then tall.cardno end as outweeksday
from(
select
total.Card# as cardno,COUNT(*)as qty,
total.Workshop,
total.Card#,
total.PO,
total.[Style No],
MAX(case when total.Ticket#=4 then total.Ticket# END) as Inticket,
MAX(case when total.Ticket#=20 then total.Ticket# END) as exitticket,
MAX(case when total.Ticket#=4 then total.[Receive Time] END) as Intime,
MAX(case when total.Ticket#=20 then total.[Receive Time] END) as Outime

from(
select
only.Ticket#,
only.Workshop,
only.Card#,
only.PO,
only.[Style No]
from test.dbo.TicketDetailData1420 only
group by
only.Workshop,
only.Card#,
only.Ticket#,
only.PO,
only.[Style No]
)total
where
total.Workshop=total.Workshop
group by
total.Workshop,
total.Card#,
total.PO,
total.[Style No]
/* order by
total.Workshop*/
)tall
where
tall.cardno=tall.cardno
/*order by
tall.Workshop*/
)zenbu
where zenbu.Workshop=zenbu.Workshop
and
zenbu.Diffhours <>0
and
zenbu.Diffhours>23 and zenbu.Diffhours<49
group by
zenbu.Workshop,
zenbu.Intime,
zenbu.Outime,
zenbu.qty,
zenbu.Card#,
zenbu.Diffhours,
zenbu.Diffday,
zenbu.PO,
zenbu.[Style No],
zenbu.out2timecard,
zenbu.out3timecard,
zenbu.out4timecard,
zenbu.out5timecard,
zenbu.out6timecard
order by
zenbu.Workshop

• 写回答

#### 1条回答默认 最新

• R-Sissel 2018-12-27 08:29
关注

group by
zenbu.Workshop,
zenbu.Intime,
zenbu.Outime,
zenbu.qty,
zenbu.Card#,
zenbu.Diffhours,
zenbu.Diffday,
zenbu.PO

group by会把相同值进行合并分组展示。sql语句查询出来的值，倒数第二列三个to08相同的值

本回答被题主选为最佳回答 , 对您是否有帮助呢?
评论

• 已采纳回答 8月24日

#### 悬赏问题

• ¥20 Html备忘录页面制作
• ¥15 黄永刚的晶体塑性子程序中输入的材料参数里的晶体取向参数是什么形式的？
• ¥20 数学建模来解决我这个问题
• ¥15 计算机网络ip分片偏移量计算头部是-20还是-40呀
• ¥15 stc15f2k60s2单片机关于流水灯，时钟，定时器，矩阵键盘等方面的综合问题
• ¥15 YOLOv8已有一个初步的检测模型，想利用这个模型对新的图片进行自动标注，生成labellmg可以识别的数据，再手动修改。如何操作？
• ¥30 NIRfast软件使用指导
• ¥20 matlab仿真问题，求功率谱密度
• ¥15 求micropython modbus-RTU 从机的代码或库？
• ¥15 django5安装失败