我有两个表: Item和Job我正尝试从item表中获取项目的单位重量。“职务”表有两个项目代码列。Item1,第2项我用的是这样的东西。
CASE
WHEN job.item2
LIKE '%cc%'
THEN item.unitweight * job.qty
ELSE job.qty
END AS specific item
职务表
+-----+-------+-------+-----+
| job | Item1 | Item2 | qty |
+-----+-------+-------+-----+
| 1 | aa | aaa | 5 |
| 2 | bb | cc | 6 |
| 3 | cc | bb | 7 |
| 4 | aa | ddd | 8 |
+-----+-------+-------+-----+
项目表
+------+-------------+
| Item | Unit Weight |
+------+-------------+
| aa | 5 |
| bb | 6 |
| cc | 7 |
| dd | 8 |
+------+-------------+
我想让查询返回与Item2相关联的42,但我的查询返回36
发布于 2018-09-13 07:21:44
根据我对你的问题的最好解释,我想出了这个:
SELECT J.JOB,
CASE WHEN J.ITEM1 = 'cc'
THEN I1.Unit_Wt * j.QTY
ELSE 0
END
+ CASE WHEN J.ITEM2 = 'cc'
THEN I2.Unit_Wt * j.QTY
ELSE 0
END AS cc_tot_wt
FROM #Job AS J
LEFT JOIN #Item AS I1
ON I1.ITEM = J.ITEM1
LEFT JOIN #Item AS I2
ON I2.ITEM = J.ITEM2
WHERE I1.ITEM = 'cc'
OR I2.ITEM = 'cc'
这将为您提供作业2的答案42和作业3的答案49,这是作业的项目“cc”的总权重。
https://stackoverflow.com/questions/52302342
复制相似问题