select ds.machineID,PC.ShortName,DS.JobSheetNo from tw1.DyeSchedule ds
left join tw1.JobSheet js on ds.JobSheetNo=js.SheetNo
left join tw1.P_customer pc on pc.CustomerID=js.CustomerID
where ds.machineID='D1#'这是其中的一条,现在有多条语句,只有ds.machineID='D2#' D3#这不一样,我想让他们在列在加起来,(就是每加一条多三个column,)语句要怎么写?
left join tw1.JobSheet js on ds.JobSheetNo=js.SheetNo
left join tw1.P_customer pc on pc.CustomerID=js.CustomerID
where ds.machineID='D1#'这是其中的一条,现在有多条语句,只有ds.machineID='D2#' D3#这不一样,我想让他们在列在加起来,(就是每加一条多三个column,)语句要怎么写?
,max(case when ds.machineID = 'D1#' then PC.ShortName end as col12
,max(case when ds.machineID = 'D1#' then DS.JobSheetNo end as col13
,max(case when ds.machineID = 'D2#' then ds.machineID end) as col21
,max(case when ds.machineID = 'D2#' then PC.ShortName end as col22
,max(case when ds.machineID = 'D2#' then DS.JobSheetNo end as col23
...
from tw1.DyeSchedule ds
left join tw1.JobSheet js on ds.JobSheetNo=js.SheetNo
left join tw1.P_customer pc on pc.CustomerID=js.CustomerID
where ds.machineID in ('D1#','D2#',...)
left join tw1.JobSheet js on ds.JobSheetNo=js.SheetNo
left join tw1.P_customer pc on pc.CustomerID=js.CustomerID
where ds.machineID='D1#' or ds.machineID='D2#' or ds.machineID='D3#'
这样?
如果是其他,可以根据情况,看能不能拼接出语句。不排除用动态方法实现,在存储过程中拼接
select
max(case ds.machineID when 'D1#' then ds.machineID end)+
max(case ds.machineID when 'D2#' then ds.machineID end)+
max(case ds.machineID when 'D3#' then ds.machineID end),
max(case ds.machineID when 'D1#' then PC.ShortName end)+
max(case ds.machineID when 'D2#' then PC.ShortName end)+
max(case ds.machineID when 'D3#' then PC.ShortName end),
max(case ds.machineID when 'D1#' then DS.JobSheetNo end)+
max(case ds.machineID when 'D2#' then DS.JobSheetNo end)+
max(case ds.machineID when 'D3#' then DS.JobSheetNo end)
from tw1.DyeSchedule ds
left join tw1.JobSheet js on ds.JobSheetNo=js.SheetNo
left join tw1.P_customer pc on pc.CustomerID=js.CustomerID
max(case ds.machineID when 'D1#' then ds.machineID end) ,
max(case ds.machineID when 'D2#' then ds.machineID end),
max(case ds.machineID when 'D3#' then ds.machineID end),
max(case ds.machineID when 'D1#' then PC.ShortName end),
max(case ds.machineID when 'D2#' then PC.ShortName end),
max(case ds.machineID when 'D3#' then PC.ShortName end),
max(case ds.machineID when 'D1#' then DS.JobSheetNo end),
max(case ds.machineID when 'D2#' then DS.JobSheetNo end),
max(case ds.machineID when 'D3#' then DS.JobSheetNo end)
from
tw1.DyeSchedule ds
left join
tw1.JobSheet js on ds.JobSheetNo=js.SheetNo
left join
tw1.P_customer pc on pc.CustomerID=js.CustomerID
这是我要的效果.没办法只能搞到这里了
--这样
select
'D1#',
max(case ds.machineID when 'D1#' then PC.ShortName end),
max(case ds.machineID when 'D1#' then DS.JobSheetNo end),
'D2#',
max(case ds.machineID when 'D2#' then PC.ShortName end),
max(case ds.machineID when 'D2#' then DS.JobSheetNo end),
'D3#',
max(case ds.machineID when 'D3#' then PC.ShortName end),
max(case ds.machineID when 'D3#' then DS.JobSheetNo end)
from tw1.DyeSchedule ds
left join tw1.JobSheet js on ds.JobSheetNo=js.SheetNo
left join tw1.P_customer pc on pc.CustomerID=js.CustomerID
有点意思了,不过这个好像相互之间有种关系,出现的null,我这个相互没有关系,就是简单的连起来就行
要么改inner join?具体还是要看你想怎样的,我们又不知道你具体要干嘛
只是提供思路,细节自行更改