有条SQL如下SELECT dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
SUBSTRING(dbo.PRDTC.TA006,2,3) FROM dbo.PRDTC INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
HAVING (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
我想把它修改成下面的样子,但是有问题,请大家帮忙修改下;
SELECT dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA' then SUBSTRING(dbo.PRDTC.TA006,3,3) else SUBSTRING(dbo.PRDTC.TA006,2,3) end FROM dbo.PRDTC INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
HAVING (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
SUBSTRING(dbo.PRDTC.TA006,2,3) FROM dbo.PRDTC INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
HAVING (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
我想把它修改成下面的样子,但是有问题,请大家帮忙修改下;
SELECT dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA' then SUBSTRING(dbo.PRDTC.TA006,3,3) else SUBSTRING(dbo.PRDTC.TA006,2,3) end FROM dbo.PRDTC INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
HAVING (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
SELECT dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA'
then SUBSTRING(dbo.PRDTC.TA006,3,3)
else SUBSTRING(dbo.PRDTC.TA006,2,3)
end
FROM dbo.PRDTC INNER JOIN dbo.INVMB
ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
SUBSTRING(dbo.PRDTC.TA006,1,2)
HAVING (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ') --group by SUBSTRING(dbo.PRDTC.TA006,2,3)
修改成
--SUBSTRING(dbo.PRDTC.TA006,1,2)
case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA' then SUBSTRING(dbo.PRDTC.TA006,3,3) else SUBSTRING(dbo.PRDTC.TA006,2,3) end
FROM dbo.PRDTC INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
where (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
having 是加聚合类条件的地方
case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA'
then SUBSTRING(dbo.PRDTC.TA006,3,3)
else SUBSTRING(dbo.PRDTC.TA006,2,3)
end
FROM dbo.PRDTC INNER JOIN dbo.INVMB
ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
where (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
SUBSTRING(dbo.PRDTC.TA006,1,2)
dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA' then SUBSTRING(dbo.PRDTC.TA006,3,3) else SUBSTRING(dbo.PRDTC.TA006,2,3) end
FROM
dbo.PRDTC INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
where
(dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
GROUP BY
dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
这个可能有问题
dbo.PRDTC.TADATE,
A = case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA'
then SUBSTRING(dbo.PRDTC.TA006,3,3)
else SUBSTRING(dbo.PRDTC.TA006,2,3)
end
FROM dbo.PRDTC
INNER JOIN dbo.INVMB
ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
WHERE A = SUBSTRING(dbo.PRDTC.TA006,2,3)
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE
HAVING (dbo.PRDTC.TADATE = '2009-11-4 0:00:00')
AND (dbo.PRDTC.LINENUM = 'B22 ')
SELECT dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA'
then SUBSTRING(dbo.PRDTC.TA006,3,3)
else SUBSTRING(dbo.PRDTC.TA006,2,3)
end
FROM dbo.PRDTC
INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
where(dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
--try
SELECT dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,
max(case when SUBSTRING(dbo.PRDTC.TA006,1,2)='SA' then SUBSTRING(dbo.PRDTC.TA006,3,3)
else SUBSTRING(dbo.PRDTC.TA006,2,3) end) --加上聚合条件
FROM dbo.PRDTC INNER JOIN dbo.INVMB ON dbo.PRDTC.TA006 = dbo.INVMB.MB001
GROUP BY dbo.PRDTC.LINENUM, dbo.PRDTC.TADATE,SUBSTRING(dbo.PRDTC.TA006,2,3)
HAVING (dbo.PRDTC.TADATE = '2009-11-4 0:00:00') AND (dbo.PRDTC.LINENUM = 'B22 ')