用例&当声明 [英] Use case & when statement

问题描述

我有一个临时表作为Table1，包含以下字段：



帐户ID，CustomerID，州

4564640 252 TX

4561210 257 WI

4564620 253 TX

4564720 259 NM



这里是我想要实现的，我需要根据表2中的状态从不同的服务器查找每个客户的帐户ID以获得正确的CustomerID并使用该ID，在表3中检查该客户是否真的有效是不是。



我尝试过的事情：

I've got a temp table as Table1 with the following fields:

Account ID, CustomerID, State
4564640 252 TX
4561210 257 WI
4564620 253 TX
4564720 259 NM

Here is what I am trying to achieve, I need to look up each customer with its Account ID from a different Server according to its state from Table2 to get the proper "CustomerID" and using that ID, check in Table3 if that Customer is actually valid or not.

What I have tried:

SELECT CAST(
            CASE
                WHEN LocationState = 'TX'
                        THEN

推荐答案

您不需要使用 CASE ... WHEN ... THEN ... END 语句。您需要使用 JOIN 的。请参阅： SQL连接的可视化表示 [ ^ ]

You don't need to use CASE ... WHEN ... THEN ... END statement. You need to use JOIN's. See: Visual Representation of SQL Joins[^]

SELECT t1.<FirstField1>, t2.<AnotherField>
FROM [dbo1].Table1 AS t1 <LEFT|RIGHT> JOIN [dbo2].Table2 AS t2 ON t1.KeyField = t2.OtherKeyField

由于数据信息不完整，我们无法提供更多详细信息。

We can't provide more details due to incomplete information about data.

我现在要发布这个 - 这是我之前试图发布的那个。道歉 - 它包含了很多其他人所说的内容
$ b $是你正在寻找的技术是加入 - 请参阅此文 SQL连接的可视化表示 [ ^ ]



没有必要使用CASE，我不知道你为什么要尝试来自CAST的东西。



根据您对表的描述，您在使用数据库模式时会出错。 ActiveCustomer标志是联系人或客户的功能而不是客户的交易。您需要阅读数据库规范化 [ ^ ]



它也很混乱，因为你的Table1已经有了CustomerID，所以我不知道为什么你需要到某个地方去正确的客户ID。



我使用了一些样本表，如下所示：

I'm going to post this now anyway - it's the one I was trying to post earlier. Apologies - it contains a lot of what the others have said
e technique you are looking for is JOIN - see this article Visual Representation of SQL Joins[^]

There is no need to use CASE and I have no idea why you are trying to CAST something.

From your description of your tables you are making mistakes with your database schema. the ActiveCustomer flag is a feature of a Contact or Customer not the Transactions for the customer. You need to read up on Database Normalization[^]

It's also very confusing because your Table1 already has CustomerID on it so I have no idea why you need to go somewhere to get the "correct" CustomerID.

I used some sample tables that look like this:

create table table1
(
	[Account ID] varchar(7), 
	CustomerID int, 
	[State] varchar(3)
)
insert into table1 values
('4564640', 252, 'TX'),
('4561210', 257, 'WI'),
('4564620', 253, 'TX'),
('4564720', 259, 'NM')

create table contact
(
	[Account ID] varchar(7),
	[CustomerID] int,
	ActiveCustomer bit,
	FirstName varchar(50)
	-- other contact fields
)
insert into contact values
('4564640', 252, 1, 'John'), --active
('4564620', 253, 0, 'Fred')  -- not active

create table [transactions]
(
	[CustomerID] int,
	transdate date
	-- other transaction fields
)

insert into transactions values
(252, getdate()),
(252, getdate()),
(253, getdate())

您需要做的就是在表格中进行连接以获取所需信息：

All you need to do is do a join across the table to get the information you need:

lect c.CustomerID, t1.[Account ID], FirstName, [State], CASE WHEN ActiveCustomer = 1 THEN 'Active' ELSE 'Inactive' END AS [status]
from transactions t
inner join contact c on t.CustomerID = c.CustomerID
inner join table1 t1 on t1.[Account ID] = c.[Account ID]

如果您愿意，可以包含WHERE子句以省略非活动客户：

You can include a WHERE clause to omit inactive customers if you want to:

WHERE c.ActiveCustomer = 1

如果一个或多个表位于不同的服务器上，那么您只需要确保您使用完全限定的表名

例如

If one or more of the tables is on a different server then you just need to ensure that you use the fully qualified table names
e.g.

MyOtherServer.DatabaseName.schemaname.tablename

您可能需要将服务器设置为链接服务器（数据库引擎） [ ^ ]

You may need to set the servers up as Linked Servers (Database Engine)[^]

这篇关于用例&当声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！