连接在查询中获取错误信息
连接在查询中获取错误信息
提问于 2012-01-26 23:28:57
我在连接查询时遇到了一些问题。我想要的是选择两个表的行，这些表有一些相似的状态，其他的没有。我有一个状态等价的观点
Table Sales:
ID ..... StateID
1 1
2 1
3 6
Table Orders
ID ..... StateID
11 2
12 2
15 3
Table StatesEquivalence
ID SalesState OrdersState StateName
1 1 2 Attended
2 2 3 Declined我已经将两个表(Sales和Orders)合并在一起，我想要一个包含EquivalentStateID和StateName的列，方法如下：
SELECT sales.ID as ID,equivalence.ID as State,equivalence.StateName
FROM Sales
INNER JOIN StatesEquivalence as equivalence
ON sales.StateID = equivalence.SalesState
WHERE sales.ID = 1
UNION
SELECT orders.ID as ID,equivalence.ID as State,equivalence.StateName
FROM Orders as orders
INNER JOIN StatesEquivalence as equivalence
ON orders.StateID = equivalence.OrdersState不知何故，我在equivalentID上获取了错误的信息
ID State StateName
1 2 Attended
2 2 Attended
11 2 Attended
...我不知道发生了什么。因为状态名是正确的，但是StateID显示了错误的信息
该表可能显示以下内容：
ID State StateName
1 1 Attended
2 1 Attended
11 1 Attended
...回答 3
Stack Overflow用户
回答已采纳
发布于 2012-01-26 23:50:24
我不认为你向我们展示了真正的问题代码。我做了以下工作：
begin transaction
create table sales (id int, stateid int)
create table orders (id int, stateid int)
create table statesequivalence (id int, salesstate int, ordersstate int, statename varchar(20))
insert into sales values (1, 1)
insert into sales values (2, 1)
insert into sales values (3, 6)
insert into orders values (11, 2)
insert into orders values (12, 2)
insert into orders values (15, 3)
insert into statesequivalence values (1, 1, 2, 'Attended')
insert into statesequivalence values (2, 2, 3, 'Declined')
SELECT sales.ID as ID,equivalence.ID as State,equivalence.statename
FROM Sales
INNER JOIN statesequivalence as equivalence
ON sales.StateID = equivalence.SalesState
WHERE sales.ID = 1
UNION
SELECT orders.ID as ID,equivalence.ID as State,equivalence.StateName
FROM Orders as orders
INNER JOIN statesequivalence as equivalence
ON orders.StateID = equivalence.OrdersState
rollback结果：
ID State statename
----------- ----------- --------------------
1 1 Attended
11 1 Attended
12 1 Attended
15 2 Declined所以，它是有效的。你能试试上面的代码吗？
Stack Overflow用户
发布于 2012-01-26 23:50:39
以下是测试脚本和语句的输出。
也许从这里开始工作更容易，您可以告诉我们这个输出有什么问题。
测试脚本可以通过进行调整和执行
输出
ID State StateName
----------- ----------- ---------
1 1 Attended
11 1 Attended
12 1 Attended
15 2 Declined测试脚本
;WITH Sales AS (
SELECT * FROM (VALUES
(1, 1)
, (2, 1)
, (3, 6)
) AS Sales (ID, StateID)
)
, Orders AS (
SELECT * FROM (VALUES
(11, 2)
, (12, 2)
, (15, 3)
) AS Orders (ID, StateID)
)
, StatesEquivalence AS (
SELECT * FROM (VALUES
(1, 1, 2, 'Attended')
, (2, 2, 3, 'Declined')
) AS StatesEquivalence (ID, SalesState, OrdersState, StateName)
)
SELECT sales.ID as ID
, equivalence.ID as State
, equivalence.StateName
FROM Sales
INNER JOIN StatesEquivalence as equivalence ON sales.StateID = equivalence.SalesState
WHERE sales.ID = 1
UNION
SELECT orders.ID as ID
, equivalence.ID as State
, equivalence.StateName
FROM Orders as orders
INNER JOIN StatesEquivalence as equivalence ON orders.StateID = equivalence.OrdersState Stack Overflow用户
发布于 2012-01-26 23:45:32
我不明白你到底需要什么，但也许这就是你想要的？
select *
from StatesEquivalence EQ
full join Sales S on EQ.SalesState = S.StateId
full join Orders O on EQ.OrdersState = O.StateId这可能是一个起点..。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9020506
复制相关文章
相似问题
腾讯云开发者
