如何根据一个条件从同一列中获取相同表中的值？ [英] How can I fetch value from same table from same column depending on one condition?

问题描述

我必须从表BplD中获取销售和交易的价值总数。

BplD和BplM值可以加入。

唯一的条件是BplM.Line ='LeSales'然后bplD.total值应该是Sales值



并且如果BplM.Line ='LeTrade'那么BplD.total值应该是交易价值。 />


如何做到这一点？



我的尝试：



选择BplD.total作为销售，

BplD.total作为交易

来自BplD

内连接BplM

在BplD.BplMPkey = BplM.pkey

其中BplM.Line ='LeSales'



但这只会给销售​​价值而不是交易价值。

如何获得两个价值？

请帮助

I have to fetch value of Sales and trade from table BplD from column Total.
BplD and BplM value can be joined.
only condition is if BplM.Line='LeSales' then bplD.total value should be Sales value

and If BplM.Line='LeTrade' then BplD.total value should be trade value.

How to do this?

What I have tried:

Select BplD.total as Sales,
BplD.total as Trade
from BplD
inner join BplM
on BplD.BplMPkey= BplM.pkey
where BplM.Line='LeSales'

But this will give only Sales value not the Trade value.
How to get both values?
Please help

推荐答案

尝试：

Try:

...where BplM.Line='LeSales' OR BpIM.Line = 'LeTrade'

如果这不是你想要的，那么我们需要样本输入和输出为了弄清楚你想要实现的目标。



BTW：您确实意识到输出的Sales和Trade列是从同一列中提取的，因此将始终包含相同的值？

If that isn't what you are after, then we need sample input and output in order to work out exactly what you are trying to achieve.

BTW: You do realize that the "Sales" and "Trade" columns of your output are fetched from the same column, so will always contain the same value?

Quote：

虽然输出的Sales和Trade列是取自同一列，但值是基于BplM.Line ='LeSales'和BplM.Line ='LeTarde'

Though the "Sales" and "Trade" columns of output are fetched from the same column, but value is coming based on BplM.Line='LeSales' and BplM.Line='LeTarde'

啊！所以你想要的是两列，一个是BplM.Line是'Sales'时的值，另一个是'Trade'时的值？

这并不复杂，但需要一个案例：

Ah! So what you want is two columns, one has a value when BplM.Line is 'Sales' and the other when it is 'Trade'?
That's not complicated, but it needs a CASE:

SELECT CASE WHEN m.Line='LeSales' THEN d.total ELSE 0 END AS Sales,
       CASE WHEN m.Line='LeTrade' THEN d.total ELSE 0 END AS Trade
FROM BplD d
INNER JOIN BplM m
ON d.BplMPkey = m.pkey

如果Line中有其他值，则需要带有OR子句的WHILE。



但是数据设计需要一些工作：你正在复制信息并减慢处理速度。添加第三个名为TransType的表，其中包含两列

If you have other values in Line, then you will need the WHILE with an OR clause.

But the data design needs some work: you are duplicating information and slowing processing down. Add a third table called TransType with two columns

ID     INT, IDENTITY
LINE   NVARCHAR

并将Line列设为该表的外键。这样，你就不能在你的Line专栏中获得愚蠢的价值，这意味着错过了数据。

And make the Line column a Foreign Key to that table. That way, you can't get "stupid values" in your Line column which means data gets missed.

这篇关于如何根据一个条件从同一列中获取相同表中的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！