如果未找到 A,则使用 TSQL 选择值 B
我们有一个地址表,其中帐户的地址被标记为“主要”或“运输”。
我们要选择送货地址,除非没有送货地址,在这种情况下,我们需要主要地址。
在 SqlServer 2005 上使用 TSQL 的最佳方法是什么?
在下面的示例中,查询 ID 1 或 2 应返回 S 记录。查询 ID 2 应返回 P 记录。
请注意,还有其他可能的地址类型应被忽略。
DECLARE @tmp TABLE(
ID int,
AddressType CHAR
)
INSERT INTO @tmp (ID,addresstype) VALUES (1,'P')
INSERT INTO @tmp (ID,addresstype) VALUES (1,'S')
INSERT INTO @tmp (ID,addresstype) VALUES (2,'P')
INSERT INTO @tmp (ID,addresstype) VALUES (2,'A')
SELECT * from @tmp
We have an address table where the addresses for an account are marked Primary or Shipping.
We want to select the Shipping address unless there is no shipping address, in which case we want the Primary address.
Whats the best approach using TSQL on SqlServer 2005?
In the below sample, querying for ID 1 or 2 should return the S record. Querying for ID 2 should return the P record.
Note there are other possible address types that should be ignored.
DECLARE @tmp TABLE(
ID int,
AddressType CHAR
)
INSERT INTO @tmp (ID,addresstype) VALUES (1,'P')
INSERT INTO @tmp (ID,addresstype) VALUES (1,'S')
INSERT INTO @tmp (ID,addresstype) VALUES (2,'P')
INSERT INTO @tmp (ID,addresstype) VALUES (2,'A')
SELECT * from @tmp
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
这很讨厌,但它有效:
This is nasty but it works:
假设您想在 P 或 S 之间进行选择
Select Top 1 * from @tmp Where AddressType In( 'P', 'S') And ID = @id Order By AddressType Desc
Assuming you want to choose between P or S
Select Top 1 * from @tmp Where AddressType In( 'P', 'S') And ID = @id Order By AddressType Desc
您在寻找COALESCE吗?它返回其参数中的第一个非空表达式。
例如,
SELECT COALESCE(ShippingAddress, PrimaryAddress) FROM tableName
...如果
ShippingAddress
不为NULL
,则检索ShippingAddress
,如果ShippingAddress
为NULL
,则返回PrimaryAddress
code>,除非PrimaryAddress
也是NULL
,在这种情况下,它只返回NULL
。这是一个可能有用的示例页面。以及 MSDN 页面。
Are you looking for COALESCE? It returns the first non-null expression among its arguments.
For example,
SELECT COALESCE(ShippingAddress, PrimaryAddress) FROM tableName
....retrieves
ShippingAddress
if it is notNULL
, and ifShippingAddress
isNULL
, it returnsPrimaryAddress
, unlessPrimaryAddress
is alsoNULL
, in which case it just returnsNULL
.Here is an example page that might be helpful. And the MSDN page.
不用花哨的代码,只需依赖
S
>仅限于 P 和 S 的集合中的P
您也可以使用 ROW_NUMBER 或 UNION 或派生表。
如果你只想要一个ID,那么TOP会更容易
这取决于你的用法:一个ID或多个ID在一个查询中
Instead of fancy code, just rely on
S
>P
in a set limited to P and SYou can use ROW_NUMBER or a UNION or a derived table too.
If you want for one ID only, then TOP is easier
It depends on your usage: one ID or many IDs in one query