SQL从两列和两个表中附加不同的值 [英] SQL append distinct values from two columns and two tables

问题描述

我正在尝试创建 SQL 代码，该代码从两列中获取不同的值并附加"它们.我的意思是下表:

I'm trying to create SQL code that takes the distinct values from two columns and "appends" them. By that, I mean that that the following table:

Account   Article
-----------------
   1        1
   1        2
   2        3

应该产生以下结果:

Account   Article
-----------------
   1        1
   1        2
   1        3
   2        1
   2        2
   2        3

我使用联合从两个表中执行此操作，因此想法是获取两个表中所有唯一帐号的所有组合以及两个表中所有唯一商品编号.我想要一个子句，将两个表都限制在一年前的订单日期之后.

I'm doing this from two tables using a union, so the idea is to get all combination of all unique account numbers in both tables with all unique article numbers in both tables. And I want a clause that limits both tables to a order date later then one year ago.

到目前为止我有:

Select Distinct 
    "Tra.".cus_outnum As "account number", 
    "Tra.".artnum As "article number"
From 
    (table1) "Tra." 
Where 
    "Tra.".invdat >= DATEADD(year, -1, GETDATE())

Union

Select Distinct 
    "Sal.".outnum As "account number", 
    "Sal.".artnum As "article number"
From 
    (table2) "Sal."  
Where 
    "Sal.".deldat>= DATEADD(year, -1, GETDATE())

问题是它只给了我帐户和文章都存在的组合.我已经厌倦了用 with 语句来做这件事:

Problem is that it only gives me the combination where both account and article exist. I have unsuccessfully tired to do it with a with statement:

WITH temp1 AS 
(
     Select distinct cus_outnum 
     From table1
), temp2 AS
(
     Select distinct artnum 
     From table1
)
SELECT cus_outnum,artnum
FROM temp1, temp2, table1

非常感谢蚂蚁的帮助！

推荐答案

这给出了预期的结果:

with cte1 as (Select distinct account from test)
,cte2 as (Select distinct article from test)
Select * from cte1 cross join cte2

架构:

Create table test(account int, article int);
Insert into test values(1,1);
Insert into test values(1,2);
Insert into test values(2,3);

这篇关于SQL从两列和两个表中附加不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！